Please start any new threads on our new site at We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 Replication (2005)
 Identity Specification Changes After Replication

Author  Topic 

Starting Member

1 Post

Posted - 2014-04-16 : 12:46:31
Hello all --

I generally work on the programmer/user side of SQL (i.e. I'm not a DBA and my knowledge there is somewhat limited), so please pardon and correct any incorrect use of terminology, etc.

Simply put, we employ two separate SQL server setups that I'll call production and test. Every night, the records from the production system are replicated to (and overwrite) everything on the test system, so each day we start with a fresh copy of what's in production.

A great many of the tables on the production system have a sequence column (SEQ) with the Identity Specification set to Yes. These are all setup correctly and function perfectly.

After replication, however, the Identity Specification for the SEQ columns in the test system tables is set to No (the production tables are unaffected). This creates many headaches for programmers such as me as it results in many "Cannot insert the value NULL into column..." errors.

My approach thus far has simply been to use SSMS to change the Identity Specification to Yes, but the number of tables involved continues to increase and that's really becoming cumbersome to do everyday.

I'm now considering compiling the SQL change scripts for every table into a single large one and running that each day. The number of records in these tables is small enough (I think) that it wouldn't be a show-stopper; but, I really can't help but think that there is simply a setting in the replication logic that could be changed that will solve my problem at the source.

Is there? If not, any other suggestions?

Thanks for reading and please let me know if I can provide any further information.


- Advertisement -