Post reply

Replication 6 Table limit

  • April 19, 2005 at 11:31 pm

    #164330

    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

    #553681

    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