| Author |
Topic |
|
golden_manish
Starting Member
13 Posts |
 Posted - 2005-12-10 : 07:43:59
|
| Hi All,I have scripted all the objects of a database to the disk, each object has different files.I am then running all the scrips one by one. The problem i am facing is that many of the objects are dependent on other objects. Ex. View2 needs View1, so it is paramount that View1 is created first.How do i ensure that all the primery object (Ex. View1) scripts are executed first.Any idea how microsoft does this, when scripting the database.Thanks in advance.Manish |
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2005-12-10 : 13:12:51
|
| Why did you script them all out as individual objects??? There's no easy magic way to do this with SQL Server 2000. We have a database release tool created that allows us to specify an order in the release folders. That takes care of the issue for us. You would have to get the order though by lookin at the dependencies. You could then create a "wrapper script" using something like osql commands to run all the scripts in order.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
 |
|
|
influent
Constraint Violating Yak Guru
367 Posts |
Posted - 2008-06-26 : 16:39:06
|
| I find it really annoying that neither Enterprise Manager nor Mgmt Studio nor sqlpubwiz will create the full database creation scripts with all the dependencies in the correct order. I need to automate creation of two databases nightly with a small subset of the data for integration testing and I would love to not have to be updating the scripts all the time to manage the dependencies. Isn't there a tool out there that can do it for me?? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-06-26 : 16:40:16
|
quote:
Originally posted by influent
I find it really annoying that neither Enterprise Manager nor Mgmt Studio nor sqlpubwiz will create the full database creation scripts with all the dependencies in the correct order. I need to automate creation of two databases nightly with a small subset of the data for integration testing and I would love to not have to be updating the scripts all the time to manage the dependencies. Isn't there a tool out there that can do it for me??
Fix your dependencies and it'll script in the correct order. There's a reason why you either get warnings or errors when the parent objects don't exist.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
influent
Constraint Violating Yak Guru
367 Posts |
Posted - 2008-06-26 : 17:41:49
|
| I'm not sure how I'm supposed to "fix my dependencies." I have a view that uses one table and EntMan decided to put the creation of the view before the creation of the table in the script. Are you saying I can control that somehow? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-06-26 : 17:49:32
|
| Script out tables into one file, stored procedures into one file, views into one file, and functions into file. Run them in this order:1. Tables2. Views3. Functions4. Stored proceduresI concatenate them together to get them into one script file using the DOS type command. If you name them alphabetically, the concat will put them in the correct order.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
influent
Constraint Violating Yak Guru
367 Posts |
Posted - 2008-06-26 : 17:51:23
|
| I was afraid you'd say that. That means I can't use sqlpubwiz to automate the process. :( |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-06-26 : 17:51:38
|
| To fix the dependencies, drop your objects and then create them in the correct order. See sp_depends for the dependencies.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-06-27 : 22:52:16
|
| Sql2k comes with scptxfr.exe, you can run it in schedule to script the db. |
|
|
|
|
Scripting the Database.