Results 1 to 3 of 3
  1. #1
    Join Date
    May 2004
    Posts
    34

    Question Unanswered: bit column behavior MSSQL7 and MSSQL2000

    I was using MSSQL7 for a long period.
    I upsized to MSSQL7 from Access some years ago. Without any particular reason when writing code in stored procedures, when I wanred to select some records having a bit column to true I used the syntax bitcolumn=-1 (and not bitcolumn=1). This behavior was used in Access. Everything worked fine. Then I moved to MSSQL2000 and by restoring the MSSQL7 database I had no problem.
    However, in order to use some features of MSSQL2000 I had to run the sp
    sp_dbcmptlevel <database>, 80
    After that the condition bitcolumn=-1 didn't work.
    Can anyone verify this behavior, since I have to make dozens of changes in my stored procedures and triggers???

    Regards,
    Manolis Perrakis

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Bit datatype stored only value 1, 0, or Null.
    Because you cannot use aggregate functions on bit values, sometimes it is preferable to use the tinyint data type instead.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Dec 2003
    Posts
    39
    sp_dbcmptlevel <database>, 80
    the sp set the compatibility of the database to 80 (sql server 2k). now the database is on 2k version, where in sql server 2k version the value of bit datatype is 1, 0, or null. so change the syntax bitcolumn=-1 into bitcolumn=1 to refer to "yes"
    Link Link

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •