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.

3 Comments

  • 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.

  • 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.



  • 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 :-)

Comments have been disabled for this content.