Solved

SQL Server retrieving error message from sysjobhistory using PATINDEX

Posted on 2008-06-20
4
1,009 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Comparison of Amazon Drive, Google Drive, OneDrive

What is Best for Backup: Amazon Drive, Google Drive or MS OneDrive? In this free whitepaper we look at their performance, pricing, and platform availability to help you decide which cloud drive is right for your situation. Download and read the results of our testing for free!

Question has a verified solution.

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

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.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.

734 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