CODEWITHSUNDEEP
c#sql-serverlocaldb
Kieren Johnstone
Kieren Johnstone

Reputation: 42003

SQL Server LocalDB simply failing to create DB

Here's my complete example:

var builder = new SqlConnectionStringBuilder();
builder.DataSource = "(localdb)\\v11.0";
builder.IntegratedSecurity = true;
builder.AttachDBFilename = "test.mdf";
builder.InitialCatalog = "test";
builder.ConnectTimeout = 60;

var connStr = builder.ToString();

using (var conn = new SqlConnection(connStr))
{
    conn.Open();
}

The exception:

System.Data.SqlClient.SqlException (0x80131904): Cannot attach the file 'test.mdf' as database 'test'.
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
ClientConnectionId:6ea99bb4-5703-4354-84f3-cfc5bcd8c045
Error Number:1832,State:1,Class:14

If I comment out the AttachDBFilename part, I get:

Cannot open database "test" requested by the login. The login failed.

If I comment out the InitialCatalog part, I get:

An attempt to attach an auto-named database for file test.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.

I'm running as a regular user, and get the precise same errors if I specify the data path using Path.GetTempPath(), i.e. I have access rights to the data location.

There is no existing test.mdf file, and no test database. I can change the file name and database name all I like, and get precisely the same messages.

If I use the sqllocaldb tool to stop and delete the v11.0 instance, the first time I then run the above code, I get:

Connection Timeout Expired. The timeout period elapsed while attempting to consume the pre-login handshake acknowledgement. This could be because the pre-login handshake failed or the server was unable to respond back in time. The duration spent while attempting to connect to this server was - [Pre-Login] initialization=3274; handshake=1;

(This occurs almost immediately, i.e. it does not use up the 60 second connection timeout I set in the connection string)

I see the same if I restart the computer then run the code.

After that, I get the above errors, the same every time.

I'm not sure what else to try!

Upvotes: 2

Views: 4040

Answers (1)

Kieren Johnstone
Kieren Johnstone

Reputation: 42003

Ok, here's the deal:

LocalDB does not in fact auto-create databases. It's just that most of the examples out there are Entity Framework ones, which in a lot of configurations will create missing databases.

EF is in fact pretty clever, and will detect AttachDBFilename values in the connection string to build a custom CREATE DATABASE command, along with correct data/log file paths in line with the specified value. It will try to use the connection string specified, and if that fails, it will adjust the connection string to connect to master DB and issue a CREATE DATABASE. In the end, that's what I did, too.

As an aside, I was in fact using EF in my main example. The code in my question is a good test case, but I was stuck for ages getting precisely the same exceptions I outlined here, even when using EF in an "auto-create-if-missing" configuration mode. It was because I had break-on-exception turned on, along Just My Code turned off, i.e. I was seeing EF internal exceptions.

Upvotes: 4

Related Questions