April 19, 2005 at 11:31 pm
hi
I seem to be having a problem with the number of tables published in an article.
I am setting up Transactional replication, but the publication appears to only able to manage 6 tables. when a senth table is added I get the following error message:
"Violation of PRIMARY KEY constraint 'PK__@snapshot_seqnos__28DAF76C'. Cannot insert duplicate key in object '#27E6D333'."
This is not a table or key in the replicated DB, so it must be one set up by the Svr, but I can not find any reference to it in MSDB, Master or the distribution DB.
Some one get me out of the dark, I've been stuck here for more than a week
Thanks....
April 20, 2005 at 2:19 am
This is a known bug in SQL Server 2000, for which there is now a hotfix (not sure what the KB article etc is).
This happens because there is a bug in the sp_MSget_repl_commands stored procedure where it does an insert select distinct subscription_seqno in to a temporary table, and tries to insert a varbinary into a varchar column. The result of this conversion is a blank, and the stored proc therefore tries to insert multiple blanks into the primary key column of the temporary table.
There are a couple of workarounds I know of:-
Set up a dummy subscription, then the real subscription, then delete the dummy subscription. Sometimes I have had to do this more than once, but eventually it works!.
Alternatively, you can identify the agent_id for the subscription (along with your error message in replication history you will see something similar to "call sp_MSget_repl_commands(20, ?, 0, 7500000)}). The agent id is the first parameter to the sp_MSget_repl_commands command, seen in the error above (i.e. 20 in this case).
Then run the following command:-
update mssubscriptions set subscription_seqno = 0x00000000000000000000 where agent_id = ???
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply