June 3, 2008 at 9:04 am
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
June 5, 2008 at 1:55 am
I did manage to track down the issue. 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.
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply