PRB: Select/Into Another Database Changes Timestamp Datatype

• Microsoft SQL Server, versions 6.0

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.

RESOLUTION

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.