CREATE/ALTER/PRINT之后出现System.Data.SqlClient.SqlException [英] System.Data.SqlClient.SqlException after CREATE/ALTER/PRINT

问题描述

我来自另外两个问题，并试图了解为什么会发生此异常.

I'm coming from 2 other questions, and am trying to understand why this exception happens.

实体框架种子->SqlException:重置连接将导致状态不同于初始登录.登录失败.差异化结果

重置连接"有什么作用;意思是?System.Data.SqlClient.SqlException(0x80131904)

此代码再现了异常.

string dbName = "TESTDB";
Run("master", $"CREATE DATABASE [{dbName}]");
Run(dbName, $"ALTER DATABASE [{dbName}] COLLATE Latin1_General_100_CI_AS");
Run(dbName, "PRINT 'HELLO'");

void Run(string catalog, string script)
{
    var cnxStr = new SqlConnectionStringBuilder
    {
        DataSource = serverAndInstance,
        UserID = user,
        Password = password,
        InitialCatalog = catalog
    };

    using var cn = new SqlConnection(cnxStr.ToString());
    using var cm = cn.CreateCommand();
    cn.Open();
    cm.CommandText = script;
    cm.ExecuteNonQuery();
}

完整的堆栈跟踪为

Unhandled Exception: System.Data.SqlClient.SqlException: Resetting the connection results in a different state than the initial login. The login fails.
Login failed for user 'user'.
Cannot continue the execution because the session is in the kill state.
A severe error occurred on the current command.  The results, if any, should be discarded.
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async, Int32 timeout, Boolean asyncWrite)
   at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)
   at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
...

如果我将第一个 Run(dbName ... )更改为 Run("master" ... )，它将运行正常.因此，这与在同一数据库的上下文中运行 ALTER DATABASE

If I change the first Run(dbName... to Run("master"... it runs fine. So it's related to running ALTER DATABASE in the context of the same database

重置连接"有什么作用?意思是?为什么会话处于杀死状态".?我应该避免运行"ALTER"吗?同一数据库内的语句?为什么?

What does "Resetting the connection" mean? Why is the session "in the kill state." ? Should I avoid running "ALTER" statements inside the same database? Why?

推荐答案

错误重置连接所导致的状态与初始登录不同.登录失败.是由于池化连接在数据库状态更改(数据库排序规则更改)之后被重用.以下是导致错误的内部原因.

The error "Resetting the connection results in a different state than the initial login. The login fails." is due to a pooled connection being reused after the database state change (database collation change). Below is what happens internally that leads to the error.

此代码运行时:

Run(dbName, $"ALTER DATABASE [{dbName}] COLLATE Latin1_General_100_CI_AS");

ADO.NET通过匹配连接字符串和安全上下文来查找现有的池连接.未找到任何内容，因为现有池连接的连接字符串(来自 CREATE DATABASE 查询)不同( master 数据库而不是 TESTDB ).ADO.NET然后创建一个新连接，其中包括建立TCP/IP连接，身份验证和SQL Server会话初始化. ALTER DATABASE 查询在此新连接上运行.连接处置后(从 using 作用域中删除)，该连接即被添加到连接池中.

ADO.NET looks for an existing pooled connection by matching the connection string and security context. None is found because the connection string of the existing pooled connection (from the CREATE DATABASE query) is different (master database instead of TESTDB). ADO.NET then creates a new connection, which includes establishing a TCP/IP connection, authentication, and SQL Server session initialization. The ALTER DATABASE query is run on this new connection. The connection is added to the connection pool when it's disposed (goes out of the using scope).

然后运行:

Run(dbName, "PRINT 'HELLO'");

ADO.NET查找现有的合并的 TESTDB 连接，并使用该连接而不是实例化新连接.当 PRINT 命令发送到SQL Server时，TDS请求将包含一个重置连接标志，以指示它是重用的池连接.这导致SQL Server在内部调用 sp_reset_connection 进行清理工作，例如回滚未提交的事务，删除临时表，注销，登录等)，详细说明如

ADO.NET finds the existing pooled TESTDB connection and uses that instead of instantiating a new connection. When the PRINT command is sent to SQL Server, the TDS request includes a reset connection flag to indicate it's a reused pooled connection. This causes SQL Server to internally invoke sp_reset_connection to do cleanup work like rollback uncommitted transactions, dropping temp tables, logout, login, etc.) as detailed here. However, sp_reset_connection cannot revert the connection back to the initial collation due to the database collation change, resulting in the login failure.

下面是一些避免错误的技术.我建议选择3.

Below are some techniques to avoid the error. I suggest option 3.

1. 更改排序规则后调用静态 SqlConnection.ClearAllPools()方法

为 ALTER DATABASE 命令指定 master 而不是 TESTDB ，以便重新使用现有的"master"池连接创建一个新的连接.由于池中不存在一个连接，因此随后的 PRINT 命令将为 TESTDB 创建一个新连接.

Specify master instead of TESTDB for the ALTER DATABASE command so that the existing 'master' pooled connection is reused instead of creating a new connection. The subsequent PRINT command will then create a new connection for TESTDB since one does not exist in the pool.

在 CREATE DATABASE 语句上指定排序规则，并完全删除 ALTER DATABASE 命令

specify the collation on the CREATE DATABASE statement and remove the ALTER DATABASE command entirely

这篇关于CREATE/ALTER/PRINT之后出现System.Data.SqlClient.SqlException的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！