ACC2000: Append Query on Linked Tables Adds Incorrect GUID Values

You have an append query that takes values from a linked SQL Server table that contains a field of type UniqueIdentifier and appends the values to another linked SQL Server table. When you open the table to which the records are appended, you find that the UniqueIdentifier field for each record appended contains values different from those in the source table.

Obtain the latest Microsoft Jet 4.0 service pack that contains an updated version of the Microsoft Jet 4.0 database engine.

For additional information about how to obtain the latest version of the Jet 4.0 database engine, click the following article number to view the article in the Microsoft Knowledge Base:

Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.

Steps to Reproduce Behavior

1. Use either Microsoft SQL Server 7.0 Enterprise Manager or Access 2000 to open the sample database NorthwindCS (log on as the database owner).
2. Use the following SQL syntax to create a new stored procedure. This procedure creates two test tables in the SQL Server or MSDE database.

   Create Procedure MakeTestTables
   
   As
   
   if exists (select * from sysobjects where id =
   object_id(N'[dbo].[GUID_Test]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
   drop table [dbo].[GUID_Test]
   
   if exists (select * from sysobjects where id =
   object_id(N'[dbo].[GUID_Test2]') and OBJECTPROPERTY(id, N'IsUserTable') =
   1)
   drop table [dbo].[GUID_Test2]
   
   CREATE TABLE [dbo].[GUID_Test] (
   	[ColA]  uniqueidentifier ROWGUIDCOL  NOT NULL ,
   	[ColB] [varchar] (50) NULL )            
               ON [PRIMARY]
   
   ALTER TABLE [dbo].[GUID_Test] WITH NOCHECK ADD 
   	CONSTRAINT [PK_GUID_Test] PRIMARY KEY  NONCLUSTERED 
   	([ColA])
               ON [PRIMARY] 
   
   CREATE TABLE [dbo].[GUID_Test2] (
   	[ColA] [uniqueidentifier] NULL ,
   	[ColB] [varchar] (50) NULL ) 
               ON [PRIMARY]
   
   INSERT INTO GUID_TEST VALUES ('{DBAB6FFE-82B2-4D65-819E-32DD4D904C51}',
   'TESTRECORD1')
   INSERT INTO GUID_TEST VALUES ('{DBAB6FFE-82B2-4D65-819E-32DD4D904C52}',
   'TESTRECORD2')
   INSERT INTO GUID_TEST VALUES ('{DBAB6FFE-82B2-4D65-819E-32DD4D904C53}',
   'TESTRECORD3')
   
   return
   					

3. Save and close the stored procedure. Accept the default name of MakeTestTables, and then run the procedure by double-clicking it.
4. Open Access 2000 and create a new Access Database named MyTest.mdb. On the File menu, point to Get External Data, and then click Link Tables.
5. In the Link dialog box, change the Files of type box to ODBC Databases.
6. In the Select Data Source dialog box, open or create a data source name (DSN) that points to NorthwindCS.
7. Create links to the GUID_Test and GUID_Test2 tables on the server. Leave the linked tables with the names dbo_GUID_Test and dbo_GUID_Test2. If you are asked for an index column, select ColA.
8. Create a new query in Design view, but add no tables.
9. In the query design grid, click SQL View on the View menu.
10. Type the following SQL statement:

    INSERT INTO dbo_GUID_Test2 (ColA, ColB)
    SELECT dbo_GUID_Test.ColA, dbo_GUID_Test.ColB
    FROM dbo_GUID_Test;
    					

11. Save and run the query.
12. Open both the dbo_GUID_Test and dbo_GUID_Test2 tables. Size the windows and columns so that you can see their entire contents at the same time.

Note that GUID values inserted into dbo_GUID_Test2.ColA do not match the GUID values in dbo_GUID_Test.ColA.

For additional information about another issue involving linked tables with UniqueIdentifier fields, click the article number below to view the article in the Microsoft Knowledge Base: