Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Deploying Scripts To Multiple Instances Expand / Collapse
Author
Message
Posted Tuesday, September 26, 2006 7:37 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Saturday, September 28, 2013 5:14 AM
Points: 64, Visits: 129
Comments posted here are about the content posted at temp
Post #311197
Posted Wednesday, September 27, 2006 1:23 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, March 20, 2007 12:30 PM
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?

Post #311445
Posted Wednesday, September 27, 2006 1:41 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, April 30, 2014 4:17 AM
Points: 27, Visits: 160

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
 
 



Post #311448
Posted Wednesday, September 27, 2006 2:36 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, September 5, 2014 7:19 AM
Points: 126, Visits: 104

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

Post #311458
Posted Wednesday, September 27, 2006 2:44 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, March 20, 2007 12:30 PM
Points: 24, Visits: 1

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

Post #311459
Posted Wednesday, September 27, 2006 7:40 AM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Sunday, May 4, 2014 5:27 AM
Points: 47, Visits: 80

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

Post #311522
Posted Wednesday, September 27, 2006 7:55 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, April 14, 2008 7:18 AM
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.

Post #311530
Posted Wednesday, September 27, 2006 12:43 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, February 7, 2013 2:55 PM
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.
Post #311659
Posted Wednesday, September 27, 2006 12:52 PM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Sunday, May 4, 2014 5:27 AM
Points: 47, Visits: 80

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.

Post #311660
Posted Friday, May 25, 2007 8:17 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, October 21, 2013 6:54 AM
Points: 2, Visits: 116

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

Post #369032
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse