Microsoft KB Archive/145907

= BUG: Select/Into Another Database Changes Timestamp Datatype =

Article ID: 145907

Article Last Modified on 10/17/2003

-

APPLIES TO


 * Microsoft SQL Server 6.0 Standard Edition
 * Microsoft SQL Server 6.5 Standard Edition

-



This article was previously published under Q145907



BUG #: 13273 (6.50)



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 SQL Server 6.0 and 6.5. Starting with SQL Server version 7.0, the table created by SELECT INTO will retain both the timestamp data type and the values from the original table.

Additional query words: insert

Keywords: kbbug kbpending kbusage KB145907

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.