| Author |
Topic |
|
shaoen01
Yak Posting Veteran
78 Posts |
 Posted - 2006-01-03 : 09:59:43
|
| Is there anyway to import/export stored procedures instead of backing up the whole database? Thanks |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
|
|
shaoen01
Yak Posting Veteran
78 Posts |
Posted - 2006-01-03 : 10:40:25
|
| Hi,I replied at the link you provided. I didnt take notice of your username and didnt know that you were the one who posted that script. |
 |
|
|
afrika
Master Smack Fu Yak Hacker
2706 Posts |
Posted - 2006-01-03 : 10:50:38
|
| You could generate SQL scripts for all sp objects |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-01-03 : 10:53:31
|
| Only displays the first 4000 characters doesn't it Madhi? Or am I doing something daft?I was trying:Select routine_definition from DBname.information_Schema.routines order by routine_nameKristen |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2006-01-04 : 00:52:34
|
| >>Only displays the first 4000 characters doesn't it Madhi? I am not sure on thatIf thats the case, then I would prefer Generate Script approachMadhivananFailing to plan is Planning to fail |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2006-01-04 : 01:19:42
|
| sp_help syscomments shows syscomments.text is nvarchar(8000)-----------------[KH]2006 a new beginning |
|
|
|
shaoen01
Yak Posting Veteran
78 Posts |
Posted - 2006-01-04 : 01:28:19
|
| I tried to use your query above to save my stored procedures in a script. How will i be able to get the stored procedures in the script into another database? And is it possible to select which stored procedure to export? However, i met with the following errors:Password:SQLState = 37000, NativeError = 170Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near 'Assistant'.SQLState = 37000, NativeError = 8180Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared.NULL |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2006-01-04 : 03:05:11
|
| Post the query you usedMadhivananFailing to plan is Planning to fail |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-01-04 : 08:03:26
|
| "sp_help syscomments shows syscomments.text is nvarchar(8000)"except that 'coz its nvarchar the "length" parameter has to be divided by 2 - so its nvarchar(4000)syscomments stores successive "blocks" of the code in additional records. However, sp_helptext fails to stitch them together properly (it incorrectly inserts a CR LF between each "block"). But generating a script in EM doesn't have this failing, so it must be doing something smarter!"How will i be able to get the stored procedures in the script into another database"Just EXECute them on the "other" database??"And is it possible to select which stored procedure to export?"Enterprise Manager can generate a script for all the objects in the database (right click the database, "All tasks", "Generate SQL"). You could choose just the SProcs you want, and then have E.M. generate you a script (or it can put each object into a separate file, if you prefer)Kristen |
|
|
|
shaoen01
Yak Posting Veteran
78 Posts |
Posted - 2006-01-04 : 12:52:37
|
| exec master..xp_cmdshell'bcp "Select routine_definition from Interactive Store Assistant.information_Schema.routines order by routine_name" queryout "C:\Documents and Settings\Valerie\Desktop\scripts.sql" -c' |
|
|
|
shaoen01
Yak Posting Veteran
78 Posts |
Posted - 2006-01-04 : 12:56:48
|
Kristen: I didnt know that Enterprise Manager can select exactly which stored procedure to export. Thanks a lot! |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-01-04 : 13:10:21
|
| "I didnt know that Enterprise Manager can select exactly which stored procedure to export. Thanks a lot!"No problem!"exec master..xp_cmdshell ..."If you have a space in your database name you have to enclose it in [My DB Name] (same with table and column names):exec master..xp_cmdshell'bcp "Select routine_definition from [Interactive Store Assistant].information_Schema.routines order by routine_name" queryout "C:\Documents and Settings\Valerie\Desktop\scripts.sql" -c'Kristen |
|
|
|
shaoen01
Yak Posting Veteran
78 Posts |
Posted - 2006-01-04 : 13:38:15
|
| Oh ... Thanks a lot! Works like a charm! |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2006-01-05 : 02:03:36
|
It is better practice to avoid having space in the objects names that can lead you unnecessary problems. Instead use Underscore (_) in place of space for better readabilityYou are good to go if you have Interactive_Store_Assistant  MadhivananFailing to plan is Planning to fail |
|
|
|
shaoen01
Yak Posting Veteran
78 Posts |
Posted - 2006-01-05 : 02:21:20
|
| Okay thanks, i will take note of that. |
|
|
|
shaoen01
Yak Posting Veteran
78 Posts |
Posted - 2006-01-22 : 03:43:30
|
| I have managed to copy the stored procedures successfully. But cant seem to export into another database. I tried to use import/export data but it does not work. Can someone help me out. Thanks |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-01-22 : 08:40:31
|
| Generate a script, and run it on the other database.Not sure what you mean by "I have managed to copy..." and "... cant seem to export into another database"Kristen |
|
|
|
shaoen01
Yak Posting Veteran
78 Posts |
Posted - 2006-01-22 : 19:29:47
|
My "managed to copy ..." means that i have generated the script already. But when i use the import/export wizard to help me to import the script into another database it does not work.So what i did was to copy the script generated and executed it in the query analyzer and it works. I thought it could be done by using the wizards though.quote:
Originally posted by Kristen
Generate a script, and run it on the other database.Not sure what you mean by "I have managed to copy..." and "... cant seem to export into another database"Kristen
|
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2006-01-22 : 19:49:33
|
| Use Query Analyser, connect to your destination database, load your script and run-----------------'KH' |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-01-23 : 02:07:06
|
| "So what i did was to copy the script generated and executed it in the query analyzer and it works"Yup, that's the best way. I don't think you can use import/export wizard.Kristen |
|
|
|
Next Page
|
Import/Export Stored Procedures