In SQL2005, you can create SQL Agent jobs. They can be scheduled, have multiple steps, alert, notify, etc. Pretty great setup. There are some downfalls though.
Like in order to call a job from a job, you need to execute the second job with T-SQL. Thing is, it doesn’t run synchronously. It runs asynchronously, which really stinks if you want to wait to see if the second job completes successfully or what not.
Another thing, if you call an XMLA script from a SQL Agent job, if the XMLA query or command fails, the SQL Agent job still reports success – that’s no good! What can you do? Use ascmd.exe!
ascmd.exe is a utility that you actually have to download the SQL Samples from CodePlex (http://www.codeplex.com/SqlServerSamples/Release/ProjectReleases.aspx?ReleaseId=4000) and then build the ascmd solution, to get ascmd.exe A few notes. The samples are VS2005, and I don’t have that installed ,so I had to open with VS2008, then it is digitally signed, and when building couldn’t find the .snk file to sign it, so I turned that off as well, after I had it built, I did some testing locally and made sure it would work as I wanted it to.
You can test by just calling it from the cmd line, for example:
ascmd.exe -S SSASServerName -d SSASDatabaseName -i MyXMLA.xmla
you can use -Q and try to pass in XMLA, but then you have to handle all the special characters and what not, which is a pain. Now, if you just put this on your C drive, (or wherever), then create a SQL Agent Job to call this command, it will fail the job if the ascmd.exe reports back failure. Exactly what we want!!
You will notice in the SQL Agent job setup that you can specify the value for a failed command – 0 is the default and that is what we want.
Now, get some XMLA that you know works, set it up in the xmla file and test it, the job should succeed. Now just change something in the xmla (like CubeId) to some fake value and test it, the job should fail, and you can have it alert your DBA’s or whoever.
Pretty sweet, but I wish SQL Agent would handle failed XMLA like a failed query and report failure, I am not sure if in SQL 2008 it does that or not, but it would make life a lot easier. Otherwise you could be scratching your head trying to find where stuff is failing, looking in logs, etc, but not seeing any failures. The only way you would be able to tell is to run the XMLA manually, ugh 🙂
Steve Novoselac's Blog 
9 replies on “SQL 2005, SSAS 2005: Using ascmd.exe To Create SQLAgent Jobs That Give You Completion Status”
Trackback…
……
LikeLike
Steve,
I have downloaded the samples from Codeplex.com- From SSAS databses to Adjenture works to the SQL server engine. I generated a strong Key but I have not figured how to compile the code. I opned VS and the ascmd.sln file generated an error when I opened it.
Would you be so kind as to itemize the steps required to generate the utility. satrting with what specifically to download.
Thanks
Tee
LikeLike
What error? My first guess is that you don’t have the full visual studio installed, only the BIDS studio. If you really need ascmd.exe I can email you the exe…let me know
LikeLike
Steve,
The error I got was that the application for the project – ascmd.csproj was not instaled.
Yes, I would appreciate it if you could send the executable. Please send it to the new email. I’ll let you know how things go.
Thanks
Tee
LikeLike
Or, you can just insert a check step after each XMLA step containing T-SQL along these lines:
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
IF @Message LIKE ‘%<Exception %’
RAISERROR (@Message, 17, 1)
LikeLike
cool that works. What I usually do now is processing from SSIS and then on the error output call a Notify Operator task, on the valid output I do the same to notify when the cube was loaded
LikeLike
I hear you guys are gettin’ a solid DBA soon 😉
LikeLike
Yeah, I know how to pick em 🙂
LikeLike
[…] have also blogged about using ascmd.exe here . ascmd comes with the SSAS Samples with SQL 2005 (usually located here: C:Program FilesMicrosoft […]
LikeLike