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?

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

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

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

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

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

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.

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