ADO Connection.execute ...Times Out 2

[Monkeygumbo]

Hi,

Here's my code:

strSql = "Execute dbo.spStoredProc '123456'"
objConnection.Execute strSql

This returns a timeout error while executing. The server is Sql Server. When I run the Sql string in Query Analyzer, it runs fine but takes 6 minutes.

Is there a way to increase the timeout variable on an .execute method?

MG

 

[Transcend]

You can increase the timeout on your connection object,
i'm pretty sure for example if you have say conn defined as your connection object you can set

conn.timeout = 120

where 120 is 120 seconds

you can set this to whatever value you like, i'm not sure if there is a maximum value but this should get you started.

Transcend

 

[vb5prgrmr]

I believe...
objConnection.CommandTimeout = 0 'for indefinite amount
objConnection.CommandTimeout = 360 'for 6 minutes

 

[Monkeygumbo]

My connection objection doesn't have that property.....

 

[Transcend]

Ah,

is it commandtimeout? My apologies .. 6am might get some sleep.

Transcend

 

[Monkeygumbo]

I believe that this property is set perform a connection is open and determines the timeout time when establishing a connection. It doesn't appear to affect .execute

 

[Monkeygumbo]

excuse me....before a connection is open

 

[Transcend]

No if you set this property on your connection object it affects each query you execute using that connection object.

Trust me i did this not so long ago because I had some long running queries on some searches.

Transcend

 

[vb5prgrmr]

From help

CommandTimeout Property


Indicates how long to wait while executing a command before terminating the attempt and generating an error.

Settings and Return Values

Sets or returns a Long value that indicates, in seconds, how long to wait for a command to execute. Default is 30.

Remarks

Use the CommandTimeout property on a Connection object or Command object to allow the cancellation of an Execute method call, due to delays from network traffic or heavy server use. If the interval set in the CommandTimeout property elapses before the command completes execution, an error occurs and ADO cancels the command. If you set the property to zero, ADO will wait indefinitely until the execution is complete. Make sure the provider and data source to which you are writing code supports the CommandTimeout functionality.

The CommandTimeout setting on a Connection object has no effect on the CommandTimeout setting on a Command object on the same Connection; that is, the Command object’s CommandTimeout property does not inherit the value of the Connection object’s CommandTimeout value.

On a Connection object, the CommandTimeout property remains read/write after the Connection is opened.

 

[Monkeygumbo]

Thanks guys!

 

[kel1981b]

'When I run the Sql string in Query Analyzer, it runs fine but takes 6 minutes'.
6 minutes?!! Are you trying to retrieve million records? Why it works so slow? Seems to me you need to improve yours storeproc performance before change timeout of connection/command execution. By the way in most shops it's DBA's responsibility to define timeout not programmer's.

 

[Transcend]

I don't know about MonkeyGumbo but the only time
my queries were running over 5 minutes actually
was because I was retrieving 3 million records.

However in my app I think if the query was returning this many records i made the user refine their search

Transcend

 

[Transcend]

Also where I work it's the programmer's job to
change the timeout in their app if they so desire.

Transcend

 

[Monkeygumbo]

I work for a large insurance firm. I've seen some of their stored procedures run over hour. I'm just about to graduate and learned everything I know on this system. I didn't question the procedure's efficency. Some of their DTS packages move over a million records... In the scheme of things, a 5 min. wait isn't all that bad.