[Microsoft][ODBC SQL Server Driver] Timeout expired

[Michael V]

Hi

I have two stored procedures in an SQL database that I
execute from my Access application. These two procedures
work fine when executed from the sql server. When I run
the procedure from Access though, only the first one
executes OK and the second one comes up with:
[Microsoft][ODBC SQL Server Driver] Timeout expired

I think it's to do with the timeout setting or something
as the second one takes about 10 seconds more to complete
than the first one.

Both procedures are being called and executed by the same
function in my form.

Can anyone help please.

thanks Michael V

 

[Douglas J. Steele]

How are you running the SPs? Assuming it's through a pass-through query, one
of the properties of the query is ODBC Timeout.

The value there is an Integer representing the number of seconds to wait
before a timeout error occurs. When the ODBCTimeout property is set to -1,
the timeout defaults to the current setting of the QueryTimeout property of
the Connection or Database object that contains the QueryDef. When the
ODBCTimeout property is set to 0, no timeout error occurs.

 

[Michael V]

Hi Doug

I execute the stored procedure using this function:

Private Function runStoredProc(storedProc As String)
Dim Conn As ADODB.Connection
Dim Cmd As ADODB.Command
Dim Connect As String

sConnect = "driver={sql server}; server=xxx.xx.x.xxx;
Database=mydb; UID=userid; PWD=pwd;"

Set Conn = New ADODB.Connection
Conn.ConnectionString = sConnect
Conn.Open sConnect
Set Cmd = New ADODB.Command
Cmd.ActiveConnection = Conn
Cmd.CommandText = "Exec " & storedProc
Cmd.Execute
End Function

how do I set the ODBC timeout property to 0?

Michael V

-----Original Message-----
How are you running the SPs? Assuming it's through a pass-through query, one
of the properties of the query is ODBC Timeout.

The value there is an Integer representing the number of seconds to wait
before a timeout error occurs. When the ODBCTimeout property is set to -1,
the timeout defaults to the current setting of the QueryTimeout property of
the Connection or Database object that contains the QueryDef. When the
ODBCTimeout property is set to 0, no timeout error occurs.

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)

Hi

I have two stored procedures in an SQL database that I
execute from my Access application. These two procedures
work fine when executed from the sql server. When I run
the procedure from Access though, only the first one
executes OK and the second one comes up with:
[Microsoft][ODBC SQL Server Driver] Timeout expired

I think it's to do with the timeout setting or something
as the second one takes about 10 seconds more to complete
than the first one.

Both procedures are being called and executed by the same
function in my form.

Can anyone help please.

thanks Michael V

.

 

[Douglas J. Steele]

Set Cmd = New ADODB.Command
Cmd.ActiveConnection = Conn
Cmd.CommandText = "Exec " & storedProc
Cmd.CommandTimeout = 0 ' or whatever you want
Cmd.Execute

BTW, you don't need to set the ConnectionString property and use the
Connection string to open the connection. One or the other is sufficient.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi Doug

I execute the stored procedure using this function:

Private Function runStoredProc(storedProc As String)
Dim Conn As ADODB.Connection
Dim Cmd As ADODB.Command
Dim Connect As String

sConnect = "driver={sql server}; server=xxx.xx.x.xxx;
Database=mydb; UID=userid; PWD=pwd;"

Set Conn = New ADODB.Connection
Conn.ConnectionString = sConnect
Conn.Open sConnect
Set Cmd = New ADODB.Command
Cmd.ActiveConnection = Conn
Cmd.CommandText = "Exec " & storedProc
Cmd.Execute
End Function

how do I set the ODBC timeout property to 0?

Michael V

-----Original Message-----
How are you running the SPs? Assuming it's through a pass-through query, one
of the properties of the query is ODBC Timeout.

The value there is an Integer representing the number of seconds to wait
before a timeout error occurs. When the ODBCTimeout property is set to -1,
the timeout defaults to the current setting of the QueryTimeout property of
the Connection or Database object that contains the QueryDef. When the
ODBCTimeout property is set to 0, no timeout error occurs.

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)

Hi

I have two stored procedures in an SQL database that I
execute from my Access application. These two procedures
work fine when executed from the sql server. When I run
the procedure from Access though, only the first one
executes OK and the second one comes up with:
[Microsoft][ODBC SQL Server Driver] Timeout expired

I think it's to do with the timeout setting or something
as the second one takes about 10 seconds more to complete
than the first one.

Both procedures are being called and executed by the same
function in my form.

Can anyone help please.

thanks Michael V

.

 

[Michael V]

Thanks Doug

-----Original Message-----
Set Cmd = New ADODB.Command
Cmd.ActiveConnection = Conn
Cmd.CommandText = "Exec " & storedProc
Cmd.CommandTimeout = 0 ' or whatever you want
Cmd.Execute

BTW, you don't need to set the ConnectionString property and use the
Connection string to open the connection. One or the other is sufficient.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi Doug

I execute the stored procedure using this function:

Private Function runStoredProc(storedProc As String)
Dim Conn As ADODB.Connection
Dim Cmd As ADODB.Command
Dim Connect As String

sConnect = "driver={sql server}; server=xxx.xx.x.xxx;
Database=mydb; UID=userid; PWD=pwd;"

Set Conn = New ADODB.Connection
Conn.ConnectionString = sConnect
Conn.Open sConnect
Set Cmd = New ADODB.Command
Cmd.ActiveConnection = Conn
Cmd.CommandText = "Exec " & storedProc
Cmd.Execute
End Function

how do I set the ODBC timeout property to 0?

Michael V

-----Original Message-----
How are you running the SPs? Assuming it's through a pass-through query, one
of the properties of the query is ODBC Timeout.

The value there is an Integer representing the number

of
seconds to wait

before a timeout error occurs. When the ODBCTimeout property is set to -1,
the timeout defaults to the current setting of the QueryTimeout property of
the Connection or Database object that contains the QueryDef. When the
ODBCTimeout property is set to 0, no timeout error occurs.

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)


"Michael V" <[email protected]>

wrote
in message

Hi

I have two stored procedures in an SQL database that I
execute from my Access application. These two procedures
work fine when executed from the sql server. When I run
the procedure from Access though, only the first one
executes OK and the second one comes up with:
[Microsoft][ODBC SQL Server Driver] Timeout expired

I think it's to do with the timeout setting or something
as the second one takes about 10 seconds more to complete
than the first one.

Both procedures are being called and executed by the same
function in my form.

Can anyone help please.

thanks Michael V


.

.