Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
mistux
Yak Posting Veteran
90 Posts |
 Posted - 2005-08-31 : 12:01:00
|
| I am getting frustrated with doing what I thought would be a very simple task: Getting all my stored procedures and functions and views into a different dastabase.What I have is this: Database A: my development database that has tables, views, stored procedures and functions.Database B: just tables that have been upsized from an aging Acccess database. I had to rename the tables from "T-" to "tbl" and some of the field names inside some of the tables accordingly.I tried to create all my things from A (except for tables) inside of B by going to A and generating scripts then taking those scripts and going into Query analyzer and running them in B. Many things did not get created much to my surprise. I figured out (correctly I hope) that the items that did not get created were not created due to the fact thqat they relied on an item that had not been created in the script yet, so SLQ decided not to make it at all. (Right/wrong??) For example; if a sp used a view, but that view didn't get created in the script before the sp, the sp would not be created. I think that is what happend.Anyway,1. Is there a way to make it so that the script runs and could tell SQL not to "not make" something if it finds a "dependency" (if that is a valid term here) not existing yet? Kind of like, "hey, just make what I tell you, trust me they will all be there at the end."2. Is there a better way to get all my items from one databse to the other (keeping in mind that the database B has the tables that I want to use and database A has all the other things).Thanks.MichaelFor all your DNC needs for your CNC machines. www.mis-group.com |
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2005-08-31 : 12:24:49
|
| Scripting is an excellent way to do this, and SQL Server's default style for creating stored procedures, etc. is to just do it and expect you'll make sure all the components are in place when the procedure is used (compiled). I believe one distinct exception is anything that requires a specific user name such as creating a view owned by someone other than dbo. If you are not sure about the dependencies and getting things in the right order, you can have Enterprise Manager generate the scripts for you, all into a single file, and it usually accounts for those dependencies in picking the order it puts the commands in the script for you.An alternative approach, although I don't think it's nearly as efficient, is to use Enterprise Manager's Export feature and select Move Objects between SQL Servers, and then click on the button to allow you to select specifically what objects to move.---------------------------EmeraldCityDomains.com |
 |
|
|
mistux
Yak Posting Veteran
90 Posts |
Posted - 2005-08-31 : 15:25:29
|
| I found that I had to run the exact same script (minus the Drop statements) several times to get all of the items in. In some cases I had to make a new script by cuty and pasting the original in but in a differnet order. That seems wrong to me.MichaelFor all your DNC needs for your CNC machines. www.mis-group.com |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-09-01 : 01:19:43
|
| Maybe the sysDepends table on your DEV system has got out of sync. This is, unfortunately, not hard - e.g. I think this will do it:CREATE PROCEDURE SP_ACREATE PROCEDURE SP_B which Executes SP_ADrop and Recreate SP_AApplication still works fine, sysDepends may no longer know that SP_B depends on SP_AA complete database script should deal with making tables first, then Views, etc. and Triggers and SProcs last - but if for any reason a Create View fails then its going to cause trouble all the way down the rest of the script.I would suggest running the script in sections, (e.g. all tables, then all views, etc.) and checking that there were no errors before proceeding.I think its a bit unfortunate that EM creates scripts with the DROPs all at the top - we script our SProcs, for example, to have the DROP and CREATE one after the other - so each create does a pre-delete of any existing SProc, that way its easy to just re-run the whole script if some failed to create the first time (actually, they won't fail to create [in a way that running them again will fix!], but you might get sysDepends errors that a second run will fix - although as I don't really have any faith in sysDepends I'm not sure why I bother with that step ...)Kristen |
|
|
|
|
|
|
|
|
Combining 2 databases