Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Deploying Scripts To Multiple Instances


Deploying Scripts To Multiple Instances

Author
Message
Joe Doherty-260822
Joe Doherty-260822
Valued Member
Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)

Group: General Forum Members
Points: 69 Visits: 180
Comments posted here are about the content posted at temp
Ravi Prashanth Lobo-275382
Ravi Prashanth Lobo-275382
Grasshopper
Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)

Group: General Forum Members
Points: 24 Visits: 1

Good article. Got some new ideas.

Will this approach work for complex sql? Suppose I want to create a SP on multiple servers?

How about a batch file and isql?


sgtwilko
sgtwilko
SSC Rookie
SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)

Group: General Forum Members
Points: 32 Visits: 177

Your error handling is screwed.

The moment you run any code after the line the has an error @@error is reset, you can test this fact with the following:

select 1/0 as bob
if @@error<>0
begin
print @@error
end

Your error checking should be as follows:

SET @Error = @@Error
IF @Error <> 0
BEGIN
GOTO spERROR
END
 
 




AlexP-220494
AlexP-220494
SSC-Enthusiastic
SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)

Group: General Forum Members
Points: 149 Visits: 164

For deploying sp's (which are located in a script called C:\mySP.sql) you can use:

SET @mySQL = 'CALL isql -S' + @InstanceName + ' -E -i"C:\mySP.sql" < and the rest of your isql options>'

EXEC sp_executesql @mySQL

Ofcourse this only works if the file is located on your machine hosting SQL Server. If you're working remotely (as most people are) you can put the "CALL isql"-commands in a temp-table, select all rows from this table and put them in a batch-file:

CREATE TABLE #tmp (cmd VARCHAR(4000))

-- Do the following for all your instances
BEGIN
INSERT #tmp (cmd) VALUES ('CALL isql -S' + @InstanceName + ' -E -i"C:\mySP.sql" < and the rest of your isql options>')
END

-- when you're finished
SELECT cmd FROM #tmp

-- Paste the result in a *.bat file and execute it

Grtz, Lex


Ravi Prashanth Lobo-275382
Ravi Prashanth Lobo-275382
Grasshopper
Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)

Group: General Forum Members
Points: 24 Visits: 1

I got the idea. Thanks. I will try this let you guys know.


BitWise MnM
BitWise MnM
SSC Rookie
SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)

Group: General Forum Members
Points: 47 Visits: 88

while it may seem like a nice idea to run a multi instance task right from your Query Analyzer, I wouldn't do it. First of all performance is bound to be terrible, second of all, you can not use some of the query hints (like with (nolocks)). I think that a osql batch file would be just as easy to use, and certainly more versitile, not to mention a VB script (which can even be a DTS job)...


Omri Bahat-228920
Omri Bahat-228920
SSC Rookie
SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)

Group: General Forum Members
Points: 40 Visits: 21
If you use tools like SQL Farm Combine you can run this script, or any other scripts against any number of servers with zero effort or coding (adding/removing remote servers, etc.), and also get aggregated and/or individual results from all databases and servers. I do think that this technique is pretty cool, but not extremely useful.

Editor's Note: : Dr. Omri Bahat works for SQLFarms, a software vendor of SQL Server tools.

clee51
clee51
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 37
Well he did preface the article by saying that people might have other software to do this sort of thing easily. For those situations where DBA's don't have that, this technique is a nice and viable alternative. In that sense, this article is very good in demonstrating the alternative.
BitWise MnM
BitWise MnM
SSC Rookie
SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)

Group: General Forum Members
Points: 47 Visits: 88

This is true, I did not think that this solution was portrayed as "the only one". just that the performance concern is pretty big, and osql option is just as easy so I thought you would have wanted to mention that.


tongbee
tongbee
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 122

Good Post!

How can I pass the linkserver in the OPENQUERY. I try the following query, it doesn't like @LinkSrvName, if I change @LinkSrvName to actual servername (ServerB). It works fine.

SELECT * FROM OPENQUERY (@LinkSrvName,'SELECT @@servername, SERVERPROPERTY(''productversion'') , SERVERPROPERTY (''productlevel'') , SERVERPROPERTY (''edition'')')

Thanks,

CS


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