Forum Discussion
ODBC Timeout Problem
Hi,
I am calling a SQL Server stored procedure from my MS Access program and am getting the ODBC timeout error. When I run the stored procedure from SSMS, it executes in less than one second. I am passing no input parameters and the results are not returned from the stored procedure - they are written to a table instead. How can I avoid the timeout error?
Dim cnn As New ADODB.Connection
Dim cmd As New ADODB.Command
connString = <all the usual stuff>
cnn.Open (connString)
With cmd
.ActiveConnection = cnn
.CommandType = adCmdStoredProc
.CommandText = "usp_GetUnbilledItems"
.Execute ---Times out here.
End With
- Please show us the exact error message.
Can you use other stored procedures from Access?
How long does usp_GetUnbilledItems run? Whats your ODBC timeout setting?
Are there blocking transactions running? - I would also like to know if other stored procedures time out in this environment. As a test, create something really simple for the stored proc, something that should execute in milliseconds, like
SELECT @@OPTIONS;
If that runs without timing out, you can be more confidant the problem is in this particular stored proc.
Another thing to consider is whether your SQL Server login has appropriate permissions to run that stored proc.- The problem was caused by blocking. I was able to resolve that.
