June 9, 2003 at 6:21 am
Greetings all.
Is there a better way to copy a proc other than SP_HelpText into a table, rename it (Search / replace)?
I need to make copies of procs on the fly. This is the only way I can think of.
Proble is, one day I will be using WITH ENCRYPTION. This will render my method usless.
Cheers,
Crispin
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
June 9, 2003 at 7:51 pm
Apart from maybe using OLE automation scripts (which won't be all that much better) your sp_helptext method may be the most straightforward. Even tracing what actions the DTS copy objects wizard undertakes doesn't reveal anything terribly inspirational.
Of course, when you implement WITH ENCRYPTION you will probably be stuck.
(Remember too that WITH ENCRYPTION isn't all that secure... there are tools out there for cracking it)
Cheers,
- Mark
June 9, 2003 at 10:59 pm
Unfortunatly I think I have a problem... 🙁
I am using With Encryption just to hid the proc from the client. No for any security reason.
I did find some script which retrieved the code once encrypted.
In the past the client has dug around in the procs. I ant to prevent this.
Thanks, I think helptext will be the way until I start using encryption.
Cheers,
Crispin
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
June 10, 2003 at 5:22 am
Could use DMO to do it, something like this:
Dim oServer As SQLDMO.SQLServer
Dim oproc As SQLDMO.StoredProcedure
'connect
Set oServer = New SQLDMO.SQLServer
oServer.LoginSecure = True
oServer.Connect "Roosevelt"
'get the proc we want to copy
Set oproc = oServer.Databases("YourDB").StoredProcedures("OriginalProcName")
'get script to create it
procscript = oproc.Script(SQLDMOScript_Default)
'change the names
procscript = Replace$(procscript, oproc.Name, "NewProcName")
'create it
oServer.Databases("Thortest2").ExecuteImmediate procscript
'done
Set oproc = Nothing
oServer.DisConnect
Set oServer = Nothing
Andy
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply