Can't immediately connect to newly-created SQL Server database

Question

I'm creating a database using SQL Server Management Objects.

I wrote the following method to generate a database:

public static void CreateClientDatabase(string serverName, string databaseName)
{
    using (var connection = new SqlConnection(GetClientSqlConnectionString(serverName, String.Empty)))
    {
        var server = new Server(new ServerConnection(connection));
        var clientDatabase = new Database(server, databaseName);

        clientDatabase.Create();
        server.ConnectionContext.Disconnect();
    }
}

Shortly thereafter, I call another method to execute a SQL script to generate tables, etc.:

public static void CreateClientDatabaseObjects(string createDatabaseObjectsScriptPath, string serverName, string databaseName)
{
    using (var connection = new SqlConnection(GetClientSqlConnectionString(serverName, databaseName)))
    {
        string createDatabaseObjectsScript = new FileInfo(createDatabaseObjectsScriptPath).OpenText().ReadToEnd();
        var server = new Server(new ServerConnection(connection));

        server.ConnectionContext.ExecuteNonQuery(createDatabaseObjectsScript);
        server.ConnectionContext.Disconnect();
    }
}

The statement server.ConnectionContext.ExecuteNonQuery(createDatabaseObjectsScript) throws a SqlException with the message Cannot open database "The database name" requested by the login. The login failed. Login failed for user 'the user'.

If I try stepping through the statements in the debugger, this exception never happens and the script executes fine.

My only guess is that the server needs some time to initialize the database before it can be opened. Is this accurate? Is there a way to tell if a database is ready other than trying and failing to connect?

Edit: I should mention that the database is definitely being created in all cases.

Edit 2: Prior to creating the database, I call the System.Data.Entity.Database.Exists method of EntityFramework.dll to check if the database already exists. If I remove that call, everything seems to work as expected. It's almost as if that call is caching the result and messing up the subsequent connections from seeing the new database (regardless of whether or not they use Entity Framework).

Edit 3: I replaced the EntityFramework's Database.Exists method with an SMO-based approach using Server.Databases.Contains(databaseName). I get the same issue in that the database cannot be opened immediately after creating. Again, if I don't check if a database exists prior to creation I can immediately open it after creating it but I'd like to be able to check for existence prior to creation so I don't try to create an existing database.

Both the EntityFramework's Database.Exists and SMO's Server.Databases.Contains both just execute SQL that looks for the database name in master.sys.databases. I don't understand why/how this is interfering with database connections.

Answer 1

A database that has just come online is not necessarily ready to accept connections. To identify when a database can accept connections, query the collation_name column of sys.databases or the Collation property of DATABASEPROPERTYEX. The database can accept connections when the database collation returns a non-null value.

Answer 2

In my case it turned out that the problem is not that the newly created database is not ready. But the problem was that SqlConnection.Open() apparently used a failed cached connection, which then just returned the same error again.

When I call the static SqlConnection.ClearAllPools() method before I try to open the newly created database, it works fine!

See also this question.