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.
More information about the dba-SQLServer mailing list