Run xmla file (Analysis Services) from command line

Question

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

score 1 · Accepted Answer · edited Sep 21 '17 at 05:14

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;
}

which dlls or NuGet package did you refer for your C# code? What namespaces did you refer? In which namespace does this `Server` class reside? — RBT, Jul 10 '17 at 11:16
@RBT It looks like they are in Microsoft.AnalysisServices.Core.dll or Microsoft.AnalysisServices.dll which are in the GAC. Details here: https://msdn.microsoft.com/en-us/library/microsoft.analysisservices.aspx — DarcyThomas, Sep 21 '17 at 04:14

score 0 · Answer 2 · answered Oct 22 '15 at 21:38

I would recommend you download ASCMD_StressTestingScripts (which contains Ascmd.exe) from: https://sqlsrvanalysissrvcs.codeplex.com/releases

Then create an Ascmd.exe.config file with the following contents in the same directory as Ascmd.exe. This will cause it to use the SQL 2014. If you want SQL 2012, then change 12.0.0.0 to 11.0.0.0:

<configuration>
   <runtime>
      <assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1">
         <dependentAssembly>
            <assemblyIdentity name="Microsoft.AnalysisServices" publicKeyToken="89845dcd8080cc91" />
            <bindingRedirect oldVersion="10.0.0.0" newVersion="12.0.0.0" />
            <publisherPolicy apply="no" />
         </dependentAssembly>
      </assemblyBinding>
   </runtime>
</configuration>

Run xmla file (Analysis Services) from command line

2 Answers2

Linked