Multiple server queries in 2005?

Question

Multiple server queries in 2005?

SQLDCH

SSChampion

Points: 11630
More actions
October 14, 2009 at 9:58 am

#205629

Hey all, I'm wondering if there's a way to run one query against multiple registered servers in SSMS, SQL Server 2005. I realize this can be done in 2008.
Is there a registry key or entry somewhere in SSMS that I can loop thru to get the registered servers?
foreach 'registered server in ssms'
do
select @@SERVERNAME
end
My overall goal is to just see if they are all responding after our nightly reboots. I have SSMS installed on my local machine and want to run the query from there, instead of having to open Query windows for each instance.
TIA.
----------------------------------------------------------------------------
Sacramento SQL Server users group - http://sac.sqlpass.org
Follow me on Twitter - @SQLDCH
----------------------------------------------------------------------------
Yeah, well...The Dude abides.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Piotr.Rodak SSCrazy Eights Points: 9376 More actions · Answer 1

Hi,

Unfortunately there is no such option in SSMS2005. There are third party tools that enable this. I know that SSMS Toolpack [/url] has such feature, although I never used it.

Regards

Piotr

...and your only reply is slàinte mhath

SQLDCH SSChampion Points: 11630 More actions · Answer 2

Definitely not elegant, but works. I used a DOS command line and the 'osql' utility:

FOR /F %s in (serverlist.txt) do osql -S %s -E -h-1 -Q "SELECT @@SERVERNAME"

Obviously, 'serverlist.txt' has a list of server names.

----------------------------------------------------------------------------
Sacramento SQL Server users group - http://sac.sqlpass.org
Follow me on Twitter - @SQLDCH
----------------------------------------------------------------------------

Yeah, well...The Dude abides.

Elliott Berkihiser SSCarpal Tunnel Points: 4068 More actions · Answer 3

Here is a method that utilizes the proc "xp_FixedDrives" as a convenient tool to query every server (and also send valuable info on drive space):

Declare @cmd varchar(500)

Declare @svr varchar(50)

Declare curServers Cursor

FOR

select [Name] from sys.servers

OPEN curServers

Fetch Next from curServers into @svr

WHILE @@Fetch_Status=0

BEGIN

Set @cmd='EXECUTE [' + @svr + '].Master..xp_FixedDrives'

-- Brackets are needed for instance names such as "Server1\SQLExpress"

Select @svr

Exec(@cmd)

Fetch Next from curServers into @svr

END

Close curServers

deallocate curServers

The cursor is necessary because the context must shift to each Master database in turn.

HTH

elliott