Haslett, Andrew
andrew.haslett at ilc.gov.au
Wed Feb 11 04:22:23 CST 2004
There was an Access 2000 SQL Server 'Readiness Update' in early version of
Office 2k. I believe it was included in a later service pack.
This may or may not fix the issue, however it definitely fixed issues we had
when upgrading an Access MDB to SQL Server.
Check out Office Update for more info.
Cheers,
Andrew
-----Original Message-----
From: Lawhon, Alan C Contractor/Morgan Research
[mailto:alan.lawhon at us.army.mil]
Sent: Tuesday, 10 February 2004 9:01 AM
To: 'accessd at databaseadvisors.com'
Cc: dba-sqlserver at databaseadvisors.com
Subject: [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
IMPORTANT - PLEASE READ ********************
This email and any files transmitted with it are confidential and may
contain information protected by law from disclosure.
If you have received this message in error, please notify the sender
immediately and delete this email from your system.
No warranty is given that this email or files, if attached to this
email, are free from computer viruses or other defects. They
are provided on the basis the user assumes all responsibility for
loss, damage or consequence resulting directly or indirectly from
their use, whether caused by the negligence of the sender or not.