Copy SP using T-SQL

Question

Copy SP using T-SQL

dkloehr

SSC Enthusiast

Points: 194
More actions
November 6, 2002 at 10:05 am

#79656

Is there a way to copy a stored procedure from one server to another using T-SQL? I tried writing to the sysobjects table but I get a permission denied error.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 736162 More actions · Answer 1

not really. you need to "compile" the object, you cannot copy it. You could write some T-SQL to get the data from syscomments and then exec() this to compile it. Be messy.

Steve Jones

sjones@sqlservercentral.com

http://www.sqlservercentral.com/columnists/sjones

paulhumphris Hall of Fame Points: 3496 More actions · Answer 2

I presume you are wanting to do this programmatically, since the manual method of scripting the object is straight forward.

Have you considered creating a DTS package to transfer the SQL object(s).

You should then be able to call the DTS package using xp_cmdexec and DTSRun.exe

dkloehr SSC Enthusiast Points: 194 More actions · Answer 3

Didn't think to use the DTS package, although I was hoping find some simple method (programatically). Thanks for your input.