Solved

SQL Server retrieving error message from sysjobhistory using PATINDEX

Posted on 2008-06-20
4
985 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Excellent thank you so much. This is excatly what I was looking for!
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

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 …
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
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…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

771 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now