By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,265 Members | 1,333 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,265 IT Pros & Developers. It's quick & easy.

Timeout Expired

P: n/a
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

Mar 30 '06 #1
Share this Question
Share on Google+
3 Replies


P: n/a
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

Mar 30 '06 #2

P: n/a
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.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"cheesey_toastie" <bl************@yahoo.co.uk> wrote in message
news:11**********************@t31g2000cwb.googlegr oups.com...
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

Mar 31 '06 #3

P: n/a
Wonderful.
I stared at that for ages.... !!

Many thanks!

Mar 31 '06 #4

This discussion thread is closed

Replies have been disabled for this discussion.