Thrasy - It's all dynamic and as such I can't see how to create the tables in the correct order as I have little involvent during execution. For them to build in the correct order they need to be scripted in the correct order and as the tables can change (new tables added / tables removed) i can't see how to do that.
I am now creating tables first then the constraints after, I've been able to get it working to the point of my schema looks fine and is identical but I'm having issues inserting my data due to identity inserts.
I have a cursor that executes a truncate, insert, select from statement to transfer data from my source DB to the copy DB for every table however my ID fields in the copy DB are obviously not allowing me to insert into them.
I added a 'SET IDENTITY_INSERT '+ @TableName+' ON ..... before my truncate statement so the executed (on copy DB) code in the cursor will read:
EXEC('SET IDENTITY_INSERT '+@DestTable+' ON TRUNCATE TABLE '+@DestTable+' INSERT INTO '+@DestTable+' SELECT * FROM LIVE_CONTROL..'+@DestTable)
now this still returns the error
"An explicit value for the identity column in table TableName can only be specified when a column list is used and IDENTITY_INSERT is ON"
Do I really have to list out every column? We could live without the identies so if anyone knows how you can ignore them when scripting tables with DMO that would be useful?
I've no doubt there is but as usual the requirements keep changing and things get messy.
I'm building an SSIS package that can be configured to work on any database on any server. It's completely modular.
The user states which DB is to be copied, the package must then:
- drop old 'copy DBs'
- build a new 'copy DB' (Blank)
- create a 'Create Tables Script' from source DB
- create a 'Create Indexes Script' from source DB
- executes these scripts into the new DB
- populate the new DB with the data from the original
As i've said it's modular and there is to be no code on any of the servers it's all created dynamically in SSIS using a number of SSIS variables that dynamically set up connections to the DB, connections to the script files, the SQL that has to be run...
The two scripts are used in our products which is why we build the copy database up from those scripts. It provides us with a test DB that should reflect exactly the schema of the products.
I have however found the fix I was after, DMO has an inbuilt scripttype constant to ignore identities '1073741824' to be precise so this will allow me to populate my tables.