SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Replication (2005)
 Identity Specification Changes After Replication
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

hawkeye91
Starting Member

1 Posts

Posted - 04/16/2014 :  12:46:31  Show Profile  Reply with Quote
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.

Scott
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000