SQL Server Performance Forum – Threads Archive

Timestamp datatype and Replication

Let say I created a table with column TimeStamp, having a timestamp data type and is not nullable. A stored procedure has an INSERT statement for this table but not specifying the TimeStamp column in the code. The stored procedure successfully inserts records in this table with the TimeStamp column automatically populated. However, after publishing this table for merged replication, the TimeStamp column changes to binary datatype and an additional column, named rowguidcol was created. With this change, the stored procedure now fails with this error: "Cannot insert the value NULL into column TimeStamp, table <table name>; column does not allow nulls. INSERT fails. …" Understandably, this error occurs because the TimeStamp column is NOT NULLABLE. However, when it was initially in the timestamp data type, I never encountered this error. The following are my question: 1. I suspect that after publishing the table for replication, the Replication changed the data type of the TimeStamp column to binary. Is this assumption valid? 2. Does this mean I should modify the stored procedure, just to adapt with the changes made by the Replication? 3. Is there a way to retain the timestamp data type of the column, even after I turn on the replication (because I may way want to activate/deactivate the replication)?

What is the type of replication used? If you want to add that column by your own and let SQL Server use it in replication, your rowguid column should have the following properties: – Should be of Uniqueidentifier datatype
– Should have the ROWGUIDCOL property set
– Should not allow NULLs
– Should have a default of newid()
Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

]]>