SQL Server Performance Forum – Threads Archive

Distributor Agent Stop with this Error Message

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

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.

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

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.

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

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

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.

]]>