Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

SQL Server retrieving error message from sysjobhistory using PATINDEX

Posted on 2008-06-20
4
Medium Priority
?
1,018 Views
Last Modified: 2010-04-21
I am using this code which is bombing out on the message field. I need to retrieve the message column but I want just the error message starting with the '%Error%'  Also this is being exported to an EXCEL spreadsheet so the message can not be greater than 255 characters.

SELECT      
      Server = CAST(a.server AS NVARCHAR(50))
      ,Job = CAST(b.name AS NVARCHAR(50))
      ,a.Step_Id
      ,a.Message = CASE a.Message
            WHEN a.Message Like '%Error%' THEN SUBSTRING(a.message,0, PATINDEX('%Error%',a.message))
            ELSE a.Message
            END
      ,enabled = CASE b.enabled
         WHEN 0 THEN 'No'
         WHEN 1 THEN 'Yes'
      END
    ,StartDateTime = CONVERT
    (
        DATETIME,
        RTRIM(run_date)
    )
    +  
    (
        run_time * 9
        + run_time % 10000 * 6
        + run_time % 100 * 10
    ) / 216e4
      ,a.Run_Duration
      ,a.Run_Date
      ,a.Run_Time
      ,Run_Status = CASE CAST(a.run_status AS NVARCHAR(11))
                  WHEN 0 THEN 'Failed'
                  WHEN 1 THEN 'Succeeded'
                  WHEN 2 THEN 'Retry'
                  WHEN 3 THEN 'Canceled'
                  WHEN 4 THEN 'In progress'
            END
--      ,lastupdate = GETDATE()
FROM      msdb.dbo.sysjobhistory a
INNER JOIN      msdb.dbo.sysjobs b ON
      a.job_id = b.job_id
WHERE      a.step_id <> 0
AND      CONVERT
    (
        DATETIME,
        RTRIM(run_date)
    )
    +  
    (
        run_time * 9
        + run_time % 10000 * 6
        + run_time % 100 * 10
    ) / 216e4 > DATEADD(dd, -7, getdate())
ORDER BY NAME , StartDateTime
SELECT	
	Server = CAST(a.server AS NVARCHAR(50))
	,Job = CAST(b.name AS NVARCHAR(50))
	,a.Step_Id
	,a.Message = CASE a.Message
		WHEN a.Message Like '%Error%' THEN SUBSTRING(a.message,0, PATINDEX('%Error%',a.message))
		ELSE a.Message
		END
	,enabled = CASE b.enabled
         WHEN 0 THEN 'No'
         WHEN 1 THEN 'Yes'
      END
    ,StartDateTime = CONVERT 
    ( 
        DATETIME, 
        RTRIM(run_date) 
    ) 
    +  
    ( 
        run_time * 9 
        + run_time % 10000 * 6 
        + run_time % 100 * 10 
    ) / 216e4
	,a.Run_Duration
	,a.Run_Date
	,a.Run_Time
	,Run_Status = CASE CAST(a.run_status AS NVARCHAR(11))
			WHEN 0 THEN 'Failed'
			WHEN 1 THEN 'Succeeded'
			WHEN 2 THEN 'Retry'
			WHEN 3 THEN 'Canceled'
			WHEN 4 THEN 'In progress'
		END
--	,lastupdate = GETDATE()
FROM	msdb.dbo.sysjobhistory a
INNER JOIN	msdb.dbo.sysjobs b ON 
	a.job_id = b.job_id
WHERE	a.step_id <> 0
AND	CONVERT 
    ( 
        DATETIME, 
        RTRIM(run_date) 
    ) 
    +  
    ( 
        run_time * 9 
        + run_time % 10000 * 6 
        + run_time % 100 * 10 
    ) / 216e4 > DATEADD(dd, -7, getdate())
ORDER BY NAME , StartDateTime

Open in new window

0
Comment
Question by:fusionpunch
  • 2
  • 2
4 Comments
 
LVL 51

Expert Comment

by:Mark Wills
ID: 21840218
OK, two things, first not part of the aliased table, so not a.message, and second, the case statement was just a little confused...

try:

      ,Message = CASE
            WHEN a.Message Like '%Error%' THEN SUBSTRING(a.message,0, PATINDEX('%Error%',a.message))
            ELSE a.Message
            END
0
 

Author Comment

by:fusionpunch
ID: 21845196
This solves the error I was getting but still does not address the issue that I only want to select 255 characters starting with the Error string which can be anywhere in the message field.
0
 
LVL 51

Accepted Solution

by:
Mark Wills earned 500 total points
ID: 21845541
sorry about that, forgot the second bit...

 ,Message = CASE
            WHEN a.Message Like '%Error%' THEN SUBSTRING(a.message, PATINDEX('%Error%',a.message), 255)
            ELSE a.Message
            END

0
 

Author Closing Comment

by:fusionpunch
ID: 31469252
Excellent thank you so much. This is excatly what I was looking for!
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

963 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question