Lawhon, Alan C Contractor/Morgan Research
alan.lawhon at us.army.mil
Mon Feb 9 16:31:07 CST 2004
We upgraded our SQL Server 7.0 backend to SQL Server 2000 over the weekend. The
front end runs Access 2000 GUI client applications.
After the conversion, we are experiencing a problem with Access queries that
select data from the [converted] back end SQL Server tables. The problem
involves a hardcoded "-1" (i.e. "True" logic condition criteria) in the Access
queries testing against a "Bit" data type field in the SQL Server back end.
With the SQL Server 7.0 backend tables, the Access queries were working just
fine. In converting to SQL Server 2000, it appears that previously populated
bit fields populated with the value of "-1" ("True") got arbitrarily switched
to a value of "1" during the conversion. (The "-1" values in the back end table
["Bit" fields] got changed, NOT the "-1" criteria value in the front end query
cells!) Further, when the query runs against the [converted] back end table,
the "-1" criteria (from the Access query) is no longer recognized - the query
doesn't return data.
Needless to say, we are no longer getting the expected results (from execution
of the queries) after the conversion from SQL Server 7.0 to SQL Server 2000. I
suspect that Microsoft implemented a change in the behaviour of "Bit" fields
from version 7.0 to SQL Server 2000. While I search the Knowledge Base and try
to track this down, can anybody confirm whether this is the case?
We have a lot of queries (and VBA code) that test the "-1" value against fields
of data type "Bit", so we don't want to start changing these hardcoded criteria
(from "-1" to just plain "1") until and unless we are ABSOLUTELY SURE that this
is a SQL Server version 7.0 to version 2000 conversion anomaly.
TIA for any info or enlightenment on this problem.
Alan C. Lawhon