copy new stored procedure to overwrite old

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

SQL Server 2000 Forums

SQL Server Development (2000)

copy new stored procedure to overwrite old

Author

Topic

broniusm
Starting Member

7 Posts

Posted - 2005-01-26 : 17:42:05

What's the best, scripted way to simply copy a stored procedure by name from one db to another? If I will need to, how can I "read" the script of a stored procedure for re-creation remotely?

I feel I'm hot on the trail with lots of bits and pieces here and there, but can't seem to get a good, definitive solution. Please help..?

thanks, bronius

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2005-01-26 : 18:07:12

In enterprise manager, right click on an object and use the "Generate SQL Script" option to build a script for one or many objects. You can run this on the destination server.

Damian

broniusm
Starting Member

7 Posts

Posted - 2005-01-26 : 18:11:38

Thanks, and I've seen that, and it works as a manual, hands-on, iterative solution.

I'm looking to build, however, a self-standing script into which I can pass the name of a stored procedure. I'll be using this as a single component of a larger package of tasks all based on the same name of the stored procedure to be copied.

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-01-26 : 18:13:53

See "scripting databases" in BOL.
Basically you create a script from EM or you just save/generate the script in QA.
Run this saved/copied script in your database of choice.

I assume You can connect to the remote database and run/generate scripts in that databaase.

rockmoose

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2005-01-26 : 18:17:25

Rockmoose, that's still a manual step.

broniusm, what you need to do is learn some SQL-DMO programming. There is an article or two about it on this site. With DMO you could whip up that app pretty quickly in vb.

Damian

broniusm
Starting Member

7 Posts

Posted - 2005-01-26 : 18:20:04

> SQL-DMO

arrgh. Right where I was headed when I became quickly overwhelmed. Will do-- I appreciate the direction.

(If anyone stumbles across a ready-made solution, please by all means post here. I'll do the same.)

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2005-01-26 : 18:27:19

I understand your pain, DMO takes a little bit of experimentation to "get".
Do you have a copy of vb6 ? It's the easiest environment for working with DMO (just because of the intellisense, I find it's not as intuitive if you call it from a .NET language).

Basically, what you want to do....

Connect to the database using the DMO server object.
Find the proc by name and use DMO to generate the script.
Use an ADO connection to run the script on the destination, because the script will containt "GO" statements, you have to parse the script into an array of batches and run each one individually. GO isn't SQL, it's a batch seperator.

As a side note, from my point of view you're going about this the wrong way. A better practice to manage your code is to work with all your procs in script form, saving them on the filesystem and putting them into source control.

Then when it's time to deploy them, you run the lot from script. This can be automated really well.

Damian

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-01-26 : 18:34:53

>> Rockmoose, that's still a manual step.
Yeah, I was tyoing sooo slowly You know.

[url]http://www.sqlteam.com/searchresults.asp?SearchTerms=sqldmo[/url]

rockmoose

broniusm
Starting Member

7 Posts

Posted - 2005-01-28 : 17:33:30

Thanks rockmoose. Did you notice the author of the intro article? :)

Damian-
You mentioned having to parse out the statements into separate batches when using ADO. Is there a disadvantage to using another DMO to connect to remote and executing the parsed script as-is? That *seems* to work for me...

-bronius

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2005-01-28 : 18:29:08

Ahh, does DMO figure it out ? I hadn't looked at that. If it's working for you, great!

Damian

broniusm
Starting Member

7 Posts

Posted - 2005-01-28 : 18:36:40

Sweet. Thanks for the confidence :)

Here's my adapted ASP solution. Please excuse the lack of error checking.


	' Connect to Local SQLServer
	Response.Write "Connecting to local server..."
	Dim objDMO 
	Set objDMO = CreateObject("SQLDMO.SQLServer")
	objDMO.Connect "servername", "user", "pass"
	Response.Write "Connected.<br/>"

	' Connect to Remote SQLServer
	Response.Write "Connecting to remote server..."
	Dim objsvrDMO
	Set objsvrDMO = CreateObject("SQLDMO.SQLServer")
	objsvrDMO.Connect "servername", "user", "pass"
	Response.Write "Connected.<br/>"

	' Establish local database of operation
	Dim objDB
	Set objDB = objDMO.Databases("dbname")

	' Establish remote database of operation
	Dim objsvrDB
	Set objsvrDB = objsvrDMO.Databases("dbname")

	Response.Write "Generating source script..."
	' Generate SQL to create sp
	Dim spScript
	spScript = objDB.StoredProcedures(SPToCopy).Script(1 Or 4 Or 2 Or 262144) ' If exists/drop, Script, Grant permission same as source
	Response.Write "Done.<br/>"

	Response.Write "Executing script on remote server..."
	' Execute the generated script on remote database
	objsvrDB.ExecuteImmediate spScript
	Response.Write "Done.<br/>"

	' Clean up
	objDMO.Disconnect
	Set objDMO = Nothing
	objsvrDMO.Disconnect
	Set objsvrDMO = Nothing

Of course, ServerName, DBName, SPToCopy, User, and Pass are all unique to the developer. Really straightforward to copy a stored proc from one db to another on a remote server. Thanks guys :D

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2005-01-28 : 20:08:59

You also might want to check out Nigel's stuff on DMO.

http://www.nigelrivett.net/index.html

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources