Establishing an Existent Connection with a Sample: XML/A Script Execution Tracing

Introduction

If you need to establish an existent connection and bind the next object to it, you have to use the Session ID. Each SQL Server connection object (I mean, in each object's model) has a property, SessionID (the session to connect to). First, we connect to the server as usual, and then we use the session ID of this connection each time we want to re-use an existent connection.

First, what I tried was to pass to the connection string: "Context Connection=true", but it causes an exception: "The 'Context Connection' property name is not formatted correctly." This technique can be used in SQL Server CLR Integration only.

You may to use a separate connection for each operation, or you may keep the connection and pass it to the command each time you need it. Yes, this is a solution, but it is impossible to implement if you have to keep the connection from one object model, say, Analysis Management Objects, during command execution via another one, say, ADOMD.NET. I will show you what I mean.

Tracing of XML/A script execution

...
Server server = new Server();
server.Connect("localhost");
string sessionID = server.SessionID;  //keep for future usage
if (server.Connected)
{

    server.SessionTrace.OnEvent += 
      new TraceEventHandler(SessionTrace_OnEvent);
    server.SessionTrace.Start();

    ExecuteScript("XML/A script", sessionID);

    server.SessionTrace.Stop();
    server.Disconnect();
}
server.Dispose();
server = null;

...

static void SessionTrace_OnEvent(object sender, TraceEventArgs e)
{
    Debug.WriteLine(e.TextData);
}
...

public void ExecuteScript(string xmlaScript, string sessionID)
{
    //execute script using AS connection string
    using (AdomdConnection conn = new AdomdConnection(SSASConnectionString))
    {
        conn.SessionID = sessionID;   //use existent connection
        conn.Open();

        AdomdCommand cmd = new AdomdCommand(xmlaScript, conn);
        cmd.ExecuteNonQuery();

        conn.Close();
    }
}

In case you do not use the same session, your SQL Server tracing will be terminated as soon as you create an ADOMD.NET connection, and all script execution will be processed silently. SessionID allows to pass a connection from one object model to another (from AMO to ADOMD.NET, for instance). So the Session concept is quite helpful.