James Barash
James at fcidms.com
Tue Feb 10 07:48:27 CST 2004
Yes, SQL 2000 uses 1 for true and 0 for false. In general, 0 is false
and anything non 0 is true. I believe, in Access, you can test for True
and it will find 1 or -1 or anything but 0 and Null. I usually just test
for <> 0 since that seems to work everywhere.
Another thing to watch out for is bound forms that have bit fields.
Because of the difference in how Access and SQL Server handle bit fields
it can cause errors if you have bit fields that are Nullable. You must
have a default value or Access will raise an error whenever you edit a
record.
James Barash
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Lawhon, Alan
C Contractor/Morgan Research
Sent: Monday, February 09, 2004 5:31 PM
To: 'accessd at databaseadvisors.com'
Cc: dba-sqlserver at databaseadvisors.com
Subject: [AccessD] [dba-SQLServer]X-Posted: Problem Converting Bit
Fields From SQL Server 7.0 to SQL Server 2000
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
_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com