| Author |
Topic |
|
smorty44
Yak Posting Veteran
93 Posts |
 Posted - 2007-08-20 : 10:12:10
|
| I'm using sql server 2000 and I want to copy a stored procedure from a master database to another database. How do I write this in T-Sql to use often as needed? |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-08-20 : 10:23:25
|
You can use the scripting tool in Enterprise Manager to get all SP's in "master" database.Then copy the code to "another" database and run the script.
E 12°55'05.25"N 56°04'39.16" |
 |
|
|
smorty44
Yak Posting Veteran
93 Posts |
Posted - 2007-08-20 : 10:53:29
|
| I need to write this as a stored procedure in a routine to run when called using T-SQL. |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
smorty44
Yak Posting Veteran
93 Posts |
Posted - 2007-08-20 : 12:16:50
|
| Excuse me, my mistake, I don't mean "master" db, I mean a "main" database that I am creating. I have a stored procedure that inserts data and automatically creates standard tables. I'd like to be able to copy that sp to any new dbs. I'm looking for the T-Sql code that will copy an sp from one database to another. |
|
|
|
Zoroaster
Aged Yak Warrior
702 Posts |
Posted - 2007-08-20 : 13:13:29
|
quote:
Originally posted by smorty44
Excuse me, my mistake, I don't mean "master" db, I mean a "main" database that I am creating. I have a stored procedure that inserts data and automatically creates standard tables. I'd like to be able to copy that sp to any new dbs. I'm looking for the T-Sql code that will copy an sp from one database to another.
In Query Analyzer change results to text (or file) and then type:sp_helptext [procedurename]It will give you the SQL used to create the proc, you can then copy it and reapply it elsewhere. |
|
|
|
Zoroaster
Aged Yak Warrior
702 Posts |
Posted - 2007-08-20 : 15:07:14
|
| You can also right click the SP in Query Analyzer and script object to file or to clipboard as CREATE. |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
smorty44
Yak Posting Veteran
93 Posts |
Posted - 2007-08-20 : 16:19:46
|
| I'm just doing what the bossman has requested. He does not want me to copy the sp and paste it into new dbs, but have a procedure that copies it over for us. any suggestions? |
|
|
|
rcurrey
Starting Member
30 Posts |
Posted - 2007-08-20 : 16:28:29
|
| From BOL:A user-defined stored procedure can be created only in the current database (except for temporary procedures, which are always created in tempdb). The CREATE PROCEDURE statement cannot be combined with other Transact-SQL statements in a single batch. From this, I would say that you CANNOT create a stored procedure that will create another stored procedure somewhere else.Thanks,Rich |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-08-20 : 16:32:40
|
Not even with the help of dynamic sql?
E 12°55'05.25"N 56°04'39.16" |
|
|
|
rcurrey
Starting Member
30 Posts |
Posted - 2007-08-20 : 16:37:18
|
quote:
Originally posted by Peso
Not even with the help of dynamic sql?
E 12°55'05.25"N 56°04'39.16"
Now that could get interesting. Possible issues - 8000 character string limits? Do you have an idea, Peso?Thanks,Rich |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-08-20 : 16:46:02
|
If you have a SP, you can have a parameter of TEXT datatype which means you have access to this parameter in the SP and you are not longer bound to the 8060 byte limit.
E 12°55'05.25"N 56°04'39.16" |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-08-20 : 17:07:56
|
Or with SQL Server 2005, you have NVARCHAR(MAX).The function sys.fn_dump_dblog is more than 10000 charactersdeclare @a nvarchar(max)set @a = ''select @a = @a + textfrom master..syscommentswhere encrypted = 0 and id = -1024577103ORDER BY number, colidprint @aprint datalength(@a)
E 12°55'05.25"N 56°04'39.16" |
|
|
|
rcurrey
Starting Member
30 Posts |
Posted - 2007-08-21 : 10:49:06
|
| Thanks for the learning. Didn't even occur to me.Thanks,Rich |
|
|
|
|
copy sps from one database to another