Compatibility of bit type between SqlServer 2000 and SqlServer 2005

Yesterday I had to debug an issue of a product that we are deploying to a production server. We were developing using SqlServer 2005 (express) and the hosting company uses SqlServer 2000 and as soon as we deploy, we got the following exception on one of the operations:

"Syntax error converting the varchar value 'true' to a column of data type bit"

After some debugging (a couple of hours) and after deploying some special debugging versions, we found the issue. In SqlServer 2005, the following query is perfectly legal:

SELECT UserId, Name FROM Users WHERE IsActive = 'true'

(where IsActive is a bit type)

As it turns out, this is not a valid query in SqlServer 2000, throwing above exception. Because of the single quote, SqlServer 2000, interprets the 'true' as a varchar and cannot cast it as bit, so you need to change it to:

SELECT UserId, Name FROM Users WHERE IsActive = 1

This query is valid in SqlServer 2005 and in SqlServer 2000.

This may be something widely known, but it wasn't to me, so I hope this helps you save some debugging time.

Published Tuesday, January 29, 2008 11:42 AM by jaimedp
Filed under: , ,

Comments

# re: Compatibility of bit type between SqlServer 2000 and SqlServer 2005

Tuesday, January 29, 2008 3:12 PM by FransBouma

The common mistake is to think that a bit field is a boolean. It's not, it's a bitfield. You can use it as a boolean, and it is in general used as such in sqlserver, but it's a different type inside sqlserver: in SQL, the type 'boolean' isn't really there except in predicate logic. For example a projection with a boolean won't work, you need int's or bits to help you out there.

the clients see 'bit' as a boolean type and convert true to 1 and 0 to false. Though inside the RDBMS, stick to the value the bit can have, i.e. 0, 1 or NULL (!), and not true or false.

# re: Compatibility of bit type between SqlServer 2000 and SqlServer 2005

Tuesday, January 29, 2008 3:24 PM by jaimedp

You are absolutely right, the thing that caught me was that the SqlServer 2005 handles the conversion automatically but as it turns SqlServer 2000 doesn’t.

# re: Compatibility of bit type between SqlServer 2000 and SqlServer 2005

Monday, March 15, 2010 1:58 AM by Keyur Patel(MCP)

I have also faced same issue, while we were developing using SqlServer 2005 (express) and the hosting company uses SqlServer 2000 and as soon as we deploy, we got the following exception on one of the operations:

"Syntax error converting the varchar value 'true' to a column of data type bit"

I have resolved the problem without changing dbtype in our existing DB. If you are using storeproce for inserting data in table then may be this solution helpful for you.

For this problem, we have changed all parameter type bit to varchar(@IsPermenant [varchar](10)). After that cast with bit type when passing values in storeprocedure(CAST(@IsPermenant as bit)).

Same as shown in below line.

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

GO

Create PROCEDURE [dbo].[EmployeeInsert]

@FirstName [varchar](30),

@MiddleName [varchar](30),

@State [int],

@IsPermenant [varchar](10)

AS

Begin

INSERT INTO EmployeeInsert

          (FirstName

          ,MiddleName

  ,State

  ,IsPermenant)

 VALUES

          (@FirstName,

          @MiddleName,

          @LastName,

  CAST(@IsPermenant as bit))

 Set @ID=SCOPE_IDENTITY()

END

This above solution succefully running on our prodiction site also. With this solution we can resolve our problem without changing dbtype in our existing table.

Happy Programming :-)

Leave a Comment

(required) 
(required) 
(optional)
(required)