INF: How to Perform Replication with the Timestamp Datatype

ID: Q156598

The information in this article applies to:

Microsoft SQL Server version 6.0

SUMMARY

In replication, the distribution task fails with the following error message when both the article published and the subscribing table have a timestamp column defined:

23000 [Microsoft][ODBC SQL Server Driver][SQL Server] User can't INSERT
a non-null value into a timestamp column. Use insert with a column list
or a default of null for timestamp column.

This problem occurs because the script generated by the synchronization task has defined binary (8) for the timestamp column (this is documented behavior). However, when the table already exists on the subscriber, the Bulk Copy Program (BCP) fails to replicate the data into the table on the subscriber, with the error generated by the distribution task.

MORE INFORMATION

The behavior of the distribution is largely by design. Timestamp is not supported as a datatype for replication, because it is not necessary to have identical values on published and subscribed articles for the timestamp column. The timestamp is only useful as an indication of the order of appearance of transactions on any given table in a database. In most cases, preserving the order of appearance of the transactions is sufficient. Thus, in replication, it is not possible to achieve the same values, although you can have a subscribed table with the same order of the rows, by using timestamp values and performing the following steps:

Create the article to be published. Use vertical partitioning to leave out the timestamp column.

Apply manual synchronization, to synchronize the tables.

Create a custom stored procedure for INSERT purposes. This procedure will take all values and explicitly list the columns to insert. However, the subscriber will generate the new timestamp values for the table, so the timestamp column should not be included in the parameters list of the custom stored procedure.

For example, assume the publisher is "XXX" and the subscriber is "YYY," and they both have the following table definition:

TABLE: tmstmp (col1 int not null PRIMARY KEY, col2 int, col3 timestamp)

Further, assume the following data is present on the table in the publisher:

TABLE: tmstmp on XXX has the following rows:


   col1        col2        col3
   ------------------------------------------
   1           2           0x00000001000011ed
   3           2           0x0000000100001245
   6           3           0x0000000100001306
   8           9           0x000000010000146c
   18          9           0x0000000100001502
   175         9           0x000000010000158d
   176         9           0x00000001000015b0

In terms of data, the publisher and subscriber must be synchronized manually. It is the responsibility of the user who is doing the replication to do the synchronization as well. For more information about manual synchronization, see the SQL "Administrator's Companion 6.0" and Books Online.

After manual synchronization, the subscribing table has similar data to the publishing table. However, the timestamp values will be different, because they are generated on that subscribing server. For example, the subscriber may contain the following data:

TABLE: tmstmp on YYY has the following rows:


   col1        col2        col3
   ------------------------------------------
   1           2           0x00000000000001ed
   3           2           0x0000000000000245
   6           3           0x0000000000000306
   8           9           0x000000000000046c
   18          9           0x0000000000000502
   175         9           0x000000000000058d
   176         9           0x00000000000005b0

Continuing the example, perform the following steps:

Create the following stored procedure on the subscribing server (you should create this stored procedure in the subscribing database; let us assume it is PUBS):
```
   create proc sp_timeinsert @col1 int, @col2 int
   as
    begin
       insert into pubs..time_test(col1,col2) values(@col1,@col2)
    end
   go
 
```

Establish publication with an article that is on the tmstmp table. Also, use vertical partitioning, so that only col1 and col2 in the tmstmp table or article are to be replicated, omitting the timestamp column.

On the publisher, edit the publication to include the custom stored procedure for INSERT. In the appropriate text dialog box, type "CALL sp_timeinsert" (for more information, refer pages 521-523 in the SQL "Administrator's Companion 6.0").

Now when INSERT transactions are performed, the custom stored procedure is called with the column list, and the timestamp value is generated by the subscriber.

For example, assume the following transaction is applied on publisher XXX:


   insert into tmstmp(col1,col2)  values(181, 233)

This statement will add an entry in the tmstmp table on the publisher. The timestamp value will be automatically generated, so the row in tmstmp on publisher may be:


   col1        col2        col3
   ------------------------------------------
   181         9           0x0000000100001568

The logreader will read in this replicated transaction to the distribution database. The distributor will attempt to apply this transaction to the subscriber. However, due to the use of the custom stored procedure for INSERT (note that you can have custom stored procedures for DELETE or UPDATE as well, but only INSERT is used in this example), the following command is generated in the Msjob_commands table:


   {call sp_timeinsert(181,9)}

The stored procedure is called, and as it performs an INSERT with a column list (see the stored procedure text given above), the timestamp value is automatically generated on the subscriber, and a row similar to the following row is successfully added to the subscribing table:


   col1        col2        col3
   ------------------------------------------
   181         9           0x0000000000000568

Thus, a "replication of timestamp" is achieved.

Keywords : kbusage SSrvBCP SSrvRep
Version : 6.0
Platform : WINDOWS
Issue type : kbhowto