We help IT Professionals succeed at work.

How do I execute some XMLA from inside a Stored proc (exec(@xml) doesnt work)

I have a stored procedure which builds some XMLA statements to create some roles.

The statements get stored in the @xml varchar variable.  I was hoping I could run exec (@xml) like I would normally do with SQL but that doesnt work.  Any idea how I run them?

Any help would be much appreciated.

Watch Question

Here are two methods:

Method I  - Use SQL Agent job
1) Create a job with SQL Server Analysis Services Command step inside (put your XMLA in the step).
2) In your stored procedure use msdb.dbo.sp_start_job system stored procedure to start the job and execute XMLA.

Method II - Use ascmd.exe
1) Download SP2 samples and locate ascmd.exe command line utility (this is something like sqlcmd.exe for database engine - it allows to execute XMLA on SSAS servers).
2) In your stored procedure use xp_cmdshell extended procedure to call ascmd.exe with the proper XMLA as input.

It seems the first method should be prefered for security reasons. But the second seems to be more flexible (you can execute any XMLA code).


Thanks brejk, I think i'll have to go with method 2 as the script is built dynamically.