Can't connect using SqlClient after creating new database.

Hi,

I am writing a c# console application that allows you to update a database to a specified version. It does this by running a set of scripts that are listed in an xml manifest.

The application also provides an option to create the database and then run scripts to update it.

I have a method that reads blocks of SQL from each script and runs them using the ADO.NET classes in the SqlClient namespace.

I am finding that after the application runs the script to create the database, the first time the application tries to connect to that new database the connection attempt fails. The error I receive is :-

Cannot open database {dbname} requested by the login. The login failed.
Login failed for user {user}

I find that if I set the thread to sleep for 3 seconds after the database is created, I can then connect without the error occurring, but this is a bit of a nasty hack. I'd prefer to find a better solution.

This is occurring against a local SQL Server Express instance.

My machine is reasonable. 3.4 Ghz. 2Gb RAM, so I wouldn't have thought there's any problems in that regard.

Does anyone have any pointers, firstly as to why this is occurring, and secondly, how I might solve this problem in a nicer way?

Thanks,

David.

Hi David,

This is strange behaviour. Are you using Windows auth or SQL auth? Also, are there any errors written to your error log?

Thanks,
Il-Sung.

|||

Hi ll-Sung,

Sorry it has taken so long for me to get back to you about this.

My SQL Express is set up for Windows Auth only, and I am connecting as such.

The exception I receive when I try to connect after creating the database is :-

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

Login failed for user 'Domain\UserName'.

Both the SQL Log and the Windows Event Log display a series of informational messages that occur during the creation of the database. Things like :-

Setting database option ANSI_PADDING to OFF for database DatabaseName

Also in the logs, I receive a Failure Audit error message. This occurs before any of the informational messages occur. It looks like :-

Login failed for user 'Domain\UserName'. [CLIENT: <local machine>]

However this login failure message appears whether or not the database creation succeeds or not (ie the process succeeds when I have my Sleep command in).

So overall, I guess there is no error in any of the logs indicating why this is occurring.

The SQL I am using to create the database is a script that I made simply by scripting off the db in Management Studio. I did not muck around with this at all except to put placeholders in for the database name.

Any further help you can provide for this would be much appreciated.

Thanks,

David.