DBCC CHECKIDENT (jobs, RESEED, 30) does not work

Hi,

I'm trying to set up merge replication and when I try to synchrinize with the subscriber I get the error "invalid column name ROWGUIDCOL". Following an article it recomended to reseed the identity columns on the subscriber so that there is no conflict. Now the tables with the identity column have the "Not for replication "option on. Using DBCC CHECKIDENT (table_name, RESEED, some_value) does not change the SEED when I check. SQL server help file remarks on DBCC CHECKIDENT say that if the column was created with the "Not for replication " option on it cannot change the value.

From sql server help file:

Remarks

If necessary, DBCC CHECKIDENT corrects the current identity value for a column. The current identity value is not corrected, however, if the identity column was created with the NOT FOR REPLICATION clause (in either the CREATE TABLE or ALTER TABLE statement).

Any way around it or any help so I dont get that "invalid column name ROWGUIDCOL" error?

Thanks allot. Looking forword

George

I had a similar problem before, but my problem was that I add a column with the name ROWGUID but I did forget to mark this field as ROWGUIDCOL, then the replication process make another ROWGUID field but with other name. Check that this is not the case.

I hope this can help you.

|||

Can you turn on profiler and see if a query failed due to invalid column name ROWGUIDCOL?

Thanks.

|||

Thanks for the advice

Digging a bit more Using the SQL Profiler I can locate the error to happen when sp_MSaddmergetriggers executes

This happens on the subscriber when I start the merge agent from the publisher.

George

|||

Hello George,

More questions:

1. Before you setup merge replication between 2 servers, does users table already available on both servers? Can I assume they have the same schema?

2. If tables available on both servers already, did you setup merge replication subscription using "NOSYNC" (do sp_addmergesubscription, and let @.sync_type = 'NONE')?

3. Do both tables, non of them has rowguidcol column, one has, both have?

4. Do you have a long table name?

5. I assume both publisher and subscriber are SQL server 2000.

Thanks.

This posting is provided AS IS with no warranties, and confers no rights.

|||

Hello George,

Please try this, running the follow DDL on the subscriber database.

ALTER TABLE [table_need_to_be_merged] ADD rowguid uniqueidentifier ROWGUIDCOL

Drop the subscription and re-create it with "NOSYNC" option. (SELECT "No, the Subscriber already has the schema and data" from the "Push Subscription Wizard" on the "Initialize Subscription" Page).

The subscription is created and the error message "Invalid column name 'rowguidcol'" should be disappeared.

If this method is still not working for you, I may need to look at your table schema.

Thanks.

This posting is provided AS IS with no warranties, and confers no rights.

|||

Hello Zhou,

Answers to your questions

1. Both databases are available on both servers with the same schema

2. I used NOSYNC frof the wizard

3. One of them (the publisher ) has rowguid column filled. On the subscriber I used your script to created it but is not filled. The merge agent says "No data needed to be merged"

4. The longest table name has 56 characters.

5. Both publisher and subscriber are SQL server 2000.

Thanks

|||

1. So you can create the merge sync by publication and subscription.

2. After you set up the sync and there is no data merged between the publisher and subscriber.

3. Try to update all your rowguid column on the subscriber with newid() and try to sync.

4. if no data sync from step3, try to do a dummy update such as update [table name] set column1 = column1 and do sync.

Thanks.

This posting is provided AS IS with no warranties, and confers no rights.