nWorx nWorx - 1 year ago 74
SQL Question

No Updates possible with replicated database and updating via tableadapter

I'm maintaining an old application. I'd to start a merge replication between two servers.
But afterwards I get these error messages "Updating columns with the rowguidcol property is not allowed."

Is there really no way to use the tableadapters for updating anymore? Do I have to refactor this application in order to be able to do a merge replication?

Thanks for input.


I've checked the automatic generated XSD files and discovered that the in each updatestatement every field is updated. Some columns are from type unique-identifier and even if you cannot change these values from the application the statement goes like that

update myTable set uniquekeycol = oldValue .... where actualPrimaryKey = value

our testdatabase has the same scheme but is running on sql2008 and it looks like the application is working without any changes. (i've just created a merge replication without any subscribers).

it's getting stranger and stranger

Answer Source

Replication adds triggers to the tables to capture changes and those triggers prevent the update of the replication id column (the one with the rowguidcol property). Considerations for Merge Replication:

Addition of uniqueidentifier Column

Merge replication uses a globally unique identifier (GUID) column to identify each row during the merge replication process. If a published table does not have a uniqueidentifier column with the ROWGUIDCOL property and a unique index, replication adds one. Ensure that any SELECT and INSERT statements that reference published tables use column lists. If a table is no longer published and replication added the column, the column is removed; if the column already existed, it is not removed.

You're going to have to edit your dataset and remove the replication column from the projection list of your table adapters.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download