Generate SQL script problem
-
Hey,
I have 2 views - ViewA and ViewB - ViewA uses ViewB in its query. When I script these 2 views (using the Generate SQL script option in Enterprise Manager), they are scripted alphabetically, so when I run the script I get an error that ViewB is an invalid object name. When I move ViewB's script before ViewA's, however, everythings fine.
My question: Is this a bug? is there a way to make sure that the views are scripted in the right order? It would be really bad if everytime I generate an SQL script for my DB i'd have to manually order the views...
Thanks
-
I don't know of any way of getting SQL to script it in the right order, it's a real pain in the backside when it starts loosing dependancy information like this.
-
Thanks.
I agree. I think it happens only with views (because they are evaluated when the script runs) and not with SP. I hope...
-
Sorry to dissapoint.
I think the scripting engine simply processes objects alphabetically. So if object 'a' depends on object 'z' and you want to reload using the generated script... tough luck!
Best Regards,Otto Schreibke
The future is a foreign country, they do things differently there.
(Stolen from Arthur C Clarke) -
Correct. The same thing happens with SPs. It's a big pain.
-
Guess the only workaround would be to name the dbobjects with the alphabetical scripting in mind and be creative with making the names both meaningful and in the required alphabetical order...
**ASCII stupid question, get a stupid ANSI !!!** -
Hmmm... Something still doesn't work out: I scripted my views (the same exact ones) from another database (which was itself loaded from script) and they were scripted in the correct order - not the alphabetical order.
This is so weird - everything was ok until I edited a few views today... I didn't change their names or anything like that. I have one DB on which I do all me developping, and another on which was generated from script a few days ago. Now, when I script the problematic views from the old one they come out in alphabet order, so running the script causes problems. But if I script the views from the new DB, they script fine and no problem running the script.
HOW CAN THIS BE???
-
It's called a 'feature'
-
well - here's another "weird feature" that I came across in my db...I scripted 2 views that have dependent views...one scripted in alphabetical order and the other one didn't script the dependent view at all....
however, no time to beat my brains out on this one....
**ASCII stupid question, get a stupid ANSI !!!** -
OK GUYS AT LEAST A PARTIAL SOLUTION!
The scripting is done in the order the views are modified. So, i just deleted some white spaces from the views in the order i want them scripted (luckily, only 4 views) and magically, the script came out in the right order (not alphabetically).
Conclusion - the scripting order is by the modified date or something like that...
Hope this helps anyone!
-
that's certainly helpful to know!
Now if only someone would tell me why one of my scripts included dependent objects and the other one didn't - I'd be even better informed!!!
**ASCII stupid question, get a stupid ANSI !!!** -
Maybe it's because the checkbox under the tab "Formatting" that says something like "script all dependant objects" was checked?
-
nope! conditions were identical for both scripts...
**ASCII stupid question, get a stupid ANSI !!!** -
this is how I do it and it always works, it involves using DB Ghost http://www.dbghost.com
use the data and schema scripter to script out the objects to a directory.
run the change manager using option 1 - build a database.
answer the questions of the wizard and remember to choose to record the build to a SQL file.
the SQL file will have all the dependencies in the correct order every time no matter how complex your relationships are within your database.
regards,
Mark Baekdal
+44 (0)141 416 1490
+44 (0)208 241 1762
Build, Comparison and Synchronization from Source Control = Database change management for SQL Server
-
use this script from the contributions, and change the where xtype='U'
to be where xtype in('U' ,'V')
http://www.sqlservercentral.com/scripts/contributions/759.asp
that will give you tables and views in dependancy order, which is the same order you need to script them out.
I use this in combination with a function i wrote that exports the DDL of tables.
you would probably just use this to run SP_HELPTEXT VIEWNAME to get the DDL for the views.
[after posting note] oops wrong script. this one does foreign keys, there is a different script that uses sysdepends. i will find that script and post it here. it still is the same thing...the results end up in dependancy order, and that is the order to write the scripts out.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply