Post reply

T-SQL servers

  • May 18, 2008 at 10:58 am

    #72044

    Please, how from SQL find all local servers (like in DOS wit SQLCMD -L)?

  • May 19, 2008 at 8:51 am

    #817224

    Hi,

    @@SERVERNAME will give you the local server name

    Rajesh

  • May 19, 2008 at 11:29 am

    #817341

    This is horrible.

    SET NoCount on

    declare @cmd VARCHAR(8000)

    SET @Cmd = 'EXEC master..xp_cmdshell '''

    SET @Cmd = @Cmd + 'SQLCMD /L'''

    --or for 2000 servers

    -- SET @Cmd = @Cmd + 'oSQL /L'''

    EXEC (@Cmd)

    told you.

    Dave J

    http://glossopian.co.uk/
    "I don't know what I don't know."

  • May 19, 2008 at 1:17 pm

    #817415

    Thanks all

    Josip

  • May 21, 2008 at 10:48 am

    #818734

    This will show all the running SQL Server services.

    Note: you will need to make sure srvinfo is available.

    Have Fun!

    CREATE TABLE #srvinfo (Txt VARCHAR(1024))

    INSERT #srvinfo EXEC master..xp_cmdshell 'srvinfo'

    IF @@ERROR=0

    BEGIN

    --Create the table variable

    DECLARE @SQL_Instances TABLE (InstanceName VARCHAR(255), Running BIT)

    --Add the running/stopped services to the table variable

    INSERT INTO @SQL_Instances (InstanceName, Running)

    SELECT LTRIM(RTRIM(TXT)),0 FROM #srvinfo where RTRIM(LTRIM([TXT])) LIKE '%Running]%' or RTRIM(LTRIM([TXT])) like '%Stopped]%'

    --Remove the items that aren't SQL Server related

    --Note: comment to see all running services

    DELETE FROM @SQL_Instances WHERE InstanceName not like('%MSSQL%')

    --Update the running column

    UPDATE @SQL_Instances SET Running = 1 where InstanceName like '%Running%'

    --Strip out the unwanted text

    UPDATE @SQL_Instances SET InstanceName = LTRIM(RTRIM(REPLACE(InstanceName,'[RUNNING]','')))

    UPDATE @SQL_Instances SET InstanceName = LTRIM(RTRIM(REPLACE(InstanceName,'[STOPPED]','')))

    --DELETE FROM @SQL_Instances where InstanceName not like '%[Running]%' or InstanceName not like '%[Stopped]%'

    SELECT * FROM @SQL_Instances ORDER BY 1

    END

    DROP TABLE #srvinfo

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply