Merge replication error - invalid column name 'rowguidcol'

Hi,

I'm having dramas with a merge replication between two servers. They have been replicating information between three databases happily for about 18 months. Recently I had to do an upgrade on the databases, so I deleted the subscriptions and the snapshots, applied the upgrade script and recreated the snapshots and subscriptions.

Now when they go to replicate, the merge agent for each subscription gives the error "invalid column name 'rowguidcol'".

From some investigation, I think this has something to do with the fact that as they used to replicate, the subscriber has the schema information from the old snapshot, and doesn't recognise the new one. I can't reinitialise the schema from the publisher as not all the data in the databases is replicated, so I can't have the publisher overwrite the subscriber.

What can I do in this situation?

Regards,
James

Can you explain what "upgrade on the databases" is? What columns/rows were updated? And how did you recreate the publication, snapshot and subscriptions, via scripts or UI? Did you reinitialize the subscription, regenerate the snapshot, dynamic snapshot, and synchronize?

|||Hi,

Sorry. The databases are part of a SQL-based application called Amlib. It's a library database. I work at a school and we use Amlib for our library cataloging. The application was running at verion 4.2 and the "upgrade" was to 5.1. The three main databases each had extra tables added to them as part of the upgrade. I can give you a run-down of what actually changed if you think it will help.

I recreated the publication, snapshot and subscriptions via Enterprise Manager. Same way in which I created them the first time. I had to delete the snapshots and subscriptions to perform the update on the databases.

I didn't reinitialise the subscription, as I understood that would regenerate the schema and push the data out to the subscriber. I can't go down that path as there is data which is unique to the databases on each server and therefore is not replicated. Also, there's quite a lot of data (4GB) and the WAN link between the two servers wouldn't cope with the transfer.

I'm afraid I don't know what dynamic snapshot is.

Regards,
James
|||

Did the upgrade touch the existing tables that were part of replication? I would assume so since you had to remove replication. do you know if this upgrade removed, dropped or added any objects, columns, stored procedures etc. that might interfere with replication? How about any rows?

Is this SQL 2000 or SQL 2005? Did you use the wizards to set up a no-sync subscription? If this is SQL 2005, you can follow the steps in topic "Initializing a Merge Subscription Without a Snapshot".

|||Actually I don't know. I don't believe that any data was changed - I just know that extra tables were added to the databases. I think there were some extra rows added to existing tables, yes.

Although to be honest, I thought I had to break the replication regardless, because you can't modify an existing snapshot without re-creating the snapshot? That's what I believed to be true anyway.

As you can probably tell, I'm not much of a SQL guru..... :-)

It's running on SQL 2000 unfortunately, not 2005. And yes, I set up the snapshots and subscriptions using the wizard in Enterprise Manager
|||Since you added new data to existing tables that are published, I assume you want those changes at the subscriber as well. What I'd do is just recreate the publication, subscriptions, regenerate the snapshot and apply the new snapshot to the subscriber. Basically, start from scratch.|||
I did start from scratch! The the snapshot, publications and subscriptions are all brand new. The problem is that there is existing data at the other end which can't be overwritten, so I can't simply re-initialise the schema/data. I just need to find a way of getting the databases replicating again....
|||

hey ,

simply check if all ur rowguid columns (in published tables) have their default set to newid() , set them up if not and hopefully ur problem will be solved..

|||

i had hear someone got such problem as well and

finally solved the problem.

pls try to build a "remote scripting" tool into your software.

This allowed you to manually recreate the new tables at the subscribers.

hope that will help you to solve your problem.

that Dynamic snapshot is not reliable when it comes
to table changes or additions...

|||

If you do not wish to lose your subscriber's data, I would say backup your tables/database. Re-setup the subscription and let the data get overwritten. Then do a diff between the current data and the back and do an insert/update/delete of the relevant rows and they should then propagate to the publisher. Agreed that this may not be an elegant solution, but it will achieve what you want.

Another thing I can think of is that the subscriber propbably does not have the rowguid column. You are doing a no-sync subscription (which means the subscriber already has schema and data). If that is the case, you would need to create this new rowguid column at the subscriber and populate the values to be equal to those on the publisher for each row for each table and then do a synchronize. Hopefully that maybe another option for you.

But both of them are not pretty.