title: SQL Session state - Database SessionId different to ASP.NET_SessionID value
body: <p>I am using SQL Server 2008 to store the sessions for my .NET4 application. The sessions will be stored in [DatabaseA]. There isn't any other custom configuration for this, so the ASPState database is exactly how it would come out of the box (using aspnet_regsql)</p>
  
  <p>My main application runs on [DatabaseB] (same server). Within this database I have a 2 tables that record a some data along with the sessionID.</p>
  
  <p>When the [DeleteExpiredSessions] stored procedure (on DatabaseA) is run via SQL Agent, the sessions are correctly removed from the ASPState, but I want to extend this to delete the rows based on the SessionID from [DatabaseB]</p>
  
  <p>I have tried editing the [DeleteExpiredSessions] stored procedure to include the following SQL</p>
  
  <pre><code>    OPEN ExpiredSessionCursor
  
      FETCH NEXT FROM ExpiredSessionCursor INTO @SessionID
  
      WHILE @@FETCH_STATUS = 0 
          BEGIN
              -- BEGIN MY ADDITIONS
              DECLARE @myRecordCount int
              SELECT @myRecordCount= COUNT(*) FROM [DatabaseB].dbo.Table1 WHERE [DatabaseB].dbo.Table1.SessionId = @SessionID -- AND [DatabaseB].dbo.Table1.DateEntered &lt; @now
              SELECT @myRecordCount 
              DELETE FROM [DatabaseB].dbo.Table1 WHERE [DatabaseB].dbo.Table1.SessionId = @SessionID AND [DatabaseB].dbo.Table1.DateEntered &lt; @now
              DELETE FROM [DatabaseB].dbo.Table2 WHERE [DatabaseB].dbo.Table2.SessionId = @SessionID AND [DatabaseB].dbo.Table2.DateEntered &lt; @now
              -- END MY ADDITIONS
  
              DELETE FROM [DatabaseA].dbo.ASPStateTempSessions WHERE SessionID = @SessionID AND Expires &lt; @now
              FETCH NEXT FROM ExpiredSessionCursor INTO @SessionID
          END
  
      CLOSE ExpiredSessionCursor
  
      DEALLOCATE ExpiredSessionCursor
  </code></pre>
  
  <p>But @myRecordCount is returning 0 rows.
  There are no errors reported (the agent job runs correctly, and nothing in SQL Profiler), and @myRecordCount should be returning 4 in this instance.</p>
  
  <p>The DECLARE/SELECT COUNT is there as a debugger.</p>
  
  <p><strong>UPDATE</strong></p>
  
  <p>so having debugged the sql and found that :</p>
  
  <pre><code>SELECT SessionId
          FROM [ASPState].dbo.ASPStateTempSessions WITH (READUNCOMMITTED)
          WHERE Expires &lt; GETUTCDATE() 
  --Returns SessionId = '3wj5nyrlz02ezw1vvjts4gjv28d8c075'
  
  SELECT * FROM [DatabaseB].dbo.Table1 -- returns (4) results
  SELECT * FROM [DatabaseB].dbo.Table1 WHERE [DatabaseB].dbo.Table1.SessionId = '3wj5nyrlz02ezw1vvjts4gjv28d8c075' -- returns (0) results
  </code></pre>
  
  <p>I have deduced that the SessionId is wrong.
  What is being stored in <code>[DatabaseB].dbo.Table1</code> is '3wj5nyrlz02ezw1vvjts4gjv' - <em>notice the truncation of the string</em>.</p>
  
  <p>Now my .NET code (using EF6.1.3) for storing the session variables are
  <code>Table1.SessionId = HttpContext.Current.Session.SessionID</code>
  this means that <code>3wj5nyrlz02ezw1vvjts4gjv</code> is being stored.
  The Cookie <code>ASP.NET_SessionId</code> also has the same value.</p>
  
  <p>The length of the SessionId column of Table1 is the same as the ASPState tmpSession table (88)</p>
  
  <p><strong>Updated Question</strong>
  Interestingly the ASPStateTempSessions.SessionId variable seems to be appending<br>
  <code>28d8c075</code> to the end of it.
  So ASP.NET_SessionId and HttpContext.Current.Session.SessionID are <code>3wj5nyrlz02ezw1vvjts4gjv</code> but ASPStateTempSessions.SessionId is <code>3wj5nyrlz02ezw1vvjts4gjv28d8c075</code></p>
  
  <p>I have just cleared all session variables and cookies and I have a new session variable, but the 28d8c075 is still being removed/appended to the various cookies and data values.</p>
  
  <p>I understand this is happening because the ASPStateTempSessions is appending a suffix of the application hash to the SessionId (<a href="https://msdn.microsoft.com/en-us/library/aa478952.aspx" rel="noreferrer">https://msdn.microsoft.com/en-us/library/aa478952.aspx</a>)</p>
  
  <pre><code>Column Name Column Type Description
  SessionId   nvarchar(88)    Session ID + application ID
  </code></pre>
  
  <p>How do I return this to HttpContext.Current.Session.SessionID instead of just the SessionId?</p>