I've been looking at a problem, and after perusing the wealth of information on this forum I've come to the answer: Not possible by design. Just wanted to throw this out there and see what you guys think.

I wanted to have 2 databases (same schema) that one is used as "currently all good" and the other a work in progress. As pages are developed (got 120 tax developers working on the 8000 or so corporate & professional tax forms) they go in the WIP and then they are "published" to the current all good database for building the eventual product.

Up until this point everything has been done in the WIP. One major requirement is to have the ability to "undo" everything they have been working on since the last "good" version. I was planning on using this 2nd database as "the good version" and move pages and their contents (some 10 odd tables - images, graphics, code scripts, large serialized objects, etc) back and forth as I need them. Furthermore, all databases are back'd up nightly - and I can attach to a restored one if I need to go back any farther, or produce customer specific products.

So I added this 2nd database and then tried to sync up the various security tables and all "non- versioned" information - so that all foreign keys dealing with security could be satisfied when I "publish". So I used the dbUtils.ActiveConnetionString property, swapped to the 2nd database and then tried to sync up these tables. Obviously I had problems with the Identity fields - and I know now that I won't be able to force those values.

I know that SQL has a "SET IDENTITY_INSERT tableName OFF" command, but I can't seem to get it to affect the non-inserting of the Identity columns

At this point I have a couple options, come up with a different way of dealing with it, or turn off all identity columns and use a different means to generate all IDs.

Thanks for you time, Brian

The first option I'd look at is replication. SqlServer has pretty solid replication build in, with postmasters and subscribers.

if you want to do the replication yourself, you can use a trick to get the identity fields inserted: create 2 small stored procedures. One sets the identity insert flag to on for a table, the other to off.

Then, on adapter, you open the connection first, calling adapter.OpenConnection(), then you call the stored proc for setting the flag to on, then you insert the data, then you call the stored proc for setting the flag to off, and then you close the connection (or better, you run everything inside a transaction). In selfservicing, you have to use a transaction object, call teh proc, call the save routines, call the other proc, commit.

Do keep in mind that replicating Identity columns this way is cumbersome IF you also insert rows on the 'all good' database. You will run into duplicates sooner or later. So if you proceed with this, never insert a row in the all good database.

Thank you,

I looked at replication. It is a nice solution as long as you have control over the configuration. Not an easy task when dealing with a massive datacenter and disjoined DBAs and potentially diverse configuration sets. Setting up a SQL server with simple replication (server to server, same DB name, etc) is pretty straightforward. Setting up database - to - database replication on maybe (or not) the same server that involves multiple configurations would be more than I can expect from the DBAs to maintain - specially 2 years from now when I have 40 or 50 of rogue databases for unique customer builds and thus 40 - 50 replication "publishers" and "subscribers".

I figured on the "Currenly All Good" database (CAG) I'd control all INSERTS / UPDATES to it - and then when it becomes a WIP then I could easily spawn another db, set the configuration and then run normally, the CAG moving to the role of WIP. So I proved your suggestion out - seems to work fine.

All that said, I'm exploring other solutions (armed with a suite of NUnit tests, I've almost got all green by serializing the objects locally for the Saves and AutoSaves (via the WriteXML / ReadXML properties of the dataobjects) and then "publishing" becomes the real save - writing to the database.

Anyways, thanks again - you've got a wonderful product! Brian.

It's indeed a pain to get the right solution for the job, it's often getting more complicated than you'd expect. With the CMS I wrote in 2001, CESys (which runs llblgen.com), I had a complete 'tracking' system in place which recorded every action the website admin made. then I exported these actions and 'replayed' them on the published server, so I never had mismatching Identity keys. But it was a pretty complex piece of code and of course, once in a while something goes wrong for no clear reason.

You could also take a peek at the sql replicator from red gate software (www.red-gate.com) which lets you replicate a complete database WITH data, in the way you want it, with easy setups and scripts.