Timing-related issue when connecting to a newly created database

[vtchris peterson]

Using SMO with SQL Express 2005, I'm experiencing some frustrating behavior. I'm using SMO Server and Database classes to script my database using Database.ExecuteNonQuery. The database objects are getting scripted correctly, but I'm having problems re-connecting to the database (using SqlConnection.Open). I get a SqlException ("System.Data.SqlClient.SqlException: Cannot open database \"MyDatabase\" requested by the login. The login failed.\r\nLogin failed for user 'MyDomain\\MyUser'.\r\n at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject)\r\n at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)\r\n at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)\r\n at System.Data.SqlClient.SqlConnection.Open()\r\n at MyCodePath\\DataAccessFactory.cs:line 57") The problem "goes away" if I just wait before trying to create the second connection e.g. if I use a breakpoint and step through the code it works fine. I am closing my previous connection (the one where I scripted the database) and everything is sequential on a single (the UI) thread. I'd hate to have to code some delay or retry logic for something that seems pretty standard.

[Migounette]

It's normal, you may use a connection pool in order to avoid problems of connection. myConnection.ConnectionString = "Persist Security Info=False;Integrated Security=SSPI;database=MyDatabase;server=MyServer;Connect Timeout=30"; The value is expressed in seconds: Connect Timeout=30

[vtchris peterson]

That's kind of annoying. I've noticed these types of subtleties even when using standard MS tools like SSMS. My first reaction when something fails is to change some variable of the experiment before trying again, but with SQL Server, a lot of times, you just need to act like a crazy person and try the EXACT same thing expecting different results. Thanks for your tip, I had managed to workaround the issue, but I might implement this suggestion as well.