April 19, 2005 at 3:55 pm
Hi All,
I have an Access form that gets its records from a SQL Server stored procedure. The form will also run a second SP when a button is pressed. The second SP seems to be a problem. When I run the SP from query analyzer, it completes without any errors. When I run the SP from the Access form, I get no errors, but the SP does not complete properly. This second SP does not return any records, but the SP upon which the form is based let's me know that the records were not updated properly even though no errors occured.
I've tried explicitly catching the errors in VBA using On Error Goto ... and MsgBox Err.Number ... and nothing is returned. I've tried it without the VBA attempt to catch errors ... nothing. My account is an administrative one with full rights to the SQL server.
I run the SP from within VBA using:
strSQL = "EXEC pCheckAllExceptions '" & dteBDate & "', '" & dteEDate & "'"
This method has worked for all other SP. Even with those that return errors, I can catch the error in Access and go from there ... not with this bad Daddy though.
Any ideas about where I can even begin to troubleshoot this issue? What's happening? ANYTHING?!?
Thanks in advance,
Kyle.
April 19, 2005 at 5:16 pm
Does the stored proc run from Query Analyzer with hardcoded values for the parameters ?
******************
Dinakar Nethi
Life is short. Enjoy it.
******************
April 19, 2005 at 9:19 pm
Dinakar,
Yes. The SP that doesn't work in Access will run from query analyzer with hard-coded parameters and I have checked the strings that VBA sends to SQL server to ensure that the dates are in the proper format.
From Query Analyzer, I can run:
EXEC pCheckAllExceptions '3/1/2005', '3/31/2005'
And get no errors.
The string that Access sends to SQL server is exactly the same. I think the procedure is running and stopping at some point, but I don't know where in the procedure it's having problems since no errors are returned.
Kyle.
April 19, 2005 at 9:32 pm
Is the data that is going into the variables in the same format mm/dd/yyyy ?
******************
Dinakar Nethi
Life is short. Enjoy it.
******************
April 20, 2005 at 2:31 am
Are you using ADO or DAO?
If you are using ADO try converting the SP call into a command object. You can then use a Return Value parameter to pick up any return code (typically zero for OK and non-zero for a failure) that you have programmed. You can also use the Errors collection to pick up any messages, including those from PRINT commands. You will need to loop through the returned recordsets and test their state to see which have messages, but MSDN has a couple of good examples.
For DAO you can use a QueryDef to run a pass-through query to fill a recordset with the return code using TSQL and then output using a select statement, e.g.
strSQL = "DECLARE @rc int; " & _
"EXEC @RC=pCheckAllExceptions '" & dteBDate & "', '" & dteEDate & "'; " & _
"SELECT @rc AS ReturnCode;"
If you can modify your SP try adding error checks which RAISERROR('My Error Message', 16, 1) which can be picked up by both ADO and DAO, and can be trapped with VBA error handling.
April 20, 2005 at 2:31 am
Most SQL errors lead to at least a batch abort so internal error handling is unhelpful. Assuming that you are connecting through ADO, you need to check the ADO errors collection (distinct from the VB errors collection) on the client side :
Public Function ADOErrorString(Command As ADODB.Command) As String
' Checks the supplied connection object for errors.
' If any are found, formats them in the returned string.
' Otherwise returns an empty string
Dim l_conConnection As ADODB.Connection
Dim l_prmParameter As ADODB.Parameter
Dim l_objError As ADODB.Error
Dim l_cReturnString As String
On Error Resume Next
Set l_conConnection = Command.ActiveConnection
With l_conConnection
If .Errors.Count > 0 Then
l_cReturnString = _
"ADO has returned " & CStr(.Errors.Count) & " Errors :" _
& vbCrLf & vbCrLf _
& "Connection String : " & .ConnectionString _
& vbCrLf & vbCrLf _
& "Cursor Location : " & .CursorLocation _
& vbCrLf & vbCrLf _
& "Command Text : " & Command.CommandText
If Command.Parameters.Count > 0 Then
l_cReturnString = l_cReturnString _
& vbCrLf & vbCrLf _
& "Command Parameters :" & vbCrLf
For Each l_prmParameter In Command.Parameters
l_cReturnString = l_cReturnString & vbCrLf _
& l_prmParameter.Name & " = " & CStr(l_prmParameter.Value)
Next
End If
For Each l_objError In .Errors
With l_objError
l_cReturnString = l_cReturnString & vbCrLf & vbCrLf _
& "Error Number : " & CStr(.Number) & vbCrLf _
& " Description : " & .Description & vbCrLf _
& " Source : " & .Source & vbCrLf _
& " SQL State : " & .SQLState & vbCrLf _
& " Native Error : " & CStr(.NativeError)
End With
Next
ADOErrorString = l_cReturnString
Else
ADOErrorString = ""
End If
End With
Set l_prmParameter = Nothing
Set l_conConnection = Nothing
Exit Function
End Function
April 20, 2005 at 6:45 am
"SET NOCOUNT ON" is indispensible in procedures called using ADO. If the client sees a non-error informational message output by the procedure, the client generally will not see an error message that arrives after. I remember that errors did not show up in the Errors collection even looping through the resultsets. My experience is a few years old; perhaps the latest versions have better behavior.
PRINT statements also confuse. Check for non-critical errors also. I think ADO needs a severity of at least 11 to be considered an error for ON ERROR GOTO.
April 20, 2005 at 7:02 am
Good point. Only the last output is returned to ADO as a resultset and this will be the 'rows affected' message unless NOCOUNT is set ON.
Even then, you will only get the LAST recordset even if the stored procedure produces several and not even that if there was some other (eg print) output afterwards.
A fatal error (and nearly all are!) will just drop out without even reaching any exception handler that you might have built into your stored procedure. Depending on the severity, you might get a VB error but you should always find something in the ADO error collection.
April 20, 2005 at 9:03 am
Having worked in Access for years before branching to SQL Server I have run across these type errors previously. No guarantee but the parameters you use in our embedded SQL; are they dimensioned as dates or a string. You will note that in the Query analyzer you are passing a string value for a date. Maybe you need to use the CStr function to convert the variables to strings in you embedded SQL.
April 20, 2005 at 10:08 am
Thank you VERY much to all that responded!
After making a few changes in the way the SP is called, the first few tests seem to indicate that everything is working ... for now.
I had previously been using VBA's DoCmd to call the SP as in:
strSQL = "EXEC pGetAllExceptions '" & dteBDate & "' ,'" & dteEDate & "'"
DoCmd.RunSQL strSQL
I have added a support module with a new sub that uses ADO to call the SP:
Public Sub CallExceptionsProc(dteBDate As Date, dteEDate As Date)
Dim cnn1 As ADODB.Connection
Dim cmdChkEx As ADODB.Command
Dim strCnn As String
Dim prmBDate As ADODB.Parameter
Dim prmEDate As ADODB.Parameter
Set cnn1 = New ADODB.Connection
strCnn = "Provider=SQLOLEDB.1;" & _
"Integrated Security=SSPI;" & _
"Persist Security Info=False;" & _
"Initial Catalog=XXXXX;" & _
"Data Source=XXXXX;"
cnn1.Open strCnn
Set cmdChkEx = New ADODB.Command
Set cmdChkEx.ActiveConnection = cnn1
cmdChkEx.CommandText = "pGetAllExceptions"
cmdChkEx.CommandType = adCmdStoredProc
cmdChkEx.CommandTimeout = 300
Set prmBDate = New ADODB.Parameter
prmBDate.Type = adDate
prmBDate.Direction = adParamInput
prmBDate.Value = dteBDate
cmdChkEx.Parameters.Append prmBDate
Set prmEDate = New ADODB.Parameter
prmEDate.Type = adDate
prmEDate.Direction = adParamInput
prmEDate.Value = dteEDate
cmdChkEx.Parameters.Append prmEDate
cmdChkEx.Execute
MsgBox ADOErrorString(cmdChkEx)
cnn1.Close
Set cnn1 = Nothing
Set cmdChkEx = Nothing
Set prmBDate = Nothing
Set prmEDate = Nothing
End Sub
I THINK that the problems was not that errors were occuring and not being returned, but that the DoCmd.RunSQL call was timing out. The exceptions SP takes about 1.5 minutes to run (I know, I know, "15 seconds or less", but I haven't found a good way to eliminate a particular loop in the SP ... anyone who wants to tackle that one can let me know and I'll send the SP!). So, in the new sub above, I kicked up the timeout period to 5 minutes. No errors are returned and the records appear to be updated properly. So, I think it works.
I am curious if the line "MsgBox ADOErrorString(cmdChkEx)" in the above sub is the appropriate way to access Stewart Joslyn's excellent error trapping function and return errors for the command? It seems to be working, but since I'm new to using ADO, I want to make sure the function is using the command from the sub above and not something else.
Anyway, this seems to work. Again, thanks to all who responded so quickly!
Best Regards,
Kyle Brown
April 21, 2005 at 1:43 am
The call looks OK to me. Do note that the function ignores errors in itself (this was a deliberate decision in the application that I built it for but you might want to modify it). Also, I notice that I missed out Set l_objError = Nothing at the end.
Don't forget that there are separate Connection and Command Timeout properties.
April 21, 2005 at 6:30 am
Stewart,
Thank you so much for all your help.
Kyle.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply