System.Data.SqlClient.SqlException sau CREATE / ALTER / PRINT

Question

Tôi đến từ 2 câu hỏi khác và đang cố gắng hiểu lý do tại sao ngoại lệ này xảy ra.

Hạt giống Entity Framework -> SqlException: Đặt lại kết nối dẫn đến trạng thái khác với lần đăng nhập ban đầu. Đăng nhập không thành công. results-in-a-dif

"Đặt lại kết nối" có nghĩa là gì? System.Data.SqlClient.SqlException (0x80131904)

Mã này tái tạo ngoại lệ.

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();
}

Hệ thống ngăn xếp đầy đủ là

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()
...

Nếu tôi thay đổi đầu tiên Run(dbName...thành Run("master"...nó chạy tốt. Vì vậy, nó liên quan đến việc chạy ALTER DATABASEtrong bối cảnh của cùng một cơ sở dữ liệu

"Đặt lại kết nối" có nghĩa là gì? Tại sao phiên "ở trạng thái tiêu diệt." ? Tôi có nên tránh chạy các câu lệnh "ALTER" bên trong cùng một cơ sở dữ liệu không? Tại sao?

Answer 1

Lỗi "Đặt lại kết nối dẫn đến trạng thái khác với lần đăng nhập ban đầu. Đăng nhập không thành công." là do một kết nối gộp được sử dụng lại sau khi thay đổi trạng thái cơ sở dữ liệu (thay đổi đối chiếu cơ sở dữ liệu). Dưới đây là những gì xảy ra bên trong dẫn đến lỗi.

Khi mã này chạy:

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

ADO.NET tìm kiếm một kết nối tổng hợp hiện có bằng cách khớp chuỗi kết nối và ngữ cảnh bảo mật. Không tìm thấy vì chuỗi kết nối của kết nối gộp hiện có (từ CREATE DATABASEtruy vấn) là khác nhau ( mastercơ sở dữ liệu thay vì TESTDB). ADO.NET sau đó tạo một kết nối mới, bao gồm thiết lập kết nối TCP / IP, xác thực và khởi tạo phiên SQL Server. Các ALTER DATABASEtruy vấn được chạy trên kết nối mới này. Kết nối được thêm vào nhóm kết nối khi nó bị loại bỏ (ra khỏi usingphạm vi).

Sau đó, điều này chạy:

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

ADO.NET tìm TESTDBkết nối gộp hiện có và sử dụng kết nối đó thay vì khởi tạo kết nối mới. Khi PRINTlệnh được gửi đến SQL Server, yêu cầu TDS bao gồm cờ kết nối đặt lại để cho biết đó là kết nối gộp được sử dụng lại. Điều này khiến SQL Server gọi nội bộ sp_reset_connectionđể thực hiện công việc dọn dẹp như khôi phục các giao dịch chưa cam kết, bỏ bảng tạm thời, đăng xuất, đăng nhập, v.v.) như được nêu chi tiết ở đây . Tuy nhiên, sp_reset_connectionkhông thể hoàn nguyên kết nối về đối chiếu ban đầu do thay đổi đối chiếu cơ sở dữ liệu, dẫn đến đăng nhập không thành công.

Dưới đây là một số kỹ thuật để tránh lỗi. Tôi đề xuất phương án 3.

1. gọi SqlConnection.ClearAllPools()phương thức tĩnh sau khi thay đổi đối chiếu

2. Chỉ định masterthay vì TESTDBcho ALTER DATABASElệnh để kết nối tổng hợp 'chính' hiện có được sử dụng lại thay vì tạo kết nối mới. Sau đó, PRINTlệnh tiếp theo sẽ tạo một kết nối mới TESTDBvì một kết nối không tồn tại trong nhóm.

3. chỉ định đối chiếu trên CREATE DATABASEcâu lệnh và loại bỏ ALTER DATABASEhoàn toàn lệnh