Changing a script to show SERVERPROPERTY
-
Hi all, I've a script that yields servernames across a domain, but I need help to rewrite the script (having almost no dev skills at all!!), so that it extracts the version of SQL Server using Serverproperty.
Here's the script, "text-ommitted" refers to internal server names or domains, and as you see, heavy use has been made of OSQL and CMDSHELL. Forgive the repetitive parts!!
-- Show sql servers in a domain
--
-- *****************************************************************
-- ************ Run from a text-ommitted server due to reverse trusts ***
-- *****************************************************************
--
SET NOCOUNT ON
DECLARE @string varchar(128)
IF EXISTS(SELECT * FROM TEMPDB..SYSOBJECTS WHERE NAME LIKE '#t_%') DROP TABLE #t
create table #t ( ServerName varchar(128) )
DECLARE @Match varchar(128)
DECLARE @NotMatch1 varchar(128)
DECLARE @NotMatch2 varchar(128)
SET @Match = ' %'
SET @Notmatch1 = '%(local)%'
-- SET @Notmatch2 = '%.......%'
SET @Notmatch2 = '%UKD%-%'
SET @string = 'OSQL -E -S text-ommitted -Q ' + '"' + 'EXEC MASTER..XP_CMDSHELL ' + '''' + 'OSQL -L' + '''' + '"' ; INSERT INTO #t EXEC xp_cmdshell @string
SET @string = 'OSQL -E -S text-ommitted -Q ' + '"' + 'EXEC MASTER..XP_CMDSHELL ' + '''' + 'OSQL -L' + '''' + '"' ; INSERT INTO #t EXEC xp_cmdshell @string
SET @string = 'OSQL -E -S text-ommitted -Q ' + '"' + 'EXEC MASTER..XP_CMDSHELL ' + '''' + 'OSQL -L' + '''' + '"' ; INSERT INTO #t EXEC xp_cmdshell @string
SET @string = 'OSQL -E -S text-ommitted -Q ' + '"' + 'EXEC MASTER..XP_CMDSHELL ' + '''' + 'OSQL -L' + '''' + '"' ; INSERT INTO #t EXEC xp_cmdshell @string
SET @string = 'OSQL -E -S text-ommitted -Q ' + '"' + 'EXEC MASTER..XP_CMDSHELL ' + '''' + 'OSQL -L' + '''' + '"' ; INSERT INTO #t EXEC xp_cmdshell @string
SET @string = 'OSQL -E -S text-ommitted -Q ' + '"' + 'EXEC MASTER..XP_CMDSHELL ' + '''' + 'OSQL -L' + '''' + '"' ; INSERT INTO #t EXEC xp_cmdshell @string
/*
SET @string = 'OSQL -E -S text-ommitted -Q ' + '"' + 'EXEC MASTER..XP_CMDSHELL ' + '''' + 'OSQL -L' + '''' + '"' ;
INSERT INTO #t EXEC xp_cmdshell @string
SET @string = 'OSQL -E -S text-ommitted -Q ' + '"' + 'EXEC MASTER..XP_CMDSHELL ' + '''' + 'OSQL -L' + '''' + '"' ;
INSERT INTO #t EXEC xp_cmdshell @string
SET @string = 'OSQL -E -S text-ommitted -Q ' + '"' + 'EXEC MASTER..XP_CMDSHELL ' + '''' + 'OSQL -L' + '''' + '"' ;
INSERT INTO #t EXEC xp_cmdshell @string
SET @string = 'OSQL -E -S text-ommitted -Q ' + '"' + 'EXEC MASTER..XP_CMDSHELL ' + '''' + 'OSQL -L' + '''' + '"' ;
INSERT INTO #t EXEC xp_cmdshell @string
SET @string = 'OSQL -E -S text-ommitted -Q ' + '"' + 'EXEC MASTER..XP_CMDSHELL ' + '''' + 'OSQL -L' + '''' + '"' ;
INSERT INTO #t EXEC xp_cmdshell @string
SET @string = 'OSQL -E -S text-ommitted -Q ' + '"' + 'EXEC MASTER..XP_CMDSHELL ' + '''' + 'OSQL -L' + '''' + '"' ;
INSERT INTO #t EXEC xp_cmdshell @string
SET @string = 'OSQL -E -S text-ommitted -Q ' + '"' + 'EXEC MASTER..XP_CMDSHELL ' + '''' + 'OSQL -L' + '''' + '"' ;
INSERT INTO #t EXEC xp_cmdshell @string
SET @string = 'OSQL -E -S text-ommitted -Q ' + '"' + 'EXEC MASTER..XP_CMDSHELL ' + '''' + 'OSQL -L' + '''' + '"' ;
INSERT INTO #t EXEC xp_cmdshell @string
SET @string = 'OSQL -E -S text-ommitted -Q ' + '"' + 'EXEC MASTER..XP_CMDSHELL ' + '''' + 'OSQL -L' + '''' + '"' ;
INSERT INTO #t EXEC xp_cmdshell @string
SET @string = 'OSQL -E -S text-ommitted -Q ' + '"' + 'EXEC MASTER..XP_CMDSHELL ' + '''' + 'OSQL -L' + '''' + '"' ;
INSERT INTO #t EXEC xp_cmdshell @string
SET @string = 'OSQL -E -S text-ommitted -Q ' + '"' + 'EXEC MASTER..XP_CMDSHELL ' + '''' + 'OSQL -L' + '''' + '"' ;
INSERT INTO #t EXEC xp_cmdshell @string
SET @string = 'OSQL -E -S text-ommitted -Q ' + '"' + 'EXEC MASTER..XP_CMDSHELL ' + '''' + 'OSQL -L' + '''' + '"' ;
INSERT INTO #t EXEC xp_cmdshell @string
SET @string = 'OSQL -E -S text-ommitted -Q ' + '"' + 'EXEC MASTER..XP_CMDSHELL ' + '''' + 'OSQL -L' + '''' + '"' ;
INSERT INTO #t EXEC xp_cmdshell @string
SET @string = 'OSQL -E -S text-ommitted -Q ' + '"' + 'EXEC MASTER..XP_CMDSHELL ' + '''' + 'OSQL -L' + '''' + '"' ;
INSERT INTO #t EXEC xp_cmdshell @string
SET @string = 'OSQL -E -S text-ommitted -Q ' + '"' + 'EXEC MASTER..XP_CMDSHELL ' + '''' + 'OSQL -L' + '''' + '"' ;
INSERT INTO #t EXEC xp_cmdshell @string
SET @string = 'OSQL -E -S text-ommitted -Q ' + '"' + 'EXEC MASTER..XP_CMDSHELL ' + '''' + 'OSQL -L' + '''' + '"' ;
INSERT INTO #t EXEC xp_cmdshell @string
SET @string = 'OSQL -E -S text-ommitted -Q ' + '"' + 'EXEC MASTER..XP_CMDSHELL ' + '''' + 'OSQL -L' + '''' + '"' ;
INSERT INTO #t EXEC xp_cmdshell @string
SET @string = 'OSQL -E -S text-ommitted -Q ' + '"' + 'EXEC MASTER..XP_CMDSHELL ' + '''' + 'OSQL -L' + '''' + '"' ;
INSERT INTO #t EXEC xp_cmdshell @string
SET @string = 'OSQL -E -S text-ommitted -Q ' + '"' + 'EXEC MASTER..XP_CMDSHELL ' + '''' + 'OSQL -L' + '''' + '"' ;
INSERT INTO #t EXEC xp_cmdshell @string
SET @string = 'OSQL -E -S text-ommitted -Q ' + '"' + 'EXEC MASTER..XP_CMDSHELL ' + '''' + 'OSQL -L' + '''' + '"' ;
INSERT INTO #t EXEC xp_cmdshell @string
SET @string = 'OSQL -E -S text-ommitted -Q ' + '"' + 'EXEC MASTER..XP_CMDSHELL ' + '''' + 'OSQL -L' + '''' + '"' ;
INSERT INTO #t EXEC xp_cmdshell @string
SET @string = 'OSQL -E -S text-ommitted -Q ' + '"' + 'EXEC MASTER..XP_CMDSHELL ' + '''' + 'OSQL -L' + '''' + '"' ;
INSERT INTO #t EXEC xp_cmdshell @string
SET @string = 'OSQL -E -S text-ommitted -Q ' + '"' + 'EXEC MASTER..XP_CMDSHELL ' + '''' + 'OSQL -L' + '''' + '"' ;
INSERT INTO #t EXEC xp_cmdshell @string
SET @string = 'OSQL -E -S text-ommitted -Q ' + '"' + 'EXEC MASTER..XP_CMDSHELL ' + '''' + 'OSQL -L' + '''' + '"' ;
INSERT INTO #t EXEC xp_cmdshell @string
SET @string = 'OSQL -E -S text-ommitted -Q ' + '"' + 'EXEC MASTER..XP_CMDSHELL ' + '''' + 'OSQL -L' + '''' + '"' ;
INSERT INTO #t EXEC xp_cmdshell @string
*/
SET NOCOUNT OFF
select RTRIM(LTRIM(ServerName)) ServerName from #t where ServerName like @Match and
ServerName not like @NotMatch1 and
ServerName not like @NotMatch2 group by ServerName order by ServerName
--select 'INSERT INTO #U VALUES ( ''' + RTRIM(LTRIM(ServerName)) + ''')' ServerName from #t where ServerName like @Match and
-- ServerName not like @NotMatch1 and
-- ServerName not like @NotMatch2 group by ServerName order by ServerName
drop table #t
--
-
SELECT
SERVERPROPERTY('ProductVersion') AS ProductVersion,
SERVERPROPERTY('ProductLevel') AS ProductLevel,
SERVERPROPERTY('Edition') AS Edition
On my local server that returns
ProductVersion ProductLevel Edition
9.00.1399.06 RTM Developer Edition
Is that what you're looking for?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Getting there, what I need is:
ServerName Version
--------------------------------------------------------------------
Server 1 Enterprise Edition
Server 2 Personal Edition
Server 3 etc....
-
On each server run
SELECT @@SERVERNAME AS ServerName, SERVERPROPERTY('Edition') AS Version
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Done...thank you Gail!!
Took me about 15 mins to work out where the Select should go...I really am not into scripting!!
Jaybee.
-
Hiccup in the script...
select servername, serverproperty('productversion'), serverproperty('productlevel'), serverproperty('edition')
from #t where ServerName like @Match and
ServerName not like @NotMatch1 and
ServerName not like @NotMatch2 group by ServerName order by ServerName
--select 'INSERT INTO #U VALUES servername, serverproperty('productversion'), serverproperty('productlevel'), serverproperty('edition') from #t where ServerName like @Match and
-- ServerName not like @NotMatch1 and
-- ServerName not like @NotMatch2 group by ServerName order by ServerName
I ran a 'Select @@version' on three random servers and this revealed a Standard build on the second.
Basically, when I run it FROM a Standard server, it returns all machines in the domain as "Standard" - same as when I run on an Enterprise server, all machines returned as Enterprise. All in same domain.
-
select servername, serverproperty('productversion'), serverproperty('productlevel'), serverproperty('edition')
from #t where ServerName...
That's going to return for the machine where the query is run. You're not actually reading anything from #t there, other than the servername. Serverproperty refers to the server that the machine is connected to.
You need to get that script run on each of the other machines and the results inserted into your temp table #t, then just se4lect * from that.
I haven't deciphered your script completely, but from what I' saw, you're using xmp_cmdshell to run osql, point it at each of your servers and run a command on that, inserting the results into #t. The serverproperty(..) needs to go inside that
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Thanks, I'd appreciate any help on this, I'm completely null at scripting! Toyed with the idea of 'Openquery' but I know even less about that, and it'd probably mean a big rewrite.
-
I'll have a go at writing something from scratch for you, cause I can't figure out the original script.
Is going to take a while though, please be patient.
Openquery requires that you have all the servers you want to check set up as linked servers. Is that the case? If not, is OpenRowset allowed? (many places forbit its use as it can be abused)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
I suspect Openrowset is allowed, as cmdshell is. Don't know how to test it though.
Your help is very welcome Gail, I look forward to trying your rewrite.
Cheers,
Jaybee.
-
Jay, I do apologise. This completely slipped my mind on the run up to PASS. I hope this makes up for the delay.
I don't normally recommend cursors, but this is one area where they work and are useful.
--********************************************************--
-- You can add more to this table if necessary.
-- Keep this in your admin database, if you have one.
-- I made this a permanent table so that you can keep this for as long as needed.
-- If you don't need that, make it a temp table with just the server name column, and leave out the final update
Create Table Servers (
ServerName varchar(200) not null primary key,
ProductVersion varchar(20),
ProductLevel varchar(10),
Edition varchar(20)
)
go
-- populate that with all the servers you want to check, just the names.
INSERT INTO Servers (ServerName) Values ('Excalibur') -- replace with one of your servers
-- .... Add rest of servers here ...
GO
DECLARE @ServerName VARCHAR(200), @sSQL varchar(4000)
DECLARE @ResultTable TABLE (
ServerName varchar(200),
ProductVersion varchar(20),
ProductLevel varchar(10),
Edition varchar(40)
)
DECLARE curServers CURSOR LOCAL FAST_FORWARD FOR
Select ServerName FROM Servers
OPEN curServers
FETCH NEXT FROM curServers into @ServerName
WHILE @@FETCH_STATUS = 0
BEGIN
set @sSQL = 'select cast(servername as varchar(200)), cast(version as varchar(20)), cast(productlevel as varchar(10)), cast(edition as varchar(40))
from OPENROWSET(''SQLOLEDB.1'', ''Server=' + @ServerName + ';Trusted_Connection=yes;'',''select @@servername AS ServerName, serverproperty(''''productversion'''') AS Version, serverproperty(''''productlevel'''') AS productLevel, serverproperty(''''edition'''') AS Edition'')'
--print @sSQL
insert into @ResultTable
execute (@sSQL)
FETCH NEXT FROM curServers into @ServerName
END
CLOSE curServers
DEALLOCATE curServers
Update Servers
set ProductVersion = rs.ProductVersion,
ProductLevel = rs.ProductLevel,
Edition = rs.Edition
from @ResultTable rs where servers.Servername = rs.ServerName
select * from Servers
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply