InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSun, 19 May 2013 15:47:07 GMT20http://www.sqlservercentral.com/Forums/Topic485512-146-1.aspxMorning Clunky or not - does the job.Thanks for your help - I wasted a day trying to get this to workMuch appreciated.Fri, 20 Jun 2008 00:27:52 GMTgeoffrey.sturdyhttp://www.sqlservercentral.com/Forums/Topic485512-146-1.aspxmoderately clunky, but this might do declare @remver sql_variant select @remver=REMVER from OPENQUERY(a_linked_server,'select REMVER=serverproperty(''productversion'')') select @remveralthough many better ways with SMO, ADO.NET etcHTHDickThu, 19 Jun 2008 07:58:41 GMTdbaker-620086http://www.sqlservercentral.com/Forums/Topic485512-146-1.aspxAfternoon 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 workThu, 19 Jun 2008 07:49:47 GMTgeoffrey.sturdyhttp://www.sqlservercentral.com/Forums/Topic485512-146-1.aspxthe SQLCMD utility can discover all the SQLserver instances [that haven't chosen to be hidden]so here are some horrible hacks for you ...[b]HACK 1 (bad!)[/b]at the command prompt (ie start run cmd) do sqlcmd -Lcand 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')"[b]HACK 2 (worse!)[/b]create table #SVRS(instname nvarchar(128))insert into #SVRSexec master..xp_cmdshell 'SQLCMD -Lc'select CMD='SQLCMD -S '+instname+' -E -Q "select serverproperty(''productversion''), serverproperty(''productlevel''), serverproperty(''edition'')"'from #SVRSorder by 1drop table #SVRSand of course you can dream up even nastier cursor-driven examples- but handy to massage into a relational table for subsequent queryingyuck!DickWed, 16 Apr 2008 09:39:59 GMTdbaker-620086http://www.sqlservercentral.com/Forums/Topic485512-146-1.aspxDownload an eval version of SQL 2008. You can actually do this in management studio.Wed, 16 Apr 2008 06:19:59 GMTMichael Earl-395764http://www.sqlservercentral.com/Forums/Topic485512-146-1.aspxI 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 [b]SON1286[/b]:setvar defaultserver [b]SON1286[/b] :connect $(defaultserver)USE [master]GOIF 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 ) 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.Wed, 16 Apr 2008 06:09:25 GMTChandrachurh Ghoshhttp://www.sqlservercentral.com/Forums/Topic485512-146-1.aspxExcellent! 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?Wed, 16 Apr 2008 05:19:44 GMTchapterthirteenhttp://www.sqlservercentral.com/Forums/Topic485512-146-1.aspxUse 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]GOselect 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\sqlWed, 16 Apr 2008 04:17:42 GMTChandrachurh Ghoshhttp://www.sqlservercentral.com/Forums/Topic485512-146-1.aspxHi David,if you had linked servers setup you could run the following command:[code]exec a_linked_server.master.dbo.sp_executesql N'select serverproperty(''productversion''), serverproperty(''productlevel''), serverproperty(''edition'')'[/code]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.Wed, 16 Apr 2008 04:10:52 GMTSQLZhttp://www.sqlservercentral.com/Forums/Topic485512-146-1.aspx[font="Tahoma"]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:[code]select serverproperty('productversion'), serverproperty('productlevel'), serverproperty('edition')[/code]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?[/font]Wed, 16 Apr 2008 03:58:33 GMTchapterthirteen