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.

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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).

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
kintonAuthor Commented:
Thanks brejk, I think i'll have to go with method 2 as the script is built dynamically.

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.