January 5, 2006 at 8:06 am
This might seem crazy but I am unable to create the DDL scripts that can be used to create an empty schema version of my database.
Enterprise Manager provides lots of features and options for scripting out portions of the database, which is great; but I cannot get the thing to produce the appropriate DDL to create the foreign keys successfully because it gets the ordering wrong - when I run thescript for the keys, it falls over half way through.
This cannot be a unusual thing to want to do but I can't get it right so far and I can't find any resource that tells me how.
Any words of wisdom will be gratefully received.
January 5, 2006 at 8:10 am
DBGhost Does it OK.
I use a custom method which does the table creates for all the tables and then I do all the FK stuff.
Tim S
January 5, 2006 at 8:36 am
That's what I am trying to do, actually. Here's some more info.
There is no master script, nor master scripts, from which the database can be created. The database has heaps of tables and indexes and foreign keys etc.
What I have done is to use Enterprise Manager to genereate the DDL scripts.
First I created a script to genereate the tables only; this goes to one file. Secondly, I scripted out the keys only (‘Script PRIMARY keys, FOREIGN keys, defaults and check constraints’) and this goes to another file. I partition the scripting of the other components (indexes etc) in a similar way.
I try to recreate the blank schema on another server. I create a new (empty) database and then fire up a Query Analyzer session and run the scripts created above. I get the first one done okay (no surprise). But when I run the second script, it fails part way, complaining that such-and-such an attribute does not exists so it cannot create the relationship or whatever. There are a few of these statements complaining like this. I deduce that the ordering of the statements produced is wrong and I want to get around this problem.
Does anyone recognise this situation?
January 5, 2006 at 8:43 am
I've usually had EM script fine, but I'm not sure if I've done hundreds of tables. I usually have everything in one script.
One thing you could do to debug is add a print after each creation. Then you'd know where the error occurred.
January 5, 2006 at 4:12 pm
Folks,
Thanks for your comments. I have now found the problem (I think), and a bit weird it is too.
It looks as if a corruption had occurred in the FK relationship between two tables and that that was the cause of the error. It should not have been possible but an anomaly crept in.
Repaired the FK constraint and it looks like it's okay now.
-James.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply