"Cannot open database" if logging into SQL-server too fast [modified]
-
I have a service that needs to log into an SQL 2008 Express Server. The account of the service is the local system account. When the service starts, it checks if the SQL server is running. If not, it starts it, and waits for the service status to become "Running". When that has happened, the service logs in using a SqlConnection object. If the login is done too fast, the SQL server reports "Cannot open database XXX requested by login. The login failed.". This indicates that the security subsystem of the database server is not in a running/complete state. No, I can see now that the database has not yet become online. My current approach is to hammer the database 10 times with 1 second interval to wait for the connection to succeed. This is idiotic, and just begs for shit to happen. Does anybody know a better way? This is driving me insane... :~
-- Kein Mitleid Für Die Mehrheit
modified on Friday, October 16, 2009 5:15 AM
-
I have a service that needs to log into an SQL 2008 Express Server. The account of the service is the local system account. When the service starts, it checks if the SQL server is running. If not, it starts it, and waits for the service status to become "Running". When that has happened, the service logs in using a SqlConnection object. If the login is done too fast, the SQL server reports "Cannot open database XXX requested by login. The login failed.". This indicates that the security subsystem of the database server is not in a running/complete state. No, I can see now that the database has not yet become online. My current approach is to hammer the database 10 times with 1 second interval to wait for the connection to succeed. This is idiotic, and just begs for shit to happen. Does anybody know a better way? This is driving me insane... :~
-- Kein Mitleid Für Die Mehrheit
modified on Friday, October 16, 2009 5:15 AM
Can you not make your service depend on the SQL 2008 Express Server? Check out the Dependencies tab on the control panel/services/properties page.
-
Can you not make your service depend on the SQL 2008 Express Server? Check out the Dependencies tab on the control panel/services/properties page.
I could if I wanted to, but it doesn't help. The problem is that once the SQL server is reporting to be started to the SCM, it still hasn't made all databases online yet (only the master database is available). That work progresses after the SCM has been signalled that the service is running. I found the solution to be easier than I thought. All I have to do is to
SELECT COUNT(*) FROM sys.databases WHERE name=@database_name AND state_desc='online'
and see if it returns 0 or 1. Once the database manager reports the database as online, it is possible to use the database.
-- Kein Mitleid Für Die Mehrheit