A Search and Replace Script when Moving Objects from Prod to Test

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Ben Kubicek » A Search and Replace Script when Moving...

19 posts, Page 2 of 2

««1 2

A Search and Replace Script when Moving Objects from Prod to Test

Rate Topic

Display Mode

Topic Options

Author

Message

bkubicek

Posted Wednesday, February 03, 2010 1:04 PM

UDP Broadcaster

Group: General Forum Members
Last Login: Friday, February 01, 2013 8:13 AM
Points: 1,489, Visits: 388

I have yet to see one of their shows "live." I like very much what they do/achieve. I thought it was fitting with location and job.

Jason,

I had the opportunity to see them twice. I have heard the show in Las Vegas is really crazy since they have water all over the place and you never know when they are diving into the water or landing on the stage.
You are right sometimes work can be a circus.

Ben

Post #858978

SQLRNNR

Posted Wednesday, February 03, 2010 1:12 PM

SSCoach

Group: General Forum Members
Last Login: 2 days ago @ 1:07 PM
Points: 18,733, Visits: 12,332

I guess I was wrong about seeing their show live. I just remembered that they have a show at SeaWorld too.
I will have to get down to the strip and catch one of their shows sometime.

Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server 2008

SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp

Post #858987

bkubicek

Posted Wednesday, February 03, 2010 1:20 PM

UDP Broadcaster

Group: General Forum Members
Last Login: Friday, February 01, 2013 8:13 AM
Points: 1,489, Visits: 388

Do you live in San Diego? I grew up there. Or perhaps you live in Orlando.

Ben

Post #858996

SQLRNNR

Posted Wednesday, February 03, 2010 3:03 PM

SSCoach

Group: General Forum Members
Last Login: 2 days ago @ 1:07 PM
Points: 18,733, Visits: 12,332

Nope. LV. Visited San Diego last summer.

Post #859097

Paul Harvey-458236

Posted Wednesday, February 03, 2010 4:57 PM

Forum Newbie

Group: General Forum Members
Last Login: Sunday, April 17, 2011 8:56 PM
Points: 5, Visits: 30

Dont like the cumbersome approach in this article. We use Linked Servers, although aliases would be better.

Post #859196

bkubicek

Posted Wednesday, February 03, 2010 7:42 PM

UDP Broadcaster

Group: General Forum Members
Last Login: Friday, February 01, 2013 8:13 AM
Points: 1,489, Visits: 388

Well Paul, sorry you didn't like the article. Sometimes you just find yourself in a situation and you have to make the best of it. This was my solution of making the best of it.

Ben

Post #859245

Paul Harvey-458236

Posted Wednesday, February 03, 2010 8:27 PM

Forum Newbie

Group: General Forum Members
Last Login: Sunday, April 17, 2011 8:56 PM
Points: 5, Visits: 30

I get you. Nothing against the article per-se. Was quite a good read, actually. :)

Post #859264

L' Eomot Inversé

Posted Tuesday, February 16, 2010 12:20 PM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 3:07 PM
Points: 7,096, Visits: 7,156

I see that the SP uses varchar(max) so the base must be at least SQLS2005, so probably you should not have considered using syscomments, which exists in 2005 and 2008 only for backwards compatibility and has been replaced by the sys.sql_modules system view.

Even if you use syscomments to get the text you can get all of it, not only 4000 characters as you suggested, because you can loop through the chunks (colid is the chunk sequence number for a multi-chunk object in syscomments) - but it would be a bit pointless given that sys.sql_modules is available.

Rather than changing create proc to alter proc you could prepend two lines to drop the proc and end a batch (create proc has to be the first SQL statement - apart from comments - in a batch) - this is less problematic than restricting the replace to the first 4 lines because SPs often have have the create proc statement after more than 4 lines of comment; and although matching "create proc" won't alter any non-proc create statements the first match you find may be in a comment so looping through lines until you get a match and then stopping is a risky too. Then there is no need for any sort of loop: neither to do the wanted changes, nor for changing "create" to "alter" - the whole thing becomes much simpler with no temp table and no loop controls, and this will make in perform a bit better. But given that this is probably something which isn't done all that often and isn't performance critical and your version using sp_helptext already works in your system there might not be much point in changing it.

Tom
Que conclure à la fin de tous mes longs propos? C'est que les préjugés sont la raison des sots. (Voltaire, 1756)

Post #866315

bkubicek

Posted Wednesday, February 17, 2010 5:03 AM

UDP Broadcaster

Group: General Forum Members
Last Login: Friday, February 01, 2013 8:13 AM
Points: 1,489, Visits: 388

Hey Tom,

You make a lot of good points. Thanks for you comments.

Ben

Post #866897

« Prev Topic | Next Topic »

19 posts, Page 2 of 2

««1 2

Permissions