sql-servercmdssasxmlaascmd

Run xmla file (Analysis Services) from command line


I'm looking for tool or a easy way to run xmla script (example for create or delete cube). I used to make exe file using Inno Setup program and there I can write command which can run another exe file just like in command line.

I found that there is such tool such as ascmd.exe (Readme For Ascmd Command-line Utility Sample). But it was used in older versions of MS SQL. Is there any other for MS SQL Server 2012 and newer versions?

I can say that I wasn't use ascmd.exe tool because I wasn't able to get this tool (I couldn't compile the project in C# from here: Readme For Ascmd Command-line Utility Sample).


Solution

  • I wrote my own exe file in C# which I can run from command line. Maybe my code will help someone with similar problem. :)

    using Microsoft.AnalysisServices;
    
    string cubeServerName = args[1];    
    string cubeName = args[2];  
    
    Server server = null;
    try
    {
        server = new Server();
        server.Connect("Data source=" + cubeServerName + ";Timeout=7200000;Integrated Security=SSPI;");
    }
    catch (Exception e)
    {
        return (int)ExitCode.UnknownError;
    }
    
    string sqlServerName = args[3]; 
    string user = args[4];
    string pass = args[5];
    string path = args[6];
    
    string xmlaScript = "";
    try
    {
        xmlaScript = System.IO.File.ReadAllText(@path);
    }
    catch (Exception e)
    {
        return (int)ExitCode.InvalidFilename;
    }
    
    if (server != null)
    {
        try
        {
            int exitCode = 0;
            if (xmlaScript != "")
                exitCode = ServerExecute(server, xmlaScript);
            server.Disconnect();
            return exitCode;
        }
        catch (Exception e)
        {
            return (int)ExitCode.UnknownError;
        }
    }
    
    //...
    
    private static int ServerExecute(Server server, string command)
    {
        XmlaResultCollection results = server.Execute(command);
    
        foreach (XmlaResult result in results)
        {
            foreach (XmlaMessage message in result.Messages)
            {
                if (message is XmlaError)
                {
                    Console.WriteLine("ERROR: {0}", message.Description);
                    return (int)ExitCode.UnknownError;
                }
                else
                {
                    System.Diagnostics.Debug.Assert(message is XmlaWarning);
                    Console.WriteLine("WARNING: {0}", message.Description);
                }
            }
        }
        return (int)ExitCode.Success;
    }