Replace a string across multiple stored procedures

29 Jul 2005 3:08 PM

26point2er

actually 2 questions.

a. Need to replace the string "server name" available on multiple stored
procedures p1,p2....p19
with ""?
can I do this with an update to system tables like syscomments?

b. Can I create a linked server to itself?

29 Jul 2005 3:17 PM

Aaron Bertrand [SQL Server MVP]

> can I do this with an update to system tables like syscomments?

NO! Updates to system tables are not supported and are strongly
discouraged.

Script out the stored procedures as ALTER or DROP/CREATE (you can do this
using Enterprise Manager, Query Analyzer, or a host of other tools). Do the
replace operation in the output file, then apply that script to the server.

Have you heard of source control?

29 Jul 2005 3:26 PM

Louis Davidson

Not to mention that changing the text in syscomments wouldn't actually
change the procedure, right?

--
----------------------------------------------------------------------------
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP

Show quote

"Aaron Bertrand [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in message
news:OTgoNCFlFHA.3568@TK2MSFTNGP10.phx.gbl...
>> can I do this with an update to system tables like syscomments?
>
> NO! Updates to system tables are not supported and are strongly
> discouraged.
>
> Script out the stored procedures as ALTER or DROP/CREATE (you can do this
> using Enterprise Manager, Query Analyzer, or a host of other tools). Do
> the replace operation in the output file, then apply that script to the
> server.
>
> Have you heard of source control?
>

29 Jul 2005 3:32 PM

Aaron Bertrand [SQL Server MVP]

> Not to mention that changing the text in syscomments wouldn't actually
> change the procedure, right?

That's correct, but I was nipping any other potential "what if I update
this, or what if I update that..." before they come up. :-)

29 Jul 2005 5:35 PM

Louis Davidson

Yeah, no doubt :)

--
----------------------------------------------------------------------------
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP

Show quote

"Aaron Bertrand [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in message
news:eCverKFlFHA.2852@TK2MSFTNGP15.phx.gbl...
>> Not to mention that changing the text in syscomments wouldn't actually
>> change the procedure, right?
>
> That's correct, but I was nipping any other potential "what if I update
> this, or what if I update that..." before they come up. :-)
>