Click here to Skip to main content
Click here to Skip to main content

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

, 25 Dec 2009
Rate this:
Please Sign up or sign in to vote.
Establishing of an existent connection to SQL Server 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.

License

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

About the Author

db_developer
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 Pinmemberrakesh150311-Sep-12 22:04 

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

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

| Advertise | Privacy | Mobile
Web01 | 2.8.140721.1 | Last Updated 26 Dec 2009
Article Copyright 2009 by db_developer
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid