Microsoft KB Archive/248907

From BetaArchive Wiki

Article ID: 248907

Article Last Modified on 6/24/2004



APPLIES TO

  • Microsoft Access 2000 Standard Edition



This article was previously published under Q248907

Advanced: Requires expert coding, interoperability, and multiuser skills.

This article applies only to a Microsoft Access database (.mdb).


SYMPTOMS

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.

RESOLUTION

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:

239114 How To: Obtain the Latest Service Pack for the Microsoft Jet 4.0 Database Engine


STATUS

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

MORE INFORMATION

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.

REFERENCES

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:

253837 ACC2000: #Deleted in Linked Table Containing UniqueIdentifier



Additional query words: pra not correct wrong don't aren't

Keywords: kbbug kbfix KB248907