SQL会话状态-数据库SessionId与ASP.NET_SessionID值不同

解决了

问题（正如我最初诊断的那样）是应用程序ID没有传递给SessionID变量。因此，我执行了以下步骤：

1）在Web.Config中创建了一个后备变量（AppName不太可能更改）

<add key="AppId" value="685293685"/>

为什么？-这是从ASPState [TempGetAppID]返回的appName的ID

2）[GetApplicationIdForSession]在DatabaseB中创建一个存储过程

DECLARE @appId int

    EXEC    [ASPState].dbo.[TempGetAppID]
        @appName = @iisName,
        @appId = @appId OUTPUT

    SELECT  @appId as N'AppId'

为什么？-这利用了将AppName哈希到正确的AppId的内置SQL State SP来保持一致性。（即我不是在编写单独的.net函数来尝试获得与SQL相同的值）

3）在Global.asax.vb中

Sub Application_Start(ByVal sender As Object, ByVal e As EventArgs)
        Using db As New SqlConnection(ConfigurationManager.ConnectionStrings("LocalServer").ConnectionString)
            db.Open()
            Using cmd As SqlCommand = db.CreateCommand()
                cmd.CommandText = "GetApplicationIdForSession"
                cmd.CommandType = CommandType.StoredProcedure
                cmd.Parameters.AddWithValue("iisName", HttpRuntime.AppDomainAppId.ToLower)
                Using dr As SqlDataReader = cmd.ExecuteReader()
                    If dr IsNot Nothing AndAlso dr.Read() Then
                        Application("AppId") = CType(dr("appId"), Integer)
                    Else
                        Application("AppId") = CType(ConfigurationManager.AppSettings("AppId"), Integer)
                    End If
                End Using
            End Using
            db.Close()
        End Using
        ' Fires when the application is started
    End Sub

为什么？-HttpRuntime.AppDomainAppId与ASPState数据库（DatabaseA）中使用的值相同。因此我将其传递给在步骤2中创建的存储过程，然后将输出存储在应用程序变量中，这样我就不必每次都访问数据库来获取sessionid。

4）在我的页面继承自的基类中创建此函数

Public Function GetAppIdHash() As String

        Dim hashCode As Integer = CType(Application("AppId"), Integer)
        Return ((hashCode).ToString("X2")).ToLower

    End Function

为什么？-这将获取应用程序变量（“ AppId”）并返回十六进制输出

5）将存储会话ID变量的.net代码从：Table1.SessionId = HttpContext.Current.Session.SessionID更改为Table1.SessionId = HttpContext.Current.Session.SessionID & GetAppIdHash()

6）DeleteExpiredSessionsSP被更新为此处列出的-happy中期版本- http://sqlperformance.com/2013/01/t-sql-queries/optimize-aspstate和附加SQL命令被附加到它。

ALTER PROCEDURE [dbo].[DeleteExpiredSessions]
  @top INT = 1000
AS
BEGIN
  SET NOCOUNT ON;

  DECLARE @now DATETIME, @c INT;
  SELECT @now = GETUTCDATE(), @c = 1;

 /* START Additional SQL */
    CREATE TABLE #tblExpiredSessions 
    ( 
        SessionId nvarchar(88) NOT NULL PRIMARY KEY
    )
    INSERT #tblExpiredSessions (SessionId)
        SELECT SessionId
        FROM [ASPState].dbo.ASPStateTempSessions WITH (READUNCOMMITTED)
        WHERE Expires < @now
    /* END Additional SQL */

  BEGIN TRANSACTION;

  WHILE @c <> 0
  BEGIN
    ;WITH x AS 
    (
      SELECT TOP (@top) SessionId
        FROM [ASPState].dbo.ASPStateTempSessions
        WHERE Expires < @now
        ORDER BY SessionId
    )
    DELETE x;

    SET @c = @@ROWCOUNT;

    IF @@TRANCOUNT = 1
    BEGIN
      COMMIT TRANSACTION;
      BEGIN TRANSACTION;
    END
  END

  IF @@TRANCOUNT = 1
  BEGIN
    COMMIT TRANSACTION;
  END

  /* START Additional SQL */
  DELETE FROM DatabaseB.dbo.Table1 WHERE DatabaseB.dbo.Table2.SessionId in (SELECT * FROM #tblExpiredSessions)
  DELETE FROM DatabaseB.dbo.Table2 WHERE DatabaseB.dbo.Table2.SessionId in (SELECT * FROM #tblExpiredSessions)

  DROP TABLE #tblExpiredSessions
  /* END Additional SQL */
END

旁注-我不是SQL向导，因此如果有人可以提出对DELETE FROM DatabaseB.dbo.Table1etc部分的改进建议，将不胜感激。（例如查询中的位置，它是否可以与CTE一起使用），对我来说目前似乎有些笨拙。