Solved

SQL Server retrieving error message from sysjobhistory using PATINDEX

Posted on 2008-06-20
4
1,006 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 125 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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
sql query help 15 54
SSMS Imprt data from Excel 7 25
SQL Server 2008 Std. License Key owner or vendor 4 56
Make an array show the subkey and put it in a query 2 28
Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
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

749 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