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

Question

Saya datang dari 2 pertanyaan lain, dan saya mencoba memahami mengapa pengecualian ini terjadi.

Benih Kerangka Kerja Entitas -> SqlException: Menyetel ulang koneksi menghasilkan status yang berbeda dari login awal. Login gagal. hasil-dalam-a-dif

Apa yang dimaksud dengan "Mereset koneksi"? System.Data.SqlClient.SqlException (0x80131904)

Kode ini mereproduksi pengecualian.

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

Stacktrace lengkap adalah

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

Jika saya mengubah pertama Run(dbName...untuk Run("master"...itu berjalan dengan baik. Jadi ini terkait dengan menjalankan ALTER DATABASEdalam konteks database yang sama

Apa yang dimaksud dengan "Mereset koneksi"? Mengapa sesi "dalam kondisi membunuh". ? Haruskah saya menghindari menjalankan pernyataan "ALTER" di dalam database yang sama? Mengapa?

Answer 1

Kesalahan "Menyetel ulang koneksi menghasilkan status yang berbeda dari login awal. Login gagal." disebabkan oleh koneksi gabungan yang digunakan kembali setelah status database berubah (database collation change). Di bawah ini adalah apa yang terjadi secara internal yang mengarah ke kesalahan.

Saat kode ini berjalan:

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

ADO.NET mencari koneksi gabungan yang ada dengan mencocokkan string koneksi dan konteks keamanan. Tidak ada yang ditemukan karena string koneksi dari koneksi gabungan yang ada (dari CREATE DATABASEkueri) berbeda ( masterdatabase bukan TESTDB). ADO.NET kemudian membuat sambungan baru, yang mencakup pembuatan sambungan TCP / IP, otentikasi, dan inisialisasi sesi SQL Server. The ALTER DATABASEquery dijalankan pada sambungan baru ini. Koneksi ditambahkan ke kumpulan koneksi saat dibuang (keluar dari usingruang lingkup).

Kemudian ini berjalan:

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

ADO.NET menemukan TESTDBkoneksi gabungan yang ada dan menggunakan itu daripada membuat contoh koneksi baru. Saat PRINTperintah dikirim ke SQL Server, permintaan TDS menyertakan tanda reset koneksi untuk menunjukkan bahwa itu adalah koneksi gabungan yang digunakan kembali. Hal ini menyebabkan SQL Server secara internal memanggil sp_reset_connectionuntuk melakukan pekerjaan pembersihan seperti rollback transaksi yang tidak terikat, menghapus tabel temporer, logout, login, dll.) Seperti yang dijelaskan di sini . Namun, sp_reset_connectiontidak dapat mengembalikan koneksi kembali ke pemeriksaan awal karena perubahan pemeriksaan database, mengakibatkan kegagalan login.

Berikut adalah beberapa teknik untuk menghindari kesalahan tersebut. Saya menyarankan opsi 3.

1. memanggil SqlConnection.ClearAllPools()metode statis setelah mengubah pemeriksaan

2. Tentukan masteralih-alih TESTDBuntuk ALTER DATABASEperintah tersebut sehingga koneksi gabungan 'master' yang ada digunakan kembali alih-alih membuat koneksi baru. PRINTPerintah selanjutnya akan membuat koneksi baru TESTDBkarena tidak ada di pool.

3. tentukan pemeriksaan pada CREATE DATABASEpernyataan tersebut dan hapus ALTER DATABASEperintah sepenuhnya