|
Posted by Dan Guzman on 03/31/06 05:53
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" <bletchley_scum@yahoo.co.uk> wrote in message
news:1143736747.132333.237170@t31g2000cwb.googlegroups.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
>
Navigation:
[Reply to this message]
|