System.Data.SqlClient.SqlException après CREATE / ALTER / PRINT

Question

Je viens de 2 autres questions et j'essaie de comprendre pourquoi cette exception se produit.

Entity Framework seed -> SqlException: la réinitialisation de la connexion entraîne un état différent de celui de la connexion initiale. La connexion échoue. résultats en un dif

Que signifie "Réinitialiser la connexion"? System.Data.SqlClient.SqlException (0x80131904)

Ce code reproduit l'exception.

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

Le stacktrace complet est

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

Si je change le premier Run(dbName...à Run("master"...il fonctionne très bien. C'est donc lié à l'exécution ALTER DATABASEdans le contexte de la même base de données

Que signifie "Réinitialiser la connexion"? Pourquoi la session est-elle "dans l'état de suppression". ? Dois-je éviter d'exécuter des instructions "ALTER" dans la même base de données? Pourquoi?

Answer 1

L'erreur "La réinitialisation de la connexion entraîne un état différent de celui de la connexion initiale. La connexion échoue." est due à une connexion mise en pool réutilisée après le changement d'état de la base de données (changement de classement de la base de données). Voici ce qui se passe en interne qui conduit à l'erreur.

Lorsque ce code s'exécute:

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

ADO.NET recherche une connexion mise en pool existante en faisant correspondre la chaîne de connexion et le contexte de sécurité. Aucun n'est trouvé car la chaîne de connexion de la connexion mise en pool existante (à partir de la CREATE DATABASErequête) est différente ( masterbase de données au lieu de TESTDB). ADO.NET crée ensuite une nouvelle connexion, qui comprend l'établissement d'une connexion TCP / IP, l'authentification et l'initialisation de la session SQL Server. La ALTER DATABASErequête est exécutée sur cette nouvelle connexion. La connexion est ajoutée au pool de connexions lorsqu'elle est supprimée (sort du usingchamp d'application).

Ensuite, ceci s'exécute:

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

ADO.NET trouve la TESTDBconnexion mise en pool existante et l'utilise au lieu d'instancier une nouvelle connexion. Lorsque la PRINTcommande est envoyée à SQL Server, la demande TDS inclut un indicateur de réinitialisation de connexion pour indiquer qu'il s'agit d'une connexion regroupée réutilisée. Cela oblige SQL Server à appeler en interne sp_reset_connectionpour effectuer des travaux de nettoyage tels que la restauration des transactions non validées, la suppression des tables temporaires, la déconnexion, la connexion, etc.) comme détaillé ici . Cependant, sp_reset_connectionimpossible de rétablir la connexion au classement initial en raison de la modification du classement de la base de données, entraînant l'échec de la connexion.

Voici quelques techniques pour éviter l'erreur. Je suggère l'option 3.

1. invoquer la SqlConnection.ClearAllPools()méthode statique après avoir modifié le classement

2. Spécifiez masterau lieu de TESTDBpour la ALTER DATABASEcommande afin que la connexion mise en pool «maître» existante soit réutilisée au lieu de créer une nouvelle connexion. La PRINTcommande suivante créera alors une nouvelle connexion pour TESTDBcar il n'en existe pas dans le pool.

3. spécifier le classement sur l' CREATE DATABASEinstruction et supprimer ALTER DATABASEentièrement la commande