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


Remote SQL Version Query Problem


Remote SQL Version Query Problem

Author
Message
chapterthirteen
chapterthirteen
SSC Journeyman
SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)

Group: General Forum Members
Points: 87 Visits: 70
I had a recent problem where at no notice I had to query about 100 SQL server instances to get the Version, patch level and edition prior to an audit.

I used the following command:

select serverproperty('productversion'), serverproperty('productlevel'), serverproperty('edition')



I found myself wondering if this command could be used remotely instead of locally? If it can what modifications would have to be made to enable that to happen?

My ultimate goal was to be able to output a single table with SERVER NAME, PRODUCT VERSION, PRODUCT LEVEL, EDITION for all 100 servers by executing a variation of the above query on one server only.

Any thoughts anyone?


AMO AMAS AMATIT AGAIN
SQLZ
SQLZ
SSC Eights!
SSC Eights! (874 reputation)SSC Eights! (874 reputation)SSC Eights! (874 reputation)SSC Eights! (874 reputation)SSC Eights! (874 reputation)SSC Eights! (874 reputation)SSC Eights! (874 reputation)SSC Eights! (874 reputation)

Group: General Forum Members
Points: 874 Visits: 940
Hi David,

if you had linked servers setup you could run the following command:

exec a_linked_server.master.dbo.sp_executesql N'select serverproperty(''productversion''), serverproperty(''productlevel''), serverproperty(''edition'')'



If you don't have permanent linked servers set up you could potentially create the linked servers on the fly if you had a table with the name of all your servers. Just go through that table and use sp_addlinkedserver and sp_addlinkedsrvlogin to create the linked server and associated logins. You'd then use sp_dropserver to remove the linked server.

Karl
source control for SQL Server
Chandrachurh Ghosh
Chandrachurh Ghosh
SSC Veteran
SSC Veteran (203 reputation)SSC Veteran (203 reputation)SSC Veteran (203 reputation)SSC Veteran (203 reputation)SSC Veteran (203 reputation)SSC Veteran (203 reputation)SSC Veteran (203 reputation)SSC Veteran (203 reputation)

Group: General Forum Members
Points: 203 Visits: 331
Use SQLCMD for this....
In MS Management Stusio(SSMS), you will find SQLCMD Mode under the Query menu....

Use the following script -

:setvar defaultserver
:connect $(defaultserver)
USE [master]
GO
select serverproperty('productversion'),
serverproperty('productlevel'),
serverproperty('edition')

Just change the server name and execute.....hope this helps!

Note: The windows login needs to have administrative privileges on all the servers......also if you have multiple instances use ....for example SON1843\sql

Chandrachurh Ghosh
DBA – MS SQL Server
Ericsson India Global Services Limited
Quality is not an act, it is a habit.
chapterthirteen
chapterthirteen
SSC Journeyman
SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)

Group: General Forum Members
Points: 87 Visits: 70
Excellent! Thanks for the prompt (and useful) replies. I went with option 2 and used the SQLCMD which has worked a treat.

Is it possible to loop SQLCMD so that the defaultserver variable could be updated each time from a table of server names?

AMO AMAS AMATIT AGAIN
Chandrachurh Ghosh
Chandrachurh Ghosh
SSC Veteran
SSC Veteran (203 reputation)SSC Veteran (203 reputation)SSC Veteran (203 reputation)SSC Veteran (203 reputation)SSC Veteran (203 reputation)SSC Veteran (203 reputation)SSC Veteran (203 reputation)SSC Veteran (203 reputation)

Group: General Forum Members
Points: 203 Visits: 331
I am devising a way to help you out in looping, as I know the tidious job in doing recursive work for a volume as big as 100 servers.....
As of now, the following process will let you save your outputs to a centrally located excel file. This will do away with the copy and paste stuff each time you run the script for a server.

First of all share a folder and create a blank excel workbook with a worksheet named Result. Name the columns SERVER, PRODUCTVERSION, PRODUCTLEVEL, EDITION in the worksheet.

That's all....
as before run the following script for each of the servers you want to drill in...changing just the servername SON1286
:setvar defaultserver SON1286
:connect $(defaultserver)
USE [master]
GO

IF NOT EXISTS (SELECT srv.name
            FROM sys.servers srv
            WHERE srv.server_id != 0
            AND srv.name = N'ExcelSource')
EXEC sp_addlinkedserver 'ExcelSource',
'Jet 4.0',
'Microsoft.Jet.OLEDB.4.0',
'\\son1286\Share\Test.xls',
NULL,
'Excel 5.0'

DECLARE @TSQLSTMT      VARCHAR(4000),
      @SERVER         VARCHAR(100),
      @PRODUCTVERSION   VARCHAR(150),
      @PRODUCTLEVEL   VARCHAR(150),
      @EDITION      VARCHAR(150)
SELECT @SERVER=CONVERT(VARCHAR,@@SERVERNAME),
      @PRODUCTVERSION=CONVERT(VARCHAR,SERVERPROPERTY('PRODUCTVERSION')),
      @PRODUCTLEVEL=CONVERT(VARCHAR,SERVERPROPERTY('PRODUCTLEVEL')),
      @EDITION=CONVERT(VARCHAR,SERVERPROPERTY('EDITION'))
SET @TSQLSTMT = 'Insert ExcelSource...[Result$]
                     (SERVER,
                        PRODUCTVERSION,
                        PRODUCTLEVEL,
                        EDITION
                        Wink
            SELECT '''+@SERVER+''',
                  '''+@PRODUCTVERSION+''',
                  '''+@PRODUCTLEVEL+''',
                  '''+@EDITION+''''
EXEC(@TSQLSTMT)

Note: You must not have any linked server already on your servers named ExcelSource. You can choose a unique name if required.

Chandrachurh Ghosh
DBA – MS SQL Server
Ericsson India Global Services Limited
Quality is not an act, it is a habit.
Michael Earl-395764
Michael Earl-395764
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2701 Visits: 23078
Download an eval version of SQL 2008. You can actually do this in management studio.
dbaker-620086
dbaker-620086
SSC-Enthusiastic
SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)

Group: General Forum Members
Points: 114 Visits: 196
the SQLCMD utility can discover all the SQLserver instances [that haven't chosen to be hidden]

so here are some horrible hacks for you ...

HACK 1 (bad!)
at the command prompt (ie start run cmd) do
sqlcmd -Lc
and then use Notepad to edit each line to become (2 wrapped lines shown here)
SQLCMD -S svr1-E -Q "select serverproperty('productversion'), serverproperty('productlevel'), serverproperty('edition')"
SQLCMD -S svr2\SQL2005 -E -Q "select serverproperty('productversion'), serverproperty('productlevel'), serverproperty('edition')"


HACK 2 (worse!)
create table #SVRS(instname nvarchar(128))
insert into #SVRS
exec master..xp_cmdshell 'SQLCMD -Lc'
select CMD='SQLCMD -S '+instname+' -E -Q "select serverproperty(''productversion''), serverproperty(''productlevel''), serverproperty(''edition'')"'
from #SVRS
order by 1

drop table #SVRS

and of course you can dream up even nastier cursor-driven examples
- but handy to massage into a relational table for subsequent querying

yuck!
Dick
geoffrey.sturdy
geoffrey.sturdy
Mr or Mrs. 500
Mr or Mrs. 500 (520 reputation)Mr or Mrs. 500 (520 reputation)Mr or Mrs. 500 (520 reputation)Mr or Mrs. 500 (520 reputation)Mr or Mrs. 500 (520 reputation)Mr or Mrs. 500 (520 reputation)Mr or Mrs. 500 (520 reputation)Mr or Mrs. 500 (520 reputation)

Group: General Forum Members
Points: 520 Visits: 1677
Afternoon
Is there any way of returning the query
exec a_linked_server.master.dbo.sp_executesql N'select serverproperty(''productversion'')
to a local variable ?
we do not have MSDTC enabled on the target servers so an 'insert into' on a temporary table won't work
dbaker-620086
dbaker-620086
SSC-Enthusiastic
SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)

Group: General Forum Members
Points: 114 Visits: 196
moderately clunky, but this might do
   declare @remver sql_variant
   select @remver=REMVER from OPENQUERY(a_linked_server,'select REMVER=serverproperty(''productversion'')')
   select @remver

although many better ways with SMO, ADO.NET etc

HTH
Dick
geoffrey.sturdy
geoffrey.sturdy
Mr or Mrs. 500
Mr or Mrs. 500 (520 reputation)Mr or Mrs. 500 (520 reputation)Mr or Mrs. 500 (520 reputation)Mr or Mrs. 500 (520 reputation)Mr or Mrs. 500 (520 reputation)Mr or Mrs. 500 (520 reputation)Mr or Mrs. 500 (520 reputation)Mr or Mrs. 500 (520 reputation)

Group: General Forum Members
Points: 520 Visits: 1677
Morning
Clunky or not - does the job.
Thanks for your help - I wasted a day trying to get this to work
Much appreciated.
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