Procedures working from Management Studio but not from Access 2003

[acx]

Dear colleagues,

currently I face the following problem. My client application is
programmed in MS Access 2003 and my DB resides on MS SQL Server 2005.
In my DB, I have programmed a stored procedure which runs about 8
minutes. This sp is called from the client application via pass-
through query. This mechanism worked perfectly about 1 year but
suddenly ("overnight") the problem occured. When the sp is called from
Management Studio it works perfectly but when it is called from Access
it throws the error 3146: "ODBC - call failed" after 62-70 seconds
from the initiation. Unfortunately, I can't find any SQL Server log
which would specify the error. I have also tried some ODBC tracer
("ODBC Monitor") but I have received no concrete information as well.
It is true that I have made some small changes to the sp around the
time it stopped working from Access but those changes should not make
problems and - as I said - it works still perfectly from Management
Studio.

The second "overnight" problem is similar and occured in the same time
like the first. In Access, my code often builds a syntax of rather
complicated view based on user settings made on some form and passes
it to SQL Server. In some concrete cases, especially when the syntax
is very long, Access throws the error 3146 again (after about 20
seconds). However, when I run the view in Management Studio, it works
always well. Also, when I rebuilt the view syntax on the client
immediately and the syntax is now even longer then before, it is
successfully executed sometimes although the execution lasts eg. 1
minute...

I don't know what happend. Maybe some SQL Server update has been
installed, maybe some time-outs have been set (based on the actual
server performance)...

Do you have any ideas? Any help appreciated.

Thanks,
MikeX

 

[Razvan Socol]

Hello, MikeX

You are probably getting a "Timeout expired" error, because the
procedure takes too long. The default ODBC Timeout is 60 seconds. You
can change it in the properties of the pass-trough query, but you
should also think about optimizing your procedure to perform better
(add indexes, rewrite iterative logic based on cursors with set-based
logic, etc).

Razvan

 

[acx]

Dear Razvan,

Thank you for your answer.

I also thought the problem was caused by time-out but as I said, I
used exactly this way for 1 year and it worked pretty good. Suddenly
it stopped working although NOTHING had changed on the client side!
Furthermore, the second problem arises after 20 seconds, not 60
seconds.

Thanks once more,
MikeX

 

[Razvan Socol]

The error message "ODBC call failed" says nothing about the real
error. To find out what is the cause of the second error, look into
the DBEngine.Errors collection with an error handler like this:

Sub Test()
On Error GoTo ErrHandler

CurrentDb.Execute "PassThroughQuery"

Exit Sub
ErrHandler:
Dim msg As String, e As DAO.Error
For Each e In DBEngine.Errors
If e.Number <> 3146 Then
msg = msg & vbCrLf & e.Description
End If
Next e
MsgBox Err.Description & msg
End Sub

Razvan

 

[acx]

Razvan,

thank you very much. You have solved my problem. The real error is
"The query has been canceled because the estimated cost of this query
exceeds the configured threshold of 300."

This is something that the server's admin configured without my
knowledge...

Thanks a lot,
MikeX

 

[acx]

Razvan,

thank you very much. You have solved my problem. The real error is
"The query has been canceled because the estimated cost of this query
exceeds the configured threshold of 300."

This is something that the server's admin configured without my
knowledge...

Thanks a lot,
MikeX