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.Connectio n ' Connection Object

strConnection = "Provider='SQLO LEDB';Data Source='TEST\TE ST';Initial
Catalog=composi tes;User ID=sa;pwd=mypas sword"


SQLConn.Connect ionString = strConnection
SQLConn.Connect ionTimeout = 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@hotmail.com]

Re: Timeout Expired

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]

Re: Timeout Expired

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_toasti e" <bletchley_scum @yahoo.co.uk> wrote in message
news:1143736747 .132333.237170@ t31g2000cwb.goo glegroups.com.. .[color=blue]
> 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.Connectio n ' Connection Object
>
> strConnection = "Provider='SQLO LEDB';Data Source='TEST\TE ST';Initial
> Catalog=composi tes;User ID=sa;pwd=mypas sword"
>
>
> SQLConn.Connect ionString = strConnection
> SQLConn.Connect ionTimeout = 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
>[/color]

[cheesey_toastie]

Re: Timeout Expired

Wonderful.


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

Many thanks!