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

Question

Vengo da altre 2 domande e sto cercando di capire perché si verifica questa eccezione.

Seed Entity Framework -> SqlException: il ripristino della connessione produce uno stato diverso rispetto all'account di accesso iniziale. Il login non riesce. risultati-in-un-dif

Cosa significa "Reimpostazione della connessione"? System.Data.SqlClient.SqlException (0x80131904)

Questo codice riproduce l'eccezione.

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

Lo stacktrace completo è

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

Se cambio il primo Run(dbName...ad Run("master"...funziona benissimo. Quindi è correlato all'esecuzione ALTER DATABASEnel contesto dello stesso database

Cosa significa "Reimpostazione della connessione"? Perché la sessione è "in stato di interruzione". ? Devo evitare di eseguire istruzioni "ALTER" all'interno dello stesso database? Perché?

Answer 1

L'errore "La reimpostazione della connessione risulta in uno stato diverso rispetto all'accesso iniziale. L'accesso non riesce." è dovuto al riutilizzo di una connessione in pool dopo la modifica dello stato del database (modifica delle regole di confronto del database). Di seguito è riportato ciò che accade internamente che porta all'errore.

Quando viene eseguito questo codice:

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

ADO.NET cerca una connessione in pool esistente facendo corrispondere la stringa di connessione e il contesto di sicurezza. Nessuno è stato trovato perché la stringa di connessione della connessione in pool esistente (dalla CREATE DATABASEquery) è diversa ( masterdatabase invece di TESTDB). ADO.NET crea quindi una nuova connessione, che include la creazione di una connessione TCP / IP, l'autenticazione e l'inizializzazione della sessione di SQL Server. La ALTER DATABASEquery viene eseguita su questa nuova connessione. La connessione viene aggiunta al pool di connessioni quando viene eliminata (esce usingdall'ambito).

Quindi questo viene eseguito:

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

ADO.NET trova la TESTDBconnessione in pool esistente e la utilizza invece di creare un'istanza di una nuova connessione. Quando il PRINTcomando viene inviato a SQL Server, la richiesta TDS include un flag di reimpostazione della connessione per indicare che si tratta di una connessione in pool riutilizzata. Ciò fa sì che SQL Server venga richiamato internamente sp_reset_connectionper eseguire operazioni di pulizia come il rollback di transazioni non salvate, l'eliminazione di tabelle temporanee, la disconnessione, l'accesso e così via come descritto in dettaglio qui . Tuttavia, sp_reset_connectionnon è possibile ripristinare la connessione alle regole di confronto iniziali a causa della modifica delle regole di confronto del database, con conseguente errore di accesso.

Di seguito sono riportate alcune tecniche per evitare l'errore. Suggerisco l'opzione 3.

1. richiamare il SqlConnection.ClearAllPools()metodo statico dopo aver modificato le regole di confronto

2. Specificare masterinvece di TESTDBper il ALTER DATABASEcomando in modo che la connessione in pool "master" esistente venga riutilizzata invece di creare una nuova connessione. Il PRINTcomando successivo creerà quindi una nuova connessione per TESTDBpoiché non ne esiste una nel pool.

3. specificare le regole di confronto sull'istruzione CREATE DATABASEe rimuovere completamente il ALTER DATABASEcomando