May 6, 2010 at 5:29 am
Hi all,
We have a 2000 box replicating to a 2008 box using transactional replication. When I insert a row on a specific table it causes replication to break, the replication agent says it expects a further parameter pkc3. On further investigation I can see that two articles are using the same set of insert\delete replication commands. To be clear the table I am trying to insert to is called:
TB_ALERT_COMPANY_CONTACT
When I look at the article details for this table on the replication properties I can see that the repl sps are stored as:
CALL sp_MSins_TB_ALERT_COMPANY_CONT
CALL sp_MSupd_TB_ALERT_COMPANY_CONT
CALL sp_MSdel_TB_ALERT_COMPANY_CONT
e.g. it does not have the full object name defined and this creates a conflict because there is another table called TB_ALERT_COMPANY_CONTACT_DEPT which when I look at the article SPs actually shares the same shortened version of the repl SP above. So when I do the insert, SQL gets confused as to which object it is inserting to and how many parameters it should be expecting.
I tried manually updating the repl commands for this article but was warned this would result in automatic reinitialisation on the 2000 box which I don't want to do as this is a live system and reinitialisation would result in an hours downtime.
I know how this problem came about after researching the issue, I had made a change to both these tables on the replicated database. To do this I had:
1) removed the article from subscription
2) dropped article
3) made my change
4) re-added article to publication
5) re-added article to subscription
6) run log reader agent
When this process is finished the repl commands for the object are shortened to 30 characters and as these table names create repl commands longer than 30 characters they end up sharing the same shortened repl commands and thus the insert fails!!!
I understand this is a known issue so my questions are:
1) How can I fix these two objects? Can I avoid reinitialising?
2) How can I identify further conflicts across the database?
3) How should I be making table changes on a replicated database to avoid this problem occurring without needing to reinitialise?
Any help would be much appreciated.
Thanks,
Liam
May 13, 2010 at 8:29 am
I have worked out how to do 1 & 2 but would still appreciate people's feedback on how to alter replicated tables without creating this problem.
Liam
July 15, 2010 at 9:34 am
I have just found the area where the bug arises. What I had been doing was running the following steps:
REMOVE TABLE FROM REP USING
exec sp_dropsubscription @publication = 'SQL_PUB'
, @article = 'YOUR_TABLE'
, @subscriber = 'SQL_SUB'
, @destination_db = 'SQL_PUB'
exec sp_droparticle @publication = 'SQL_PUB'
, @article = 'YOUR_TABLE'
THEN MADE ANY TABLE CHANGES HERE BEFORE ADDING BACK TO REP USING THE CODE BELOW
exec sp_addarticle @publication = 'SQL_PUB'
, @article = 'YOUR_TABLE'
, @source_table = 'YOUR_TABLE'
exec sp_addsubscription @publication = 'SQL_PUB'
, @article = 'YOUR_TABLE'
, @subscriber = 'SQL_SUB'
, @destination_db = 'SQL_PUB'
This creates the ins\upd\del replication sps with a maximum string length of 30 characters which is not a problem unless you have two tables with the same 30 characters at the start of the name. If you follow this routine with both tables both will share the same shortened repl sps and thus error occurs where expected number of params is not correct e.g. you are inserting into one table but repl picks up the sp for the other object and errors.
The way to get around this is to use the GUI on EM to add unpublished articles then run logreader agent, repl sps get created with full name and thus are not shared.
SELECT *
FROM sysarticles
WHERE del_cmd NOT LIKE '%' + dest_table
ORDER BY dest_table
You will need to delete old repl sps for the objects you want to fix on subscriber if you have been using the above technique. Check with this:
We're migrating onto SQL2008 next month so hopefully I will never see this problem again 🙂
L
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply