Using Dynamic Values in XMLA
A question was raised on Twitter today via the #sqlhelp hash tag about passing parameter values to XMLA for a backup script. The popular answer at the time I saw it was to dynamiclly create and drop a SQL job to run the script every time you need it to run. I proposed a different solution,
The following request was sent in a series of tweets:
Is there a way to pass variables between tsql and mdx queries within a sql job to automate #SSAS db backups. #sqlhelp
Correction: Is there a way to pass variables between tsql and XMLA queries within a sql job to automate #SSAS db backups. #sqlhelp
I have the tsql part working which creates the backup syntax, now I need it to execute the XMLA to perform the backups. #sqlhelp
My Proposed Solution
As I stated above, the popular solution was to use dynamic SQL to have it create a job that runs the backup and then delete the job. In my session A DBAs Guide to Administering BI Systems at the PASS Summit 2011, I talked about executing MDX queries via T-SQL across a linked server. This approach works with XMLA as well. I simply need to use dynamic SQL to build the XMLA command and then execute it across the linked server. I set up a test to demonstrate how to do it before recommending it.
I started by using the SSMS GUI to script out the backup command for the Adventure Works DW 2008R2 database in my local SSAS instance. I suspect that adding a date serial to the backup name is likely to be the reason for using this, that is the scenario I used. I then use the EXEC() AT command to execute the string across a linked server (named SSAS in my sample code).
Declare @XMLA nvarchar(1000), @DateSerial nvarchar(35); -- Change date to format YYYYMMDDHHMMSS Set @DateSerial = Convert(nvarchar, getdate(), 112) + Replace(Convert(nvarchar, getdate(), 108), ':', ''); -- Create the XMLA string Set @XMLA = N'<Backup xmlns="http://schemas.microsoft.com/analysisservices/2003/engine"> <Object> <DatabaseID>Adventure Works DW 2008R2</DatabaseID> </Object> <File>c:\bak\Adventure Works DW 2008R2_' + @DateSerial + '.abf</File> </Backup>'; -- Execute the string across the linked server (SSAS) Exec (@XMLA) At SSAS;