SQL Server Performance

A script that returns VERSIONS of SQL Server....

Discussion in 'General DBA Questions' started by jaybee, Sep 6, 2007.

  1. jaybee New Member

    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
    --
  2. satya Moderator

    You could make use of function SERVERPROPERTY(EDITION) in this case.
  3. jaybee New Member

    I could, but I'm trying to work out how I can get SERVERPROPERTY(EDITION) to appear in the result set with Servername
  4. satya Moderator

    Like:
    SELECT CONVERT(char(20), SERVERPROPERTY('servername'))+ CONVERT(char(20), SERVERPROPERTY('edition'))

  5. solartouch New Member

    How about add 'product level'?
    select serverproperty('productversion'), serverproperty('productlevel'), serverproperty('edition')
  6. jaybee New Member

    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.
  7. satya Moderator

    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
  8. jaybee New Member

    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....
  9. jaybee New Member

    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.
  10. jaybee New Member

    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
  11. satya Moderator

    See if that function is running on the same server which is an ENTERPRISE edition.
  12. jaybee New Member

    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.
  13. Adriaan New Member

    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..
  14. jaybee New Member

    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!)
  15. Adriaan New Member

    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
  16. jaybee New Member

    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.
  17. Adriaan New Member

    Ah, silly me - DECLARE @string VARCHAR(1000) should be enough.
  18. jaybee New Member

    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.
  19. Adriaan New Member

    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]

Share This Page