Post reply

Bidirectional Transactional replication update error

  • September 5, 2005 at 6:52 am

    #165842

    I have set up 2 servers to run transactional replication with both acting as a publisher and a subscriber (2 offices both need to see the other with no delays).

    When I try an update my Distribution agent fails with an error message "Cannot update identity colum '', inserts and deletes are fine

    Investigation to date has told me that for standard transactional replication (ie with one publisher) primary keys are removed from the table in the subscriber and all is well. However as I require both to publish this is not an option.

    I have found the problem is in the sp_MSupd_

    stored procedure which looks at the @bitmap (havent a notion what this is) parameter to decide whether to update the primary key column or not. The only solution (?) i have found so far is to remove the first part of the if statement in the update stored procedures, which naturally im reluctant to do (not least because there are 81 such procedures)

    anyone any other ideas? - like how I can get this part of the stored procedure substring(@bitmap,1,1) not to = 1

  • September 5, 2005 at 10:31 pm

    #587658

    Check out this article: http://www.databasejournal.com/features/mssql/article.php/3483421. It may help.

    Also, consider using Merge replication instead of transactional. It will suit your needs well, I think. You can fully replicate identity fields as long as you use the Not for Replication option on all foreign keys.

  • September 6, 2005 at 4:18 am

    #587711

    Regretably this is a design flaw and you have to manually edit the update proc to remove the identity column update.

    --

    Hilary Cotter

    Looking for a SQL Server replication book?

    http://www.nwsu.com/0974973602.html

    Looking for a FAQ on Indexing Services/SQL FTS

    http://www.indexserverfaq.com

     

  • September 6, 2005 at 8:24 am

    #587796

    I suppose I can take solace in the fact that I wasnt being stupid and missing something

    Lets hope the testing department did a better job in SQL 2005

    Thanks for the info, it all works as expected now

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply