SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
bkubicek
bkubicek
SSCertifiable
SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)

Group: General Forum Members
Points: 5068 Visits: 866
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
SQLRNNR
SQLRNNR
SSC Guru
SSC Guru (111K reputation)SSC Guru (111K reputation)SSC Guru (111K reputation)SSC Guru (111K reputation)SSC Guru (111K reputation)SSC Guru (111K reputation)SSC Guru (111K reputation)SSC Guru (111K reputation)

Group: General Forum Members
Points: 111666 Visits: 18624
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, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw

bkubicek
bkubicek
SSCertifiable
SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)

Group: General Forum Members
Points: 5068 Visits: 866
Do you live in San Diego? I grew up there. Or perhaps you live in Orlando.

Ben
SQLRNNR
SQLRNNR
SSC Guru
SSC Guru (111K reputation)SSC Guru (111K reputation)SSC Guru (111K reputation)SSC Guru (111K reputation)SSC Guru (111K reputation)SSC Guru (111K reputation)SSC Guru (111K reputation)SSC Guru (111K reputation)

Group: General Forum Members
Points: 111666 Visits: 18624
Nope. LV. Visited San Diego last summer.



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


SQL RNNR

Posting Performance Based Questions - Gail Shaw

Paul Harvey-458236
Paul Harvey-458236
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 30
Dont like the cumbersome approach in this article. We use Linked Servers, although aliases would be better.
bkubicek
bkubicek
SSCertifiable
SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)

Group: General Forum Members
Points: 5068 Visits: 866
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
Paul Harvey-458236
Paul Harvey-458236
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 30
I get you. Nothing against the article per-se. Was quite a good read, actually. Smile
Tom Thomson
Tom Thomson
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39646 Visits: 12890
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

bkubicek
bkubicek
SSCertifiable
SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)

Group: General Forum Members
Points: 5068 Visits: 866
Hey Tom,

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

Ben
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search