Split Function to break the Job History Message into a Table

,

DECLARE

    @JobMessage varchar(MAX)

    

SET @JobMessage = ( SELECT TOP 1 CONVERT( varchar(MAX),REPLACE( jh.Message, '--','' ) )

FROM msdb.dbo.sysjobs j

INNER JOIN msdb.dbo.sysjobhistory jh ON j.job_id=jh.job_id

WHERE name LIKE '<SQL Jobs Name>'

AND run_status = 0 AND run_date = CONVERT(varchar, getdate(),112)

AND run_time > REPLACE( CONVERT(varchar, DATEADD( n, -80, getdate() ),108), ':','') )

        

SELECT TOP 1 items

FROM dbo.[Split_JobErrorMessage] ( @JobMessage, '     ' )

WHERE items LIKE 'Description: SSIS Error Code%'

ORDER BY items asc

CREATE FUNCTION [dbo].[Split_JobErrorMessage] 
(
       @String varchar(8000)
       , @Delimiter char(5) )
RETURNS @temptable TABLE ( items varchar(8000) )
AS
/* **********************************************
Description: Split Function for t-Sql Server to break the Job History 
Message into Table
Resources: Modify version - Split Function in Sql Server to break 
Comma-Separated Strings into Table
URL: http://www.logiclabz.com/sql-server/split-function-in-sql-server-to-break-comma-separated-strings-into-table.aspx
Created by: LGM - 05/07/2010
** *********************************************/
BEGIN
DECLARE
     @idx INT
     , @slice VARCHAR(MAX)    

SELECT @idx = 1
IF LEN( @String ) < 1 OR @String IS NULL 
RETURN    

WHILE @idx!= 0
BEGIN
     SET @idx = charindex( @Delimiter, @String )
     IF @idx!=0    
        SET @slice = LEFT( @String, @idx - 1 )
     ELSE
        SET @slice = @String

     IF (len(@slice)>0)
        INSERT INTO @temptable(Items)
        VALUES(LTRIM(RTRIM(@slice)))

     SET @String=RIGHT(@String,LEN(@String)-@idx)

     IF LEN(@String)=0 
        BREAK
   END
RETURN    
END
GO

Rate

Share

Share

Rate