D
DavidC
Having timeout difficulties with a routine.
The routine queries data using a order number as the
criteria for the data in an SQL database over a LAN, and
pastes the information into a spreadsheet.
The routine works fine with the workbook unprotected and
not shared.
When the workbook is shared, and protected the routine
works fine for the majority of the time, but there are two
criteria situations where the result of the query returns
over 100 records and the routine fails at the set rst
=cmd.execute(),on timeout. It appears that the failure
happens after 30s. The query being run is a stored
procedure on the database.
Does anyone have any thoughts on how to extend the timeout
value for the connection?
The code I am using is below:
Set cn = New ADODB.Connection
cn.Open "Provider=SQLOLEDB;Server=XXXXX;Database=YYYY;uid=h
hh;password=0000 "
Set cmd = New ADODB.Command
cmd.ActiveConnection = cn
cmd.CommandText = "ABC SAMPLE"
cmd.CommandType = adCmdStoredProc
cmd.Parameters.Refresh
cmd.Parameters(1).Value = ActiveSheet.Cells(1,1)
Set rst = cmd.Execute()
The routine queries data using a order number as the
criteria for the data in an SQL database over a LAN, and
pastes the information into a spreadsheet.
The routine works fine with the workbook unprotected and
not shared.
When the workbook is shared, and protected the routine
works fine for the majority of the time, but there are two
criteria situations where the result of the query returns
over 100 records and the routine fails at the set rst
=cmd.execute(),on timeout. It appears that the failure
happens after 30s. The query being run is a stored
procedure on the database.
Does anyone have any thoughts on how to extend the timeout
value for the connection?
The code I am using is below:
Set cn = New ADODB.Connection
cn.Open "Provider=SQLOLEDB;Server=XXXXX;Database=YYYY;uid=h
hh;password=0000 "
Set cmd = New ADODB.Command
cmd.ActiveConnection = cn
cmd.CommandText = "ABC SAMPLE"
cmd.CommandType = adCmdStoredProc
cmd.Parameters.Refresh
cmd.Parameters(1).Value = ActiveSheet.Cells(1,1)
Set rst = cmd.Execute()