Click here to Skip to main content
15,610,563 members
Articles / Database Development / SQL Server
Posted 25 Dec 2009


3 bookmarked

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

Rate me:
Please Sign up or sign in to vote.
0.00/5 (No votes)
25 Dec 2009CPOL1 min read
Establishing of an existent connection to SQL Server with a sample: XML/A script execution tracing.


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();
string sessionID = server.SessionID;  //keep for future usage
if (server.Connected)

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

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

server = null;


static void SessionTrace_OnEvent(object sender, TraceEventArgs e)

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

        AdomdCommand cmd = new AdomdCommand(xmlaScript, conn);


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.


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Written By
Database Developer Freelancer
Ukraine Ukraine
MS SQL Server Database Developer with 7+ years experience

Technologies/languages: Business Intelligence, SQL, MDX, VBA, SQL Server, Analysis Services (SSAS), Reporting services (SSRS), Integration Services (SSIS), DataWarehouse.
Also: economic background.

Feel free to contact me for rates and details.

Comments and Discussions

QuestionEstablishing a connection with specific session ID Pin
rakesh150311-Sep-12 22:04
professionalrakesh150311-Sep-12 22:04 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.