none
Cannot insert an explicit value into a timestamp column. Use INSERT with a column list to exclude the timestamp column, or insert a DEFAULT into the timestamp column. (Source: MSSQLServer, Error number: 273)

    Question

  • Hi members,

    I have set up transactional replication between two databases within same instance of SQL Server 2008 R2. I tested it and it worked fine 2-3 times.  But somehow it has stopped working now.  When I check the Replication Monitor > Distributor to Subscriber History, I find the following error in all synchronizations.

    Command attempted:
    if @@trancount > 0 rollback tran
    (Transaction sequence number: 0x000003C3000023F8004400000001, Command ID: 1)

    Error messages:
    Cannot insert an explicit value into a timestamp column. Use INSERT with a column list to exclude the timestamp column, or insert a DEFAULT into the timestamp column. (Source: MSSQLServer, Error number: 273)
    Get help: http://help/273
    Cannot insert an explicit value into a timestamp column. Use INSERT with a column list to exclude the timestamp column, or insert a DEFAULT into the timestamp column. (Source: MSSQLServer, Error number: 273)
    Get help: http://help/273

    What could be the reason for this error?  It does not tell us in which internal operation this error occurs - neither any stack trace.  How can we trace such errors?

    I have several solutions for resolving this error but nothing has worked.  I removed subscriber and re-added it also.

    If anyone has faced similar issue before, please let me know solution.

    Friday, January 21, 2011 4:08 AM

Answers

  • Hi,

    After further investigation, I found the following results according to my tests:

    • For snapshot replication, it support timestamp columns. The timestamp column is replicated, but the literal timestamp values are not. The timestamp values are regenerated when the snapshot is applied at the subscription database.
    • For the merge replication, it support timestamp columns. The timestamp column is replicated, but the literal timestamp values are not. The timestamp values are regenerated when the snapshot is applied at the subscription database. Also, incremental data changes replicated via merge agent, the timestamp value will be regenerated on the subscription database.
    • For the transactional replication, it support timestamp columns. The timestamp column and its literal timestamp values are replicated via initial snapshot synchronization. However, the following data changes replicated via logreader, the timestamp values will be regenerated on the subscription database.

    Hope this helps. Please let me know if you need more help.


    Best Regards,
    Chunsong Feng (MSFT)

    Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
    Thursday, January 27, 2011 6:46 AM

All replies

  • Hi,

    The rowversion date type column (timestamp is the synonym for the rowversion data type) is maintenced by the Databse Eengine itself, which is incremented for each insert or update operation. When we do DML operations to a table with rowversion column, which should be excluded, the only exception is for SELECT INTO statement.

    To work around this issue, you may try the following two options:

    • Exclude rowversion clumn from all articles that has rowversion column(s), or
    • Map rowversion column to binary(8) (for nonnullable rowversion column) and varbinary(8) (for nullable rowversion column).

    For more information, see: rowversion (http://msdn.microsoft.com/en-us/library/ms182776.aspx) and Making Schema Changes on Publication Databases (http://technet.microsoft.com/en-us/library/ms151870.aspx).

    Hope this helps. Please feel free to let me know if you need more help.


    Best Regards,
    Chunsong Feng

    Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
    Monday, January 24, 2011 8:42 AM
  • Hi ChungSong,

    Thanks a lot for your reply.

    Please note that I was getting this timestamp error only with a subscriber that was initialized with a snapshot.

    To resolve this error, I deleted the subscriber DB which was restored from a backup file.  I also deleted the subscriber.  later I set up a new subscriber with snapshot by checking the "Initialize" option in the SQL Server wizard.  Surprisingly with this kind of subscriber (initialized with a snapshot without using a backup file), I am not getting the timestamp error.

    Another thing is even when I updated a table without a timestamp column, I used to get the same "cannot insert an explicit.." error.

    Do you mean to say we cannot publish rowversion (timestamp) columns in articles irrespective of type of snapshot?  Please clarify.  If that is the case, how can we automate the task of removing only the timestamp column in all articles?

    Regards,

    Murtaza

    Tuesday, January 25, 2011 9:33 AM
  • Hi,

    After further investigation, I found the following results according to my tests:

    • For snapshot replication, it support timestamp columns. The timestamp column is replicated, but the literal timestamp values are not. The timestamp values are regenerated when the snapshot is applied at the subscription database.
    • For the merge replication, it support timestamp columns. The timestamp column is replicated, but the literal timestamp values are not. The timestamp values are regenerated when the snapshot is applied at the subscription database. Also, incremental data changes replicated via merge agent, the timestamp value will be regenerated on the subscription database.
    • For the transactional replication, it support timestamp columns. The timestamp column and its literal timestamp values are replicated via initial snapshot synchronization. However, the following data changes replicated via logreader, the timestamp values will be regenerated on the subscription database.

    Hope this helps. Please let me know if you need more help.


    Best Regards,
    Chunsong Feng (MSFT)

    Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
    Thursday, January 27, 2011 6:46 AM