Script error

  • sizal0234

    SSCrazy

    Points: 2194

    I am trying to monitor the sql services on couple of instances and found the following script online which kinds of  creates tables with sql server services details.

    set nocount on

    -- Variable to store windows server name

    DECLARE @server_name varchar(100)

    DECLARE @SQLInstance_name varchar(100)

    -- table to store PowerShell script output

    CREATE TABLE #output

    (line varchar(max) null)

    -- Declaring cursor to fetch windows server name

    DECLARE server_cursor CURSOR FOR

    select distinct LTRIM(rtrim(windowsservername)) as windowsServerName,SQLInstanceName from tbl_sqlcmdb

    OPEN server_cursor

    FETCH NEXT FROM server_cursor

    INTO @server_name, @SqlInstance_Name

    WHILE @@FETCH_STATUS = 0

    BEGIN

    declare @svrName varchar(255)

    declare @sql varchar(400)

    set @svrName = @server_name

    -- Preparing PowerShell Dynamic Statement

    set @sql = 'powershell.exe -c "Get-WmiObject -ComputerName ' + QUOTENAME(@svrName,'''') + ' -Class win32_service | where {$_.name -like ' + QUOTENAME('*SQL*','''') + '} | select-object Name,state,systemname,startmode,startname | for each{$_.name+''|''+$_.state+''%''+$_.systemname+''*''+$_.startmode+''@''+$_.startname+''!''}"'

    -- Inserting PowerShell Output to temporary table

    insert #output

    EXEC xp_cmdshell @sql

    -- Deleting the rows which contains error or has not sufficient data

    delete from #output where len(line) < 30

    update #output set line = line + '!' where line not like '%!%'

    IF (SELECT COUNT(*) FROM #output where line like '%Get-Wmi%') = 0

    begin

    insert into tbl_serviceStatus(SQLServiceName, servicestatus, windowservername, startmode, startname, InstanceName)

    select rtrim(ltrim(SUBSTRING(line,1,CHARINDEX('|',line) -1))) as SQLServiceName

    ,(rtrim(ltrim(SUBSTRING(line,CHARINDEX('|',line)+1, (CHARINDEX('%',line) -1)-CHARINDEX('|',line)) ))) as ServiceStatus

    --,(rtrim(ltrim(SUBSTRING(line,CHARINDEX('%',line)+1, (CHARINDEX('*',line) -1)-CHARINDEX('%',line)) ))) as WindowsServerName

    ,@server_name

    ,(rtrim(ltrim(SUBSTRING(line,CHARINDEX('*',line)+1, (CHARINDEX('@',line) -1)-CHARINDEX('*',line)) ))) as startmode

    ,(rtrim(ltrim(SUBSTRING(line,CHARINDEX('@',line)+1, (CHARINDEX('!',line) -1)-CHARINDEX('@',line)) ))) as startname

    ,@SQLInstance_name

    from #output

    where line is not null and LEN(line) > 30

    end

    -- Clearing output table

    truncate table #output

    -- Next windows record

    FETCH NEXT FROM server_cursor

    INTO @server_name,@SQLInstance_name

    END

    CLOSE server_cursor;

    DEALLOCATE server_cursor;

    -- dropping the temporary table

    drop table #output

     

    Here is the error below...please suggest the fix.

     

    Msg 537, Level 16, State 3, Line 29

    Invalid length parameter passed to the LEFT or SUBSTRING function.

    The statement has been terminated.

    Msg 537, Level 16, State 3, Line 29

    Invalid length parameter passed to the LEFT or SUBSTRING function.

    The statement has been terminated.

  • sizal0234

    SSCrazy

    Points: 2194

    Also, please suggest any idea for sending an alert when sql service stops on any instance..? thanks!

  • Mr. Brian Gale

    SSC-Insane

    Points: 22935

    To fix problem number 1 (LEFT or SUBSTRING error), lets look at your query for all cases of LEFT or SUBSTRING.  That gives us this small chunk of  your code:

    select rtrim(ltrim(SUBSTRING(line,1,CHARINDEX('|',line) -1))) as SQLServiceName

    ,(rtrim(ltrim(SUBSTRING(line,CHARINDEX('|',line)+1, (CHARINDEX('%',line) -1)-CHARINDEX('|',line)) ))) as ServiceStatus

    --,(rtrim(ltrim(SUBSTRING(line,CHARINDEX('%',line)+1, (CHARINDEX('*',line) -1)-CHARINDEX('%',line)) ))) as WindowsServerName

    ,@server_name

    ,(rtrim(ltrim(SUBSTRING(line,CHARINDEX('*',line)+1, (CHARINDEX('@',line) -1)-CHARINDEX('*',line)) ))) as startmode

    ,(rtrim(ltrim(SUBSTRING(line,CHARINDEX('@',line)+1, (CHARINDEX('!',line) -1)-CHARINDEX('@',line)) ))) as startname

    ,@SQLInstance_name

    from #output

    EDIT - removed a chunk here because it is wrong.  Your DELETE and WHERE clause make sure that blank string are handled.  What about error strings?  If the server doesn't exist, you are inserting an error message from powershell into your table.  That could be the problem.  Might not hurt to look at the data prior to doing the substrings and see what data exists.

    As for the second one, SQL cannot monitor it's own services stopping.  Your best bet here is some proper monitoring software on a specific server to watch and make sure the services stay up.  Failing that, assuming you can trust the server the above query runs on, you could grab the value of "started" along with the other properties and you would see if it is running now.  But this is only valid based on how frequently you run the above script.  If it is a run-once script, that isn't helpful.  But you could fine tune the powershell to only bring back the "started" property, dump it into a table and then send an alert if "SELECT COUNT(1) FROM <table> WHERE started = false" has more than 0 rows?  Won't work if you have a cold failover though.

    • This reply was modified 1 month, 1 week ago by  Mr. Brian Gale. Reason: EDIT - had bad process in there. not a blank or empty data problem

Viewing 3 posts - 1 through 3 (of 3 total)

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