Solved

SQL Server retrieving error message from sysjobhistory using PATINDEX

Posted on 2008-06-20
4
991 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

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

867 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

17 Experts available now in Live!

Get 1:1 Help Now