[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


sql 2008 changing stored procedures

Posted on 2012-08-27
Medium Priority
Last Modified: 2012-09-13
Hello we have copied a production databases over to our Crash & Burn environment for further testing, what I want do ois repoint stored procedures against each of the database, the command I was running is a as follows, this was run against all non-system databases

set @searchString = 'ProdServer'
select @text = (replace (@text,@searchString,'CrashBurn Server'))

I have also tried to run this command as well against non-system databases ie master, msdb.

insert into tblObjects(ObjectID, ObjectName,colid )
select C.id, object_name(C.id),c.colid
from syscomments as C
join sysobjects as O on O.id = C.id
      and o.type = 'P'
where C.text like '%'+ 'CrashandBurnServer' +'%' -- Modify this statement to suit your needs.

select * from syscomments
where id = 1045578763

has anyone had these issues before, we need to have this down pat for our DR testing as well.
I would be grateful for some sound advice
Question by:Amanda Walshaw
LVL 143

Accepted Solution

Guy Hengel [angelIII / a3] earned 1500 total points
ID: 38339791
insert into tbl .-...

would not work to "change" stuff, but to insert stuff.

however, for system tables, you cannot do it.
you can only do alter procedures and alter views to modify the source code.
for that, you have to read in the source code of the objects, and then indeed recompile it with the new text.

Author Comment

by:Amanda Walshaw
ID: 38397496
I scripted the databases instead and that worked

Featured Post

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question