sql server connect: ADODB Connection Open: Intermittent Timeout Expired Errors -- sql server connect

ADODB Connection Open: Intermittent Timeout Expired Errors

Brian Kudera
12/7/2006 8:00:00 AM

sql server connect:

Hello all-

Connecting to a SQL Server 2005 Express w/Advanced Services database using
ADODB in vbscript. The database is on the local server and am using shared
memory. The server has dual processors and 3GB Ram, but SQL Server Express is
limited to 1 processor and 1 GB of ram.

Every once in a great while I receive timeout expired errors while trying to
connect to the database. When it works, it connects in less than a
millisecond. Therefore I believe that increasing the timeout period in the
connection string rather than using its default of 30 seconds is not going to
fix this problem.

Const CONNECTION_STRING = "Provider=sqloledb;Data Source=INGEN;Initial
Catalog=Ingen;Integrated Security=SSPI;"

Dim dbConnection : Set dbConnection = CreateObject("ADODB.Connection")

Call dbConnection.Open(CONNECTION_STRING)

Microsoft OLE DB Provider for SQL Server error '80040e31'
Timeout expired

Any suggestions?

Re: ADODB Connection Open: Intermittent Timeout Expired Errors

Roger Wolter[MSFT]
12/7/2006 8:58:09 AM

Go ahead and try the longer timeout. There may be other things happening on
your system that occasionally cause a hesitation. One of the more common
issues is that SQL Express databases have the autoclose option set by
default. This means that when no users are logged into a database, SQL
Server shuts it down to safe resources. The first user to login after the
database shuts down waits for the database to start up before the login is
complete so that may be what is causing the timeout. Generally starting up
a database takes only a few seconds but if the system is busy doing other
things it may take a while for SQL Server to obtain the resources it needs.
You can check this by using ALTER DATABASE to turn off the autoclose option
on your main databases.

--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm

[quoted text, click to view]