CODEWITHSUNDEEP
sql-server
Weyland Yutani
Weyland Yutani

Reputation: 4960

Connecting to newly created database after previous connection error

My application is periodically connecting to MyDatabase and performing a query.

I need to handle the case where the MyDatabase database does not already exist and needs to be created. What I am doing currently is each time I first connect to the master database and run something like this:

SELECT * FROM sysdatabases WHERE NAME='MyDatabase'

to determine whether MyDatabase exists. If it doesn't I create it and then proceed with connecting to MyDatabase and performing the query.

Opening a separate connection to the master database and performing a query each time seems unnecessary (even though the connections are pooled). Why can't I just connect to MyDatabase straight away? 99% of the time it will succeed and I can execute the queries. The 1% of times it fails I can detect MyDatabase is missing and create it at that point right?

But when I try this I hit a problem. If I attempt to connect to MyDatabase and it doesn't exist I get a SqlException

Cannot open database MyDatabase requested by the login. The login failed.

Fine. Great. I can catch any SqlException and then go off to the master database to determine MyDatabase does not exist and create it.

But after creating it, when I now try to connect to MyDatabase I immediately get the same error:

Cannot open database MyDatabase requested by the login. The login failed.

It looks like it isn't trying to connect again and instead is returning a cached result. If I wait 10 seconds after creating the database before attempting to connect to it, the connection succeeds.

My question is, is this caching expected (I guess so) and more importantly is there a best practice for dealing with this situation? Is there perhaps a cache clearance or timeout setting in the SqlConnection API I can use? I could implement my own timeout delay I think but I would like to know there isn't a better method I am missing.

Upvotes: 2

Views: 379

Answers (1)

Robert Hegner
Robert Hegner

Reputation: 9396

I had exactly the same problem.

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

Upvotes: 5

Related Questions