bit types different on SQL7 and 2000

• Rob-155936

  Newbie

  Points: 7

  More actions

  July 2, 2004 at 5:28 am

  #152211

  I have an application that connects to SQL 7 database which I need to upgrade to SQL 2000.

  One of the fields has a data type BIT, and is filled with 0's and 1's

  In the application (which I never wrote) appears in many places something like:

  SELECT     *  FROM         Tablename

  WHERE     (thefieldname = - 1)

  Why they used -1 and not 1  , I have no idea but it works as it returns all rows with 1 in BIT field.

  This statement returns nothing on the same table in SQL2000. Is there an explanation for this?

   

   

• Razvan Socol

  SSCarpal Tunnel

  Points: 4811

  More actions

  July 3, 2004 at 2:06 am

  #513234

  I think that they used -1 as this is the value for True in MS Access and in Visual Basic. Indeed, it seems that SQL Server 2000 behaves differently (more correct, if I may say) than SQL Server 7. But you can use sp_dbcmptlevel to simulate the SQL Server 7 behaviour:

  if exists (select * from sysobjects where name='bits_test' and type='U')

  drop table bits_test

  create table bits_test (

   id int identity primary key,

   x bit

  &nbsp

  insert into bits_test (x) values (1)

  insert into bits_test (x) values (0)

  insert into bits_test (x) values (-1)

  insert into bits_test (x) values (null)

  select * from bits_test

  declare @dbname sysname

  set @dbname=db_name()

  exec sp_dbcmptlevel @dbname, 70

  select * from bits_test where x=-1

  exec sp_dbcmptlevel @dbname, 80

  select * from bits_test where x=-1

  Setting the database compatibility level to anything below 80 will prevent you from using SQL Server 2000-specific features (for example: UDF-s, declarative cascading referential integrity, instead-of triggers, indexed computed columns, etc). If you need to use these features, you will have to change all the affected queries. I recommend that you use "<>0" instead of "=-1".

  Razvan Socol