November 19, 2009 at 2:52 pm
I have posted this over in the Replication forum but it is not even getting looked at yet so I thought I would post it here in case someone might see it quicker because I am having this problem right now.
I recently upgraded our only system that performs replication and we had our first replication errors and I have come across a problem to which I cannot find a solution.
In SQL Server 2000, whenever an error occurred, I could get the subscription details on the offending article and I could see not only the transaction sequence number and command number but also the actual command text that was being presented for replication.
In 2005, I still see the transaction sequence number and command number but I cannot find the command text. I have got to find a way to find this because I need to be able to pull some of the identifying information about the particular data being replicated so I can figure out the proper course of action.
Sometimes, due to the way we have our replication setup, we can correct the situation by simply performing an additional update inside the application which allows the replication that is failing to succeed on it's next retry then everything works fine. Sometimes, I am able to identify it as a real error and I can simply skip, or delete, the offending transaction from trying to continue.
I have checked all of the distribution tables and the replication tables in both the publisher and subscriber databases and I cannot find the data. I'm beginning to think it is encrypted, for security reasons, so that we can't get to the data directly.
If anybody knows of a way to find this, I would be extremely grateful.
Thanks,
hawg
----------------------------------------------------------01010011010100010100110000100000010100110110010101110010011101100110010101110010001000000101001001101111011000110110101101110011
November 20, 2009 at 10:00 am
Here some more info that may, hopefully, help better explain what I am looking for.
When I get a replication error, I see something like:
The INSERT failed because the "customer" does not exist.
This happens because of a trigger in the table being updated that is verifying all associations before inserting a new record.
In SQL Server 2000, I would also see some information that identified the parameter values that were being updated (i.e. if this were an order table, I would see the order number, desc, and the customer number). This would enable me to research the reason the customer does not exist. In SQL Server 2005, this information is missing (or at least I can't find it).
Maybe this will help better explain my problem. If anyone has any ideas/suggestions, I welcome them.
Thanks,
hawg
----------------------------------------------------------01010011010100010100110000100000010100110110010101110010011101100110010101110010001000000101001001101111011000110110101101110011
November 20, 2009 at 4:37 pm
Is this merge replication? I have seen a similar error in SQL 2000 and we are usually able to restart the replication agent and it comes up and the transactions were still queued at the distributor and then were able to replicate over correctly.
If you are worried about missing data at one end you can try using the table diff utility, which will take two tables and compare them for inconsistencies. It can even build a table or a script to help you update the changes.
Joie Andrew
"Since 1982"
November 23, 2009 at 8:07 am
No, this is transactional replication.
Restarting wouldn't help because these are legitimate errors in the data. Most of the time, there are one or two scenarios where associated data can get out of sync, but not be affected by replication, and it doesn't appear until another set of data replication occurs. At that point, depending on the actual problem, we can figure out what to do.
The big problem now is that, when we have many different replication transactions queued up, I need to be able to identify the data associated with the failed transaction. All I am getting is an error message. I used to be able to see the text of the procedure call that had all of the parameters listed. I can't find that now.
----------------------------------------------------------01010011010100010100110000100000010100110110010101110010011101100110010101110010001000000101001001101111011000110110101101110011
November 23, 2009 at 10:08 am
I think I know exactly what you are talking about. They changed the way you see the errors in 2005 and it took me a little bit to figure it out.. Hers my notes on how to find the offending command. This is from some documentation I wrote.
There are several replication stored procedures but the one I find the most imporatnt is the SP_BROWSEREPLCMDS proc. Use it, learn to read it , It will be your best friend.
From the output below you will see the list of commands that are in the distribution database awaiting a push into the subscriber. Find the Transaction sequence number you copied from above as most importantly you will notice there could be multiple command ID for the same xact_seqno. So verify the command ID as well in the replication agent error.
In this example the xact_seq_no and command_id shows the command {CALL [sp_MSdel_dboSETTING_VALUE] (557)}. It is trying to delete ID 557 from the SETTIGN_VALUE table. After looking at the data, it was determined the setting_value table was not in sync with the publisher and corrective action was taken. However since this had been deleted at the publisher there was no way to pull the record back in.
so there is a command to remove the records that are being distributed. The records can be found in the select * from msrepl_commands table in the distribution database, you will need the xact_seq_no from previous findings although note that it add 5 trailing zeros to the actual command in the GUI. (dunno why)
use distribution
go
delete from msrepl_commands where xact_seqno= 0x001D8A3F000004920012 and command_id = 1
go
This will remove the command from trying to be distributed to the subscriber and should clear my error above. Let me know if you have any questions.
Mike McNeer
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply