query timeout with ado?

[Thomas]

I've been searching MSDN for a while, but not having a lot of luck.

I'm occasionally getting timeout errors when running queries on my SQL
server database through ADO (called from VB DLLs and from VBScript in ASP
pages).

Can I set the querytimeout property through the ConnectString or through a
Recordset.Open method?

Most of the code I inherited uses Recordset.Open SQLStatement, ConnectString
semantics instead of using a explicit Connection object. I would prefer not
rewriting everything to get a timeout property!

Any ideas appreciated.

[John]

Thomas,

You are close. Not the ConnectionString but the ConnectionTimeout property
of the connection object.

Set cnn1 = New ADODB.Connection
cnn1.ConnectionString = "driver={SQL Server};" & _
"server=srv;uid=sa;pwd=pwd;database=Pubs"
cnn1.ConnectionTimeout = 30
cnn1.Open

--John

"Thomas" <thomas3617@spamcop.net> wrote in message
news:3cb1cf1d@10.1.10.29...
> I've been searching MSDN for a while, but not having a lot of luck.
>
> I'm occasionally getting timeout errors when running queries on my SQL
> server database through ADO (called from VB DLLs and from VBScript in ASP
> pages).
>
> Can I set the querytimeout property through the ConnectString or through a
> Recordset.Open method?
>
> Most of the code I inherited uses Recordset.Open SQLStatement,
ConnectString
> semantics instead of using a explicit Connection object. I would prefer
not
> rewriting everything to get a timeout property!
>
> Any ideas appreciated.
>
>
>
>

[Josh]

That will help you if you are getting timeouts on connecting to the database.
However, you still can get timeouts when opening recordsets or using the
execute method from the connection object. The way to get around this is
to use commands.

This example will allow you to open a recordset and avoid timeouts.

Set cnn1 = New ADODB.Connection
cnn1.ConnectionString = "driver={SQL Server};" & _
"server=srv;uid=sa;pwd=pwd;database=Pubs"
cnn1.ConnectionTimeout = 30
cnn1.Open
Set cmd1 = new adodb.command
cmd1.activeconnection = cnn1
cmd1.commandtimeout = 0
cmd1.commandtype = adCmdText
cmd1.commandtext = "SELECT * FROM Authors"
set rst1 = new adodb.recordset
rst1.open cmd1, ,[Cursortype], [Locktype]

You can also use the execute method from the command.

"John" <jp@programmer.net> wrote:
>Thomas,
>
>You are close. Not the ConnectionString but the ConnectionTimeout property
>of the connection object.
>
> Set cnn1 = New ADODB.Connection
> cnn1.ConnectionString = "driver={SQL Server};" & _
> "server=srv;uid=sa;pwd=pwd;database=Pubs"
> cnn1.ConnectionTimeout = 30
> cnn1.Open
>
>--John
>
>"Thomas" <thomas3617@spamcop.net> wrote in message
>news:3cb1cf1d@10.1.10.29...
>> I've been searching MSDN for a while, but not having a lot of luck.
>>
>> I'm occasionally getting timeout errors when running queries on my SQL
>> server database through ADO (called from VB DLLs and from VBScript in
ASP
>> pages).
>>
>> Can I set the querytimeout property through the ConnectString or through
a
>> Recordset.Open method?
>>
>> Most of the code I inherited uses Recordset.Open SQLStatement,
>ConnectString
>> semantics instead of using a explicit Connection object. I would prefer
>not
>> rewriting everything to get a timeout property!
>>
>> Any ideas appreciated.
>>
>>
>>
>>
>
>