Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

SQL Server retrieving error message from sysjobhistory using PATINDEX

Posted on 2008-06-20
4
Medium Priority
?
1,020 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

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Question has a verified solution.

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

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Suggested Courses

564 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