SQL Server Merge Replication Using An Identity Column
Posted on 2004-09-23
I have set up merge replication with a pocket pc running an embedded visual basic application. The application subscribes to our SQL Server, uses SQL Server CE, runs very well, and synchronizes with the database reliably.
The table that I'm publishing on SQL Server is a table used to store item movements in our ERP system (i.e. stock and warehouse location changes). The table is basically a queue for these movements and every 15 seconds it processes the movements in bulk.
The problem is that the SQL table has an identity column. I set my replication to automatically adjust the identity columns on the publisher and subscriber, so I have that running and no conflicts should occur. The problem is that I have webpages updating that table, in addition to the pocket pcs, and the webpages are NOT subscribers and NOT using replication. So my webpages often get this error...
The identity range managed by replication is full and must be updated by a replication agent. The INSERT conflict occurred in database...Sp_adjustpublisheridentityrange can be called to get a new identity range.
I tried using the Sp_adjustpublisheridentityrange store procedure before attempting an insert, but it did not correct the error. After sometime the error does go away, however. If I attempt the insert through the webpage, I get the error. If I attempt the webpage insert about three times, the transaction completes. But if all the rows are processed and removed from the table, my webpage errors everytime.
1) Using SQL Replication on Pocket PCs running embedded VB and SQL Ce
2) Publisher is automatically adjusting identity column on subscribers and publisher
3) When webpage attempts to insert into empty table error occurs