SQL Server Performance

Distributor Agent Stop with this Error Message

Discussion in 'Performance Tuning for SQL Server Replication' started by magictech, Mar 9, 2005.

  1. magictech New Member

    The distributor Agent in one of my replication setup has stop with the following error message "Cannot insert a non-null value into a timestamp column. Use INSERT with a column list or with a default of NULL for the timestamp column."

    If I restart the Distribution agent, it would try to connect and then it would fail again with the same error message. What is the appropriate way to resolve this type of error message? I would like to maybe flush the problem transaction out of the system so replication could keep working. In the past I've had to rebuild replication in order for the distributor agent to start again. I can#%92t afford to rebuild replication in production just to resolve this type of error messages. Please any input would be greatly appreciated.


    Regards
  2. satya Moderator

    From one of the forums on web, its referred:

    The timestamp, like the rowversion and rowguid, are automatically generated.
    Trying to force a specific value into that column will result in an error.
    From the MSDN:

    "timestamp is a data type that exposes automatically generated binary
    numbers, which are guaranteed to be unique within a database. timestamp is
    used typically as a mechanism for version-stamping table rows. The storage
    size is 8 bytes."

    The key there is "automatically generated". Remove that element from your
    "INSERT INTO" T-SQL statement and it should work fine.


    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.
  3. magictech New Member

    Thanks for responding to my posting. Here is my real problem. I'm getting an error message that being caused by a problem transaction. How do I flush out the problem transaction from replication? I need to insert another set of data in order to properly troubleshoot the problem that I#%92m having. Please any help would be greatly appreciated.


    Regards
  4. satya Moderator

    Then try to divide the transactions and insert the data using tiny transactions.
    If possible post the query where it is causing this issue.

    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.
  5. Argyle New Member

    Is this merge or transactional replication? If it's transactional generate a new snapshot and then run the distributer agent job after that.
  6. magictech New Member

    I now have a good query that should work. However, before applying this new query, I need to get rid of the existing problem query that causing the red error message signs on the distributor Agent. When I stop and start the distributor Agent, the error message reappears. I need to flush out the existing problem for the error message to go away. How do I flush out the existing problem transaction?


    Regards


  7. satya Moderator

    Can you please post the query that is causing this issue?

    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.

Share This Page