"SqlException (0x80131904): Cannot open database" when trying to drop DB

-1

I am receiving the exception bellow when trying to drop a temporary database created by one of our integration tests. Can someone please help me to understand the exact meaning of it.

Am I correct in assuming that the mentioned database does not exist any more at the time it tries to execute the following code? The exception is getting caused by command.ExecuteNonQuery();

Interesstingly this exception only occurs when running the tests on our TFS instance and not locally. Thanks in advance.

private static void DropDatabase(IDbConnection connection)
{
    var databaseName = GetDatabaseName(connection.ConnectionString);
    var str = $@"USE master;
                 ALTER DATABASE [{databaseName}] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
                 DROP DATABASE [{databaseName}];";

    try
    {
        using (DbCommand command = new SqlConnection(connection.ConnectionString).CreateCommand())
        {
            command.CommandText = str;
            command.CommandType = CommandType.Text;
            command.Connection.Open();
            command.ExecuteNonQuery();
            command.Connection.Close();
        }
    }
    catch (Exception ex)
    {
        Console.WriteLine(ex);
    }
}

System.Data.SqlClient.SqlException (0x80131904): Cannot open database "MyTestDb_8647C" requested by the login. The login failed.
Login failed for user 'MYDOMAIN\MyServiceUser'.
    at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, DbConnectionPool pool, String accessToken, Boolean applyTransientFaultHandling, SqlAuthenticationProviderManager sqlAuthProviderManager)
    at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions)
    at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnectionPool pool, DbConnection owningObject, DbConnectionOptions options, DbConnectionPoolKey poolKey, DbConnectionOptions userOptions)
    at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
    at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
    at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection)
    at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection)
    at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)
    at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
    at System.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource`1 retry)
    at System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry)
    at System.Data.SqlClient.SqlConnection.Open()
    at [...].Testing.DatabaseTestBase.DropDatabase(IDbConnection connection) in D:\[...]\DatabaseTestBase.cs:line 37
c#
sql-server
unit-testing
exception

1 Answer

0

Either the default database for your login no longer exists or the connection string is attempting to connect to a database that doesn't exist. In your connection string set the Initial Catalog property to master. You can then remove the USE MASTER statement. Also, make sure that the default database for your login is set to an existing database. Judging by your code, you'll probably want to use master for this as well.

answered on Stack Overflow Oct 18, 2018 by userfl89

User contributions licensed under CC BY-SA 3.0