Executing DMX DDL from a linked server

Luckily before I left MSFT, I had the foresight to change my contact email on that old wet blog of mine that I’m no longer able to contribute to – no hard feelings.  I received a question which is something that has come up frequently enough that it just needs to be dealt with so for all future posts, you can just say “look at the Executing DMX DML from a link server post on Jamie’s new blog – the only blog that matters,” and be done with it.

So just for definition’s sake – DMX – Data Mining eXtensions to SQL, DDL – Data Definition Language, DMX DDL – DMX statements that create or modify objects!  You would think you can add two TLA’s and get an SLA, but that stands for “service level agreement” which has nothing to do with this post.  This post could also have been named “how to execute non-rowset returning commands on Analysis Services from SQL Server”, but not only do I digress, I like the actual title better with the dual unpronounceable acronyms..

Anyway, in my DMX Digest post, I referenced this post which showed how to execute DMX statements from SQL and put the results in SQL table.  In short (just in case you don’t want to click those links), you set up a linked server and then use OPENQUERY to make the DMX call.  One (well, at least one) adventurous reader sought fit to try other kinds of statements than queries – in particular a DROP MINING STRUCTURE statement.  The problem with DROP MINING STATEMENTS – and other DDL statements is that they don’t return a rowset, which is a requirement for OPENQUERY – which really wants some output columns to bind to.

The nice way to do this would be to take advantage of the SQL EXECUTE command, which, at least in SQL Server 2008, has been extended to execute commands on linked servers.  Such a command would look very elegant, like this:

EXECUTE ( 'DROP MINING STRUCTURE [MyMiningStructure]' )
AT MyDataMiningServer

Wow – that would be nice!  If only it worked, that is.  If you endeavor to try such a think you’ll get the pleasant response of “Server 'MyDataMiningServer' is not configured for RPC.”  What this means, evidently, is that the nice way of doing things isn’t going to happen.

But, never fear, we can take advantage of all that boundless flexibility built in to SQL Server Data Mining to make it happen.  All we need to do is to create some kind of statement that can be called from SQL Server’s OPENQUERY that executes a statement of our choosing.  And the way to do this is to write a stored procedure that executes a statement and returns some sort of table.  This is the really big hammer solution to the problem.

And what do you know, I happen to have that stored procedure right here…..

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using Microsoft.AnalysisServices.AdomdServer;

namespace DMXecute
{
    public class Class1
    {
        [SafeToPrepare(true)]
        public DataTable DMXecute(string statement)
        {
            DataTable Result = new DataTable("Table");
            Result.Columns.Add("Column", typeof(int));
            if (Context.ExecuteForPrepare)
                return Result;

            AdomdCommand cmd = new AdomdCommand(statement);
            cmd.ExecuteNonQuery();

            return Result;
        }
    }
}

And calling it from SQL Server – easy

EXEC sp_addlinkedserver @server='MyDataMiningServer', -- local SQL name given to the linked server
@srvproduct='', -- not used 
@provider='MSOLAP', -- OLE DB provider 
@datasrc='localhost', -- analysis server name (machine name) 
@catalog='MyDMDatabase' -- default catalog/database 

GO

SELECT * FROM OPENQUERY(MyDataMiningServer,'CALL DMXecute.DMXecute("DROP MINING STRUCTURE [MyMiningStructure]")')

GO

Of course, you can execute any DMX or MDX statement you want there, so this is simply dangerous is general – you definitely shouldn’t be sending unvalidated user input through here for fear of SQL Injection style attacks.  A better way, in general, would be to write stored procedures that performed exactly the operations you need taking just the object name as a parameter.