SQL Server stored procedure fails to complete when called from MS Access

[moconnell2007]

Hi All,

I have inherited an MS Access application recently. I have a problem with a call to a SQL Server stored procedure (from VBA in Access). The connection is established with ADO and connects to the server fine and starts running the SP. However, there is a loop in the SP (while @@fetch_status = 0) and after about 14 iterations, the running of the SP seems to halt or break out and the connection from Access closed (as per designed).

I thought that I am missing a timeout setting somewhere (either on SQL or the connection from Access, ADO) but i have tried many different settings (e.g. myConnection.CommandTimeout etc) but to no avail. The problem is that I am not seeing any error message. Just that the SP does not complete when there is > 14 items to be processed.

The SP runs ok when run through Query Analyzer, however, I have noticed when it is running that it processes and displays the first 14 "items" in the Messages pane before a slight pause and continuing on with the remainder (and subsequent pauses after this). I was wondering if the ADO connection from Access sees this pause and thinks the SP has finished??? Anybody got any ideas? Appreciate any thoughts on this. Code for connection below:

Thanks,
Mark.

Dim cnn As New ADODB.Connection

cnn.CommandTimeout = 10000

cnn.ConnectionString = "File Name=\\musnas\IT\Retail\RMS_Dev.udl;"

cnn.Open
MsgBox "Records being sent to GOLD...Click OK and wait for further instruction...."
cnn.LocalArticlemig1 -- Have also tried cnn.Execute ("exec LocalArticlemig1") but no joy!!
cnn.Close

MsgBox "Records attempted to be sent to GOLD. Please print the Local Line Status Report to confirm success"


Forms!frmArticle!lstUnmatchedStores.Requery

DoCmd.Close

 

[SQL_Hell]

Hey there,

Just looking at your code and I wondering if you have tried setting the command timeout to zero? That should eliminate whether it is timing out or not at least.

cnn.CommandTimeout = 0


If that doesn't fix it please post the stored proc code,which sounds like it has a cursor in it somewhere.

 

[moconnell2007]

Thanks SQL Hell for the suggestion. The .CommandTimeout = 0 didn't make a difference - had tried this already.

I did manage to track down the issue tho. Inside the SP, DTS packages and user defined functions were being called - which was fine. However, within some of these were print stmts, used for debugging when they were first developed. I found that the more print statements used throughout the SP, the less iterations were being performed when run from Access. I saw all these print stmts when I ran the SP in Query Analyzer. SQL Server seems to process a chunk of data at a time - I noticed every 7-8 seconds, a chunk of data was processed (noticed this from the print messages output). The less print stmts, the more data processed within the 7-8 second window before it output to screen. Removing all prints throughout the code, caused the SP to run fully from Access.

I believe that Access determined the first output of data (from print stmts) through the connection to be the end of the query or SP and closed the connection or stopped the execute command. Either this or the print data output, reached a network package size limit and closed the connection. Whichever is the explanation, the code is working now.


Thanks again.