• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2232
  • Last Modified:

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.

1 Solution
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).
kintonAuthor Commented:
Thanks brejk, I think i'll have to go with method 2 as the script is built dynamically.


Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now