title: In MSSQL (TSQL), Can I specify a contextual variable that lives in a connection string, but does not affect pooling?
body: <p>To explain the need further, consider this scenario:</p>
  
  <p>In a legacy system that relies heavily on <code>TRIGGERS</code>, we need to provide a token of some sort (let's call it "SessionID") to be inserted into some security log tables. This token is created in C# in the application server, and will be passed into <strong>all</strong> SQL commands.</p>
  
  <p><strong>SADLY, THIS REQUIREMENT ON TRIGGERS IS UNCHANGEABLE</strong></p>
  
  <p>So, because I have access to modify the connection string, I can (and have successfully proven that I can) use the "Application Name" token to provide this piece of information.</p>
  
  <pre><code>string connectionString = string.Format("SERVER=sql.example.com; "
      + "DATABASE=someDB; User ID=xyz; Password=123; Application Name={0}", sID);
  </code></pre>
  
  <h2>Since this works, what's the problem?</h2>
  
  <p>The problem is very simple... the above works, but since we have thousands of users... our connection-pool is destroyed (since connection pools are made based on the connection string... basically, I need the connection pool to be based on everything <em>except</em> the <code>Application Name</code> property).</p>
  
  <p>So, do you know of how I can either:</p>
  
  <ol>
  <li>Set a property in the connection string that will NOT be included in pooling.</li>
  <li>Set a contextual property for this connection some other way that is not incredibly heavy on performance.</li>
  </ol>
  
  <p>As a side-note... I <em>could</em> open the connection right away and create a temp table with a single value:</p>
  
  <pre><code>SELECT 12345 AS SessionID INTO #context
  </code></pre>
  
  <p>But that seems incredibly overkill!</p>