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 |
|
mike123
Master Smack Fu Yak Hacker
1462 Posts |
 Posted - 2005-03-21 : 17:40:02
|
| I have a databse structure that I want to move to another. (All tables, indexes, sprocs, etc .. )Whats the best way to do this? Just generate a SQL script? I tried this but there were a few errors that occured..Wondering if there is a better way?Thanks for any helpmike123 |
|
|
snarayan
Starting Member
10 Posts |
Posted - 2005-03-21 : 17:53:07
|
| How about backup and restore and truncating tables or use DTS |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-03-21 : 18:49:04
|
| Generate SQL script is probably the best way to do it if you don't want to transfer the data. What were the errors?Tara |
|
|
|
mike123
Master Smack Fu Yak Hacker
1462 Posts |
Posted - 2005-03-22 : 18:32:16
|
| Hi Tara!I don't have the exact errors handy, but there was about a half dozen of them. At least one had to with constraints I believe. I ran the script while the database still existed. I have now deleted and recreated an empty DB and will try again.I'm just not sure which options to script.In EM, is "script all objects" enough? Or should I go to options as well and click all table scripting options ? (indexes, keys, etc)Thanks once again!mike123 |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-03-22 : 18:33:55
|
| Select the option to script all objects, then go to the third tab and select the options to script indexes, triggers, and keys. Run that script and let us know the errors, if any.Tara |
|
|
|
mike123
Master Smack Fu Yak Hacker
1462 Posts |
Posted - 2005-03-22 : 18:56:16
|
| Here are all the errors: I believe the ending errors are due to some changes that I have to make to the SPROC's as they are no longer up to date with structural changes to tables. The "Cannot add rows" errors I am a little worried about tho, I have no real idea on this oneThanks alot for your help!Mike123Cannot add rows to sysdepends for the current stored procedure because it depends on the missing object 'dbo.dt_adduserobject_vcs'. The stored procedure will still be created.Cannot add rows to sysdepends for the current stored procedure because it depends on the missing object 'dbo.dt_setpropertybyid'. The stored procedure will still be created.Cannot add rows to sysdepends for the current stored procedure because it depends on the missing object 'dbo.dt_setpropertybyid'. The stored procedure will still be created.Cannot add rows to sysdepends for the current stored procedure because it depends on the missing object 'dbo.dt_setpropertybyid'. The stored procedure will still be created.Cannot add rows to sysdepends for the current stored procedure because it depends on the missing object 'dbo.dt_setpropertybyid'. The stored procedure will still be created.Cannot add rows to sysdepends for the current stored procedure because it depends on the missing object 'dbo.dt_setpropertybyid'. The stored procedure will still be created.Cannot add rows to sysdepends for the current stored procedure because it depends on the missing object 'dbo.dt_displayoaerror'. The stored procedure will still be created.Cannot add rows to sysdepends for the current stored procedure because it depends on the missing object 'dbo.dt_adduserobject_vcs'. The stored procedure will still be created.Cannot add rows to sysdepends for the current stored procedure because it depends on the missing object 'dbo.dt_setpropertybyid_u'. The stored procedure will still be created.Cannot add rows to sysdepends for the current stored procedure because it depends on the missing object 'dbo.dt_setpropertybyid_u'. The stored procedure will still be created.Cannot add rows to sysdepends for the current stored procedure because it depends on the missing object 'dbo.dt_setpropertybyid_u'. The stored procedure will still be created.Cannot add rows to sysdepends for the current stored procedure because it depends on the missing object 'dbo.dt_setpropertybyid_u'. The stored procedure will still be created.Cannot add rows to sysdepends for the current stored procedure because it depends on the missing object 'dbo.dt_setpropertybyid_u'. The stored procedure will still be created.Cannot add rows to sysdepends for the current stored procedure because it depends on the missing object 'dbo.dt_displayoaerror_u'. The stored procedure will still be created.Cannot add rows to sysdepends for the current stored procedure because it depends on the missing object 'dbo.dt_getpropertiesbyid_vcs'. The stored procedure will still be created.Cannot add rows to sysdepends for the current stored procedure because it depends on the missing object 'dbo.dt_getpropertiesbyid_vcs'. The stored procedure will still be created.Cannot add rows to sysdepends for the current stored procedure because it depends on the missing object 'dbo.dt_getpropertiesbyid_vcs'. The stored procedure will still be created.Cannot add rows to sysdepends for the current stored procedure because it depends on the missing object 'dbo.dt_getpropertiesbyid_vcs'. The stored procedure will still be created.Cannot add rows to sysdepends for the current stored procedure because it depends on the missing object 'dbo.dt_displayoaerror'. The stored procedure will still be created.Cannot add rows to sysdepends for the current stored procedure because it depends on the missing object 'dbo.dt_getpropertiesbyid_vcs_u'. The stored procedure will still be created.Cannot add rows to sysdepends for the current stored procedure because it depends on the missing object 'dbo.dt_getpropertiesbyid_vcs_u'. The stored procedure will still be created.Cannot add rows to sysdepends for the current stored procedure because it depends on the missing object 'dbo.dt_getpropertiesbyid_vcs_u'. The stored procedure will still be created.Cannot add rows to sysdepends for the current stored procedure because it depends on the missing object 'dbo.dt_getpropertiesbyid_vcs_u'. The stored procedure will still be created.Cannot add rows to sysdepends for the current stored procedure because it depends on the missing object 'dbo.dt_displayoaerror_u'. The stored procedure will still be created.Cannot add rows to sysdepends for the current stored procedure because it depends on the missing object 'dbo.dt_getpropertiesbyid_vcs'. The stored procedure will still be created.Cannot add rows to sysdepends for the current stored procedure because it depends on the missing object 'dbo.dt_getpropertiesbyid_vcs'. The stored procedure will still be created.Cannot add rows to sysdepends for the current stored procedure because it depends on the missing object 'dbo.dt_getpropertiesbyid_vcs'. The stored procedure will still be created.Cannot add rows to sysdepends for the current stored procedure because it depends on the missing object 'dbo.dt_getpropertiesbyid_vcs'. The stored procedure will still be created.Cannot add rows to sysdepends for the current stored procedure because it depends on the missing object 'dbo.dt_displayoaerror'. The stored procedure will still be created.Cannot add rows to sysdepends for the current stored procedure because it depends on the missing object 'dbo.dt_getpropertiesbyid_vcs_u'. The stored procedure will still be created.Cannot add rows to sysdepends for the current stored procedure because it depends on the missing object 'dbo.dt_getpropertiesbyid_vcs_u'. The stored procedure will still be created.Cannot add rows to sysdepends for the current stored procedure because it depends on the missing object 'dbo.dt_getpropertiesbyid_vcs_u'. The stored procedure will still be created.Cannot add rows to sysdepends for the current stored procedure because it depends on the missing object 'dbo.dt_getpropertiesbyid_vcs_u'. The stored procedure will still be created.Cannot add rows to sysdepends for the current stored procedure because it depends on the missing object 'dbo.dt_displayoaerror_u'. The stored procedure will still be created.Server: Msg 207, Level 16, State 1, Procedure insert_tblSavedSearches, Line 10Invalid column name 'URL'.Server: Msg 207, Level 16, State 3, Procedure select_AffiliateReport, Line 11Invalid column name 'affilID'.Server: Msg 207, Level 16, State 3, Procedure select_AffiliateReports_TEST2, Line 11Invalid column name 'affilID'.Server: Msg 207, Level 16, State 3, Procedure select_AffiliateReports_All, Line 10Invalid column name 'affilID'.Server: Msg 207, Level 16, State 1, Procedure select_AffiliateReports_All, Line 10Invalid column name 'affilID'.Server: Msg 207, Level 16, State 1, Procedure select_AffiliateReports_All, Line 10Invalid column name 'affilID'.Server: Msg 207, Level 16, State 3, Procedure select_membershipBreakDown, Line 6Invalid column name 'membershipType'.Server: Msg 207, Level 16, State 1, Procedure select_membershipBreakDown, Line 6Invalid column name 'dateEnd'.Server: Msg 207, Level 16, State 1, Procedure select_membershipBreakDown, Line 6Invalid column name 'membershipType'.Server: Msg 207, Level 16, State 1, Procedure select_membershipBreakDown, Line 6Invalid column name 'dateEnd'.Server: Msg 207, Level 16, State 1, Procedure select_membershipBreakDown, Line 6Invalid column name 'membershipType'.Server: Msg 207, Level 16, State 1, Procedure select_membershipBreakDown, Line 6Invalid column name 'dateEnd'.Server: Msg 207, Level 16, State 1, Procedure select_membershipBreakDown, Line 6Invalid column name 'membershipType'.Server: Msg 207, Level 16, State 1, Procedure select_membershipBreakDown, Line 6Invalid column name 'dateEnd'.Server: Msg 207, Level 16, State 3, Procedure select_membershipUsers, Line 8Invalid column name 'dateEnd'.Server: Msg 207, Level 16, State 1, Procedure select_membershipUsers, Line 8Invalid column name 'membershipType'.Server: Msg 2714, Level 16, State 5, Procedure select_thread, Line 65There is already an object named 'select_thread' in the database. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-03-22 : 19:06:56
|
| Ok, so you need to remove the dt_ objects from the scripting. These are used for source control and must not exist on your second database. So instead of scripting all objects, script them all then remove the dt_ ones. And yes the other errors appear to be due to stored procedures referring to columns that do not exist anymore.Tara |
|
|
|
mike123
Master Smack Fu Yak Hacker
1462 Posts |
Posted - 2005-03-23 : 14:07:26
|
| Hi Tara,I'm working on updating all the other SP's right now with invalid column names. I did take a look at removing all the dt's that were made in the SQL script.They don't seem to be in order and I have over 500 SP's that are being created. Is there a way to skip creating these ones? Even using the find tool doesnt really help here.Thanks again!mike123 |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-03-23 : 14:12:32
|
| Well, select the option to script them all, that'll put them on the right side of the first tab. Then find the dt_ ones and highlight them to remove them. So now you'll have the left side with the dt_ ones, and the right side with the ones you want.Tara |
|
|
|
mike123
Master Smack Fu Yak Hacker
1462 Posts |
Posted - 2005-03-23 : 18:09:56
|
| Hi Tara,Perfect! - I was able to get rid of all errors except for 1.Server: Msg 2714, Level 16, State 5, Procedure select_thread, Line 65There is already an object named 'select_thread' in the database.I tried dropping and recreating this SPROC is the source DB, but still no luck. Any idea what this could be?Thanks again!mike123 |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-03-23 : 18:23:56
|
| Copy the script file into notepad. Search the script file for select_thread. See if you can figure out what is wrong or post them here. I'm thinking that you may have two of them in your db, one owned by dbo, the second owned by someone else. Then when you are trying to run this script on the destination, it's trying to have the owner as dbo for both.Tara |
|
|
|
mike123
Master Smack Fu Yak Hacker
1462 Posts |
Posted - 2005-03-23 : 18:40:39
|
| Hi Tara,I found the error in the script as you suggested. However they are both dbo.select_thread. I already dropped it and recreated it, but still the same problem.any idea? seems very odd!Thanks again, mike123 |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-03-23 : 18:44:23
|
| In the script that EM generated, this stored procedure is being created twice?Tara |
|
|
|
mike123
Master Smack Fu Yak Hacker
1462 Posts |
Posted - 2005-03-23 : 19:10:46
|
| Yes, I didnt believe it myself. I triple checked.Pretty odd id say... i was expecting a diff username at leastthxmike123 |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-03-23 : 19:22:53
|
| I guess then you've got some issues in the system tables. Check out sysobjects where type equals P and also syscomments. Syscomments is where the code is actually stored. Sysobjects has a row for each object. Do you have duplicates in either of those?Tara |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-03-23 : 23:57:15
|
| As I was driving home from work today (odd how some things just come to you when you aren't thinking about it), I realized that you may have renamed an object inside Enterprise Manager. This doesn't work well with stored procs, udfs, or views. It only renames the object and doesn't rename the actual code. So your sysobjects row is fine, but your syscomments is not. So now you've got to track this down. The problem is that when you script the objects to one file, it puts all of the drops at the top and then all of the creates. Here's what you've got, most likely at least:DROP PROC SomeProc1CREATE PROC SomeProc1DROP PROC SomeProcWithADifferentNameCREATE PROC SomeProc1This probably won't be easy to find unless you script each object into their own files (third tab has this option). Then you can concatenate these files into one file via a cmd window: type C:\DirOfObjects\*.* >C:\ConcatFile.txtAs a recommendation: Renaming should be done via DROP PROC OldName, CREATE PROC NewName.Tara |
|
|
|
|
|
|
|
|
duplicating database for another web application