SQLServerCentral / Discuss Content Posted by Joey Doherty / Article Discussions / Article Discussions by Author / Deploying Scripts To Multiple Instances / Latest Posts

RE: Deploying Scripts To Multiple Instances

Joe Yanul — Tue, 24 Feb 2009 09:17:52 GMT

Boy that was a "Dah" on my part....somehow I did that the first time and it came up empty...obviously a "user error'!. I found it fine now..thanks alot for your assistance!

RE: Deploying Scripts To Multiple Instances

Rudy Panigas — Tue, 24 Feb 2009 08:52:16 GMT

Ok, just copy "Managing many SQL Servers?" (without the quotes) and paste it into the Search window (above and to the right) and clcik Go and then on the results screen click on articles.Rudy

RE: Deploying Scripts To Multiple Instances

Joe Yanul — Tue, 24 Feb 2009 08:48:20 GMT

Hi again Rudy..I'am new on using this forum...is there an easy way to get to your article?....I've serached and cannot seem to locate it...thanks for your assistance!

RE: Deploying Scripts To Multiple Instances

Rudy Panigas — Tue, 24 Feb 2009 08:35:25 GMT

Hello Joe,Have a look at an article I created here called "Managing many SQL Servers?" and see if this works for you. Code is also provided.Thanks,Rudy

RE: Deploying Scripts To Multiple Instances

Joe Yanul — Tue, 24 Feb 2009 07:40:20 GMT

Hi. This is really a great post and is something I would like to implement.Rudy, is thewre anyway you can send me your updated code that you mentioned..it sounds like enhancements I could use..thanks

RE: Deploying Scripts To Multiple Instances

jmisc — Mon, 15 Dec 2008 12:52:28 GMT

Joe - Good basic script for dynamic Linked Servers...an auxilary post I have put up is remote RECONFIGURE capability as:[linked-server].master.dbo.RECONFIGURE does not work.Any ideas appreciated!thnx Jay

RE: Deploying Scripts To Multiple Instances

dustin.jones — Fri, 10 Oct 2008 14:04:05 GMT

this was a great starting point for me to create my solution. I used the script as a model and added some needed customizations.

RE: Deploying Scripts To Multiple Instances

Rudy Panigas — Thu, 07 Feb 2008 09:05:27 GMT

Hello Joe and everyone else,I would like to thanks you for the article. Read this when it first came out and have since modified it for my need. Here are the modifications I made.- Create table with contains Servers Name, Type (Prod or Development), Version number (2000 /2005) and Owner of the system (Finance, account, etc). This will all me to specify which systems I want to do remote execution on. So if I only want to run code against the development SQL 2000 servers only I can. Also, as new server come to our network I would just add it's info into this table and it done.- Enhanced the error handling for server(s) that already have a link connection- Made my server trusted by all the other servers so that passwords and ids are not needed for connection. My main server is completely locked down on all levels, don't need someone to connect and then have full access to the other servers.- Made a generic version of the code so that I execute anything I need on the remote server. This works for my SSRS report page. I use this code to produce several reports which include: 1) Job failures report on all servers, production only and / or development onhly 2) SQL Error log analysis report I can send you my updated code if you like.Thanks,Rudy

RE: Deploying Scripts To Multiple Instances

tongbee — Fri, 25 May 2007 08:17:00 GMT

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,

RE: Deploying Scripts To Multiple Instances

BitWise MnM — Wed, 27 Sep 2006 12:52:00 GMT

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.

RE: Deploying Scripts To Multiple Instances

clee51 — Wed, 27 Sep 2006 12:43:00 GMT

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.

RE: Deploying Scripts To Multiple Instances

Omri Bahat-228920 — Wed, 27 Sep 2006 07:55:00 GMT

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.

RE: Deploying Scripts To Multiple Instances

BitWise MnM — Wed, 27 Sep 2006 07:40:00 GMT

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)...

RE: Deploying Scripts To Multiple Instances

Ravi Prashanth Lobo-275382 — Wed, 27 Sep 2006 02:44:00 GMT

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

RE: Deploying Scripts To Multiple Instances

AlexP-220494 — Wed, 27 Sep 2006 02:36:00 GMT

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 instancesBEGIN INSERT #tmp (cmd) VALUES ('CALL isql -S' + @InstanceName + ' -E -i"C:\mySP.sql" < and the rest of your isql options>')END

-- when you're finishedSELECT cmd FROM #tmp

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

Grtz, Lex

RE: Deploying Scripts To Multiple Instances

sgtwilko — Wed, 27 Sep 2006 01:41:00 GMT

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 bobif @@error<>0 begin print @@errorend

Your error checking should be as follows:

SET @Error = @@ErrorIF @Error <> 0BEGIN GOTO spERROREND

RE: Deploying Scripts To Multiple Instances

Ravi Prashanth Lobo-275382 — Wed, 27 Sep 2006 01:23:00 GMT

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?

Deploying Scripts To Multiple Instances

Joe Doherty-260822 — Tue, 26 Sep 2006 07:37:00 GMT

Comments posted here are about the content posted at temp