Stored Procedure to create a stored procedure

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

Stored Procedure to create a stored procedure

Author

Topic

newuser001
Starting Member

19 Posts

Posted - 2009-05-15 : 13:01:05

I am writing a stored procedure to create a stored proc on all my servers in the environment
and one more stored proc which goes to all servers and executes a stored proc
all the servers are add to linked servers , i am thing to use openquery too

any suggestions or some one already tried or any links ..etc please

thanks..

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-15 : 13:07:15

why using a stored procedure for this? can i ask what exactly you're trying for? transfering stored procedures from server to server?

newuser001
Starting Member

19 Posts

Posted - 2009-05-15 : 13:13:51

i want to create sp_help_revlogin on all the 2005/2000 server and similarly execute it

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-15 : 13:18:38

cant you just script out the sp from the existing db and then apply it to db in each of the other servers?for executing you can use ssis.

newuser001
Starting Member

19 Posts

Posted - 2009-05-15 : 13:30:33

Sorry i didn't get you..
I have the stored proc which i want to create in all servers

(then apply it to db in each of the other servers?) how do u apply it to db of all server ??

yep u r right for execution i can use ssis..

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-15 : 13:59:54

do you automating the creation of stored proc in all servers?

newuser001
Starting Member

19 Posts

Posted - 2009-05-15 : 14:21:04

yep i want to automating the creation of stored proc in all servers

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-15 : 14:23:21

then use a sql task and extract definitions of required from catalog view sys.sql_modules and then use it as query for next sql task to be created in all your servers inside a for each loop in ssis.

newuser001
Starting Member

19 Posts

Posted - 2009-05-18 : 14:20:31

hey sorry i could not get u ....
can u please explain me.....

thanks

robvolk
Most Valuable Yak

15732 Posts

Posted - 2009-05-18 : 14:30:30

Save the sp_help_revlogin code to a SQL script file, then use this:

http://weblogs.sqlteam.com/robv/articles/4099.aspx

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-19 : 10:22:11

quote:
Originally posted by newuser001

hey sorry i could not get u ....
can u please explain me.....

thanks

you get the definition of procedure from catalog view sql_modules


select m.definition from sys.sql_Modules m
join sys.objects o
on o.object_id=m.object_id
where o.type='P'

save this in a variable created in ssis and then use variable as source for sql task using expression builder to create them in all servers. you require a for each loop to repeat this for each server.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources