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.

Grayish

Is the message really giving your a database name of "The database name" and a user of "the user"? That could be a problem. – Crymotherapy

The method doesn't really tell us much in regards to how the database is being created..do you have a script or code that you can post showing the Create Statement for the DB..?? – Bluejacket

@shanabus: No, I just changed them to not expose real database or login names. – Grayish

@DJKRAZE: I'm using the Database.Create of SQL Server Management Objects. I didn't write my own CREATE DATABASE SQL statement. – Grayish

aren't you reading something here in this location createDatabaseObjectsScriptPath within a file..?? – Bluejacket

@DJKRAZE: Yeah, it's just SQL statements to generate tables, etc. The connection fails to open the database so it doesn't even get as far as executing the script. – Grayish

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.

Acescent

This answer is correct. Documented at msdn.microsoft.com/en-us/library/ms186823.aspx. – Grayish

Unfortunately this check only works for DBs with auto_close disabled. Many default configurations for 2008 / 2012 express set auto_close to enabled, and this check keeps failing forever (although you can connect, and then the Collation property becomes non-NULL) – Dkl

Responding to myself: apparently one cannot connect to a newly created database but options can be set via ALTER DATABASE. Thus create the database, set AUTO_CLOSE OFF, check for the collation value, and then set AUTO_CLOSE ON again (if this is what you want - please consider sqlmag.com/blog/… and blogs.msdn.com/b/sqlsakthi/archive/2011/05/24/…) – Dkl

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.

Choiseul

Answer 3

The database name" requested by the login. The login failed. Login failed for user 'the user'

One thing to note here all is that the error message is surrounding security. How are you attempting to logon to the new database (i.e. what type of security is the connection string using for the new database connection)? If you are using SQL authentication you will need to issue sp_adduser to grant the login access to the database.

http://msdn.microsoft.com/en-us/library/ms181422.aspx

Maryettamaryjane

Answer 4

You have to approach the database creation a little bit different. The code below handles the database creation correctly. Try it ;)

private void CreateDatabase(string databaseName)
{

    Microsoft.SqlServer.Management.Smo.Server sqlServer = 
            new Microsoft.SqlServer.Management.Smo.Server(_connection);

    Database createdDatabase = new Database();
    createdDatabase.Name = databaseName;
    createdDatabase.Parent = sqlServer;
    createdDatabase.Create();

}

In the code below, the _connection is a

Microsoft.SqlServer.Management.Smo.ServerConnection

object by the way.

[Edit] Modified the code so the exception is not swallowed anymore.

Lemire

This is pretty much the same code that I already have except you're swallowing any potential exceptions. My create logic already works. I'm not experiencing any problems with the initial creation. – Grayish

-1; Fundamentally the same as the OPs code and silently swallows any exceptions. – Thunderstorm

The drawbacks are: 1. Forward compatibility - will SMO version X work well with SQL Server version (X+1) or with all future versions of Azure SQL? Of course, TSQL CREATE DATABASE will. 2. Learning curve - well known SQL DDL statements are replaced with not-well-known SMO-specific equivalents. 3. A new dependency is added (hopefully it's a Nuget package now). Which may have its own bugs etc. 4. Security. Not always it WILL be a problem, but it MAY be: it's easy to imagine that some SMO operations may require more permissions comparing to plain TSQL. – Pygidium