November 19, 2009 at 2:20 pm
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 27, 2009 at 3:19 am
Given you posted this a week ago you may have found the answer. In case you haven't then you can use sp_browsereplcmds which is run in the distribution database. It takes various parameters and be aware that the transaction sequence numbers are passed as nchar so a sample call could look like this:
sp_browsereplcmds@publisher_database_id= 25,
@xact_seqno_start= N'0x000007C200004287006B',
@xact_seqno_end= N'0x000007C200004287006B',
@command_id= 3
The value for @publisher_database_id is the value of the id column from mspublisher_databases where publisher_id is the srvid value from master..sysservers for the publishing server.
The result set contains a command column where you can see the command you are after.
Hope that helps.
Mike
PS I think SQL Server 2005 is a big step backwards as far as replication monitoring is concerned.
December 1, 2009 at 9:20 am
I am just getting back from the holidays and getting caught up.
I did get my problem resolved and it was with using the sp_browsereplcmds procedure. I had been having a problem with that procedure and it required a reboot to get it resolved. I had forgotten that that command did contain the actual command, with the parameters, that I was searching for. It used to be available in the GUI details.
Once I got the procedure working again, everything is working fine.
Thanks,
hawg
----------------------------------------------------------01010011010100010100110000100000010100110110010101110010011101100110010101110010001000000101001001101111011000110110101101110011
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply