tim laqua dot com Thoughts and Code from Tim Laqua

7Mar/096

SQL Server Analysis Services Command (XMLA) Agent Job Step Reports Success On Command Failure

I ran in to this the other day, did some googling, and really did not like what I saw for workarounds. In SQL Server 2005, when an XMLA job step fails (returns an Exception node in the XML response), the job step still reports success (because it's defining success as "did I get a response") - this has been fixed in SQL Server 2008. Common workarounds are using ascmd.exe or SSIS to handle the XMLA commands (ish - both of those solutions add a lot of complexity for a simple problem). So, I came up with a workaround that checks the text of the previous job step for the substring "<Exception ". It's been working thus far, with no issues.

After each XMLA command step, insert a T-SQL step to verify that the XMLA command step succeeded:

DECLARE @JobName VARCHAR(64)
 
SET @JobName = ‘Name Of Job This Step Belongs to’
 
DECLARE @Message VARCHAR(1024)
 
SELECT TOP 1 @Message = CAST([message] AS VARCHAR(1024))
FROM msdb.dbo.sysjobhistory a
INNER JOIN msdb.dbo.sysjobs b
ON a.job_id = b.job_id AND b.[NAME] = @JobName
ORDER BY run_date DESC, run_time DESC, step_id DESC
 
IF @Message LIKE%<Exception %RAISERROR (@Message, 17, 1)

UPDATE (2009-04-03): Added , step_id DESC to ORDER BY clause - when the XMLA job fails instantly (say you tried to process a nonexistant partition), run_time doesn't have enough granularity to sort properly.

Once your done, your job steps will look something like this:

xmla-command-verification-steps

Comments (6) Trackbacks (1)
  1. what happens if you have multiple jobs with the same job name (and you can’t change them?)

  2. In SQL Server 2005, you can’t have two jobs on the same server that have the same name 😉

  3. Fantastic, been looking for a code snippet to error trap exactly this problem for ages.

    Many, many thanks.

  4. This is a good idea. I’m going to couple this with a retry through SQL Agent, as most of the time the Analysis Services stuff fails it seems to be a random hiccup in the processing, and retrying it it works.
    So I just have this step go to a couple of the Analysis Services step as it’s On Failure action.

    • 说:This can be my third visit to this weblog. I am cdnisoering about starting a completely new weblog within the same category. Your website provided me with suggestions to function with. (Don’t worry, I won’t be copying you ) You have completed a great job.

  5. You are genius!!!

    THANK YOU!!


Leave a comment