December 13, 2006 at 3:31 pm
This question refers to SQL Server 2005, using merge replication. The replication topology is composed by just 2 servers. This an OLTP environment that is exposed to customers through a web site, so the system needs available as much as possible.
We have to perform a mass update of a column in all records of a table (about 20,000 records) which is being replicated. This update needs to be done through a .NET application (using ADO.NET), since the update requires a complex recalculation using the original value as the input, because an important business logic has changed.
My questions are:
1) What is the best strategy to perform a mass update in a table that is being replicated through a merge replication, in order to minimize downtime and have the operation completed as soon as possible?
2) We tried to test the operation in a staging environment (which mimicks the production environment), but we had the following error message coming from batch update job for each update statement:
A trigger returned a resultset and/or was running with SET
NOCOUNT OFF while another outstanding result set was active.
As note, we have not defined any triggers on our tables. The only existing triggers are the ones created by the merge replication engine. So what could be causing this error?
December 14, 2006 at 2:50 pm
Are you updating one row at a time? If so, I would suggest selecting the data into a "work" table that isn't replicated, perform the data manipulation in the work table, then update the live table with a single Update statement or in small batches with a pause between them.
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply