BUG: Select/Into Another Database Changes Timestamp Datatype

• Microsoft SQL Server versions 6.0, 6.5

SYMPTOMS

When you create a table in a different database by using the SELECT/INTO statement, the timestamp datatype that exists in the source table changes to a binary (8) dataype in the target table, but new timestamp values are generated for the binary (8) column in the target table.

CAUSE

In a SELECT/INTO, the server perceives a timestamp column as a special datatype. When the server is trying to create the new table for the target database, it uses a predefined list of datatypes based on the "storage type" of the original column. In the case of timestamp columns, its storage type is 45 (found in systypes.usertype), which corresponds to a binary column.

In SQL Server 6.0, SELECT/INTO uses the base type instead of the user- defined datatype because base types are common across all databases and prevent mismatches of user-defined datatypes in systypes.usertype across databases.

WORKAROUND

If the timestamp datatype is needed for the target table, first create the table in the other database by using the CREATE TABLE statement or by using Manage Tables to define the timestamp column as datatype timestamp. Then use INSERT/INTO or BCP to selectively copy columns from the source table to the target table.

STATUS

Microsoft has confirmed this to be a problem in Microsoft SQL Server versions 6.0 and 6.5. We are researching this problem and will post new information here in the Microsoft Knowledge Base as it becomes available.

Additional query words: insert

Keywords          : kbusage SSrvProg 
Version           : 4.21a 6.0 6.5
Platform          : WINDOWS 
Issue type        : kbprb 

Last Reviewed: March 24, 1999