A script that returns VERSIONS of SQL Server…. | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

A script that returns VERSIONS of SQL Server….

I know that @@version should yield whether something is:
SQL Svr Ent Proc
SQL Svr Ent
SQL Svr Standard Proc
SQL Svr Standard
But..how could I integrate it into my "sniffer" script? As I posted yesterday, I’m trying to get a domain-wide idea of what SQL Servers have what attributes, in this case the requirement is now for version.
Here’s the script again, "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


You could make use of function SERVERPROPERTY(EDITION) in this case.

I could, but I’m trying to work out how I can get SERVERPROPERTY(EDITION) to appear in the result set with Servername

Like:
SELECT CONVERT(char(20), SERVERPROPERTY(‘servername’))+ CONVERT(char(20), SERVERPROPERTY(‘edition’))
How about add ‘product level’?
select serverproperty(‘productversion’), serverproperty(‘productlevel’), serverproperty(‘edition’)

Thanks, but I meant how I integrate SERVERPROPERTY into my original script. Could you change a line of it as an example, and I can do the rest?
Thanks,
Jaybee.

Not sure where you want to produce that results, but you can try replacing @:
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

I think we’re at cross-purposes… :) here’s what I want, but I’m not enough of a scripty guy to integrate ServerProperty into the script.
ServerName Version
——————————————————————–
Server 1 Enterprise Edition
Server 2 Personal Edition
Server 3 etc….

It’s ok, I worked it out…took me 15 minutes to work out where the Select should ggo..I’m really not a script-writer!!!!
:)
Jaybee.

Hi Satya,
There seems to be some doubt about my results, all the servers are returned as Enterprise Edition, which seems highly dubious. This is my revised Select statement, could you tell me if it matches my requirement? Maybe it’s only returning the edition of one of the servers? 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
Edit…I ran a ‘Select @@version’ on three random servers and this revealed a Standard build on the second.
Thanks,
Jaybee

See if that function is running on the same server which is an ENTERPRISE edition.

Yeah…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.

A call to SERVERPROPERTY is always resolved by the instance that executes the statement.
You probably need to execute through OPENQUERY to get the remote details..

Sounds likely, but the trouble is I know even less about Openquery than I do ServerProperty, and it’d probably mean a huge rewrite (currently impossible for someone like me who can’t script!)

This has been a fun puzzle …
— Show sql servers in a domain

— *****************************************************************
— ************ Run from a text-ommitted server due to reverse trusts ***
— *****************************************************************
— create table #t ( ServerName varchar(128) )
create table #u ( property varchar(300) )
— Get list of available servers through OSQL utility …
SET @string = ‘OSQL -L’
INSERT INTO #t EXEC master.dbo.xp_cmdshell @string
— Clean up the list so we end up with only server names (including named instances) …
DELETE FROM #t WHERE ServerName IS NULL or RTRIM(ServerName) = ‘Servers:’
UPDATE #T SET ServerName = LTRIM(RTRIM(ServerName))
DECLARE @srvrnm VARCHAR(128)
— Loop through the server names …
DECLARE cu CURSOR FAST_FORWARD
FOR select * FROM #t
OPEN cu
FETCH NEXT FROM cu INTO @srvrnm
WHILE @@FETCH_STATUS = 0
BEGIN SET @string = ‘OSQL -E -S ‘ + @srvrnm +
‘ -Q "SELECT ”’ + @srvrnm + ‘ – ” + CAST(SERVERPROPERTY(”EDITION”) AS VARCHAR(200))"’
INSERT INTO #u EXEC master.dbo.xp_cmdshell @string
FETCH NEXT FROM cu INTO @srvrnm
END
close cu
deallocate cu
delete from #U WHERE Property NOT LIKE ‘%edition%’ OR Property IS NULL
SELECT * FROM #u
drop table #t
drop table #u

Fun for you, perhaps…I find scripting almost EVIL !! :) Something of a black art, you could say…but then I would say that…I was never a Developer nor did I receive any training in TSQL.
Ya left out a "Declare" statement for ‘@string’… not sure what datatype it ought to be.

Ah, silly me – DECLARE @string VARCHAR(1000) should be enough.

Thanks, that worked – or should I say, "Dank U wel"?
Only a couple of minor flaws:
1) The statement concatenates the result set into one column – thus making processing within an Excel spreadsheet problematic;
2) I couldn’t verify the data against our previous results – this returns only 25 or so rows, the previous one returned 139, but I need to ensure I’m running against the same server (our clustered server has some ferocious network/disk issues)!!!
****…it can wait until Monday, I have better ideas for a Friday evening… :)
Cheers!
Jaybee.

Somehow I kept getting an ‘incorrect number of columns’ error for the INSERT INTO, so I concatenated the two to a single column …
Change the hyphen on the actual query statement to a pipeline delimiter, then export the results to a CSV file, and import that into Excel.
‘ -Q "SELECT ”’ + @srvrnm + ‘|” + CAST(SERVERPROPERTY(”EDITION”) AS VARCHAR(200))"’
***
Really no need to say U to me – I’m not that old.[:p]

]]>

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |