Timeout Expired

[cheesey_toastie]

Hi,

I'm connecting to a SQL server (7.0) from Excel and VBA.

I've checked the VBA and don't think I've got this wrong but I keep
getting a Timeout Expired error message. Is there a setting in SQL
that I am not aware of that I need to set? Could it be the connection
string I use?


The connection string is

Sub SQLConnection()

Dim strConnection As String
Dim Catalogue As String

Dim sSQL As String
Set SQLConn = New ADODB.Connection ' Connection Object

strConnection = "Provider='SQLOLEDB';Data Source='TEST\TEST';Initial
Catalog=composites;User ID=sa;pwd=mypassword"


SQLConn.ConnectionString = strConnection
SQLConn.ConnectionTimeout = 0 ' wait indefinately?
SQLConn.Open


End Sub


The point at which I get the time out is when I run a large update
query

SQLConn.Execute CreateSQL(i + 1)

CreateSQL is a function that updates a table with a million or so
records and about 30 inner joins (takes approx 40 seconds to execute
through Query Analyser).


Any Help much appreciated!

ct

 

[jennifer1970]

If you set your connection timeout to zero, I don't think that means to
wait indefinitely. Plus, that just sets how long to wait before you
make a connection to your database. What you want to set is the
CommandTimeout. The default wait is 30 seconds if you don't set it.
The CommandTimeout will say how long to wait for your update to
complete.

HTH,
Jennifer

 

[Dan Guzman]

I believe you want to set the connection CommandTimeout property to zero.
That specifies the max time a query can run when you invoke the connection
Execute method.

Separately, it's a bad practice to use 'sa' for routine application access.
Use a minimally privileged account.

 

[cheesey_toastie]

Wonderful.


I stared at that for ages.... !!

Many thanks!