Results 1 to 11 of 11
  1. #1
    Join Date
    Apr 2009
    Posts
    25

    Unanswered: Foreign Keys Causing Grief In Create Tables Scripts

    Hi me again.

    Using SQL Server 2005 I'm using DMO in a cursor to generate a file that holds all the create table statements (including constraints) in a text file.


    CREATE TABLE X
    CREATE TABLE Y
    CREATE TABLE Z
    ...


    The problem I'm having is when the file is executed the creates are in no specific order so when it tries to create X (X has an FK relationship with Z) it fails as table Z hasn't been created yet.

    I've tried creating the tables without any constraints then adding them later but it's got so dirty and unstable, there must be a simple answer???????

    DOes anyone have any advice?

    Thanks


    Chris

  2. #2
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by chirs_hurley_FDB
    I've tried creating the tables without any constraints then adding them later but it's got so dirty and unstable, there must be a simple answer???????
    Well we always do the CREATE TABLEs first, and then use ALTER TABLE to define the foreign keys and that is working fine.

    What do you mean with "dirty" and "unstable"?

  3. #3
    Join Date
    Mar 2009
    Posts
    349
    or just create your tables in the right order.

  4. #4
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    Create tables first, constraints after.

    Better still, use a tool such as Visual Studio TS Database Edition that will do it all automatically for you:
    Development Edition Home

  5. #5
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    I wrote an app to do it long ago. it's on codeplex now:

    ScriptDB - Home

  6. #6
    Join Date
    Apr 2009
    Posts
    25
    Thanks for the replies.

    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:

    Code:
    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?

    Chris

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Can we backtrack a second.

    Are you simply trying to work out how to make copies of a database (structure and data)? What is the overall purpose? There might be a MUCH more straightforward way of doing this.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Apr 2009
    Posts
    25
    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.

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    You know you can do this by:
    DROP database DestDB
    BACKUP database SourceDB
    RESTORE database SourceDB as DestDB

    right?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  10. #10
    Join Date
    Apr 2009
    Posts
    25
    Hi Pootle,

    The scripts are the important part here. I need to script all the tables and all the indexes into two script files then build the new DB off them.

    It's rubbish I can't see how to do it completely autonimously if I'm to include the constraints.

    I think I better think it out again

  11. #11
    Join Date
    Mar 2009
    Posts
    349
    how does pootles rec not accomplish this. I have some scripts at home I might be able to post that do the table creation in the proper order, but Pootle solution still works.

    Why can't you use a backup of a clean empty database?

    pootles solution can be scripted.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •