Capture Errors and Print messages

[koinkoin]

Hi,

I have the following code to retrieve error and print messages. I was using the sqloledb driver. Unfortunately, I was not successful, and then I found a message on this forum saying that with sqloledb we can only obtain 1 message. As a result, I switched to MSDASQL (it was advised to do so). Now I am getting 1 more error messages and then the execution of the procedure just stops. Can someone help me understand what is wrong with my code?

Module Module1

Sub Main()

On Error GoTo ErrHandler

Dim myConn
Dim myCmd
Dim myRs

Dim myConnStr As String

myConnStr = "driver={SQL Server};server=APPD;database=Rk;Trusted_Connection=Yes"

' instantiate the ADO objects
myCmd = CreateObject("ADODB.Command")
myRs = CreateObject("ADODB.Recordset")

myConn = CreateObject("ADODB.Connection")
With myConn
.Provider = "MSDASQL"
.CursorLocation = 3
.ConnectionString = myConnStr
.Open()
End With

myCmd.ActiveConnection = myConn
With myCmd
.CommandType = 4
.CommandText = "process_hierarchy" 'sArgs(2)
End With

' execute the command
myRs = myCmd.Execute

myCmd = Nothing
myRs = Nothing
myConn = Nothing

Do While (Not myRs Is Nothing)
If myRs.State = 0 Then Exit Do
myRs = myRs.NextRecordset
Loop

GoTo Shutdown

ErrHandler:
Call ErrHandler(myConn)
Resume Next

Shutdown:
myCmd = Nothing
myRs = Nothing
myConn = Nothing

End Sub

Sub ErrHandler(ByVal objCon As Object)

Dim myErr

Dim strError As String

myErr = CreateObject("ADODB.Error")

For Each myErr In objCon.Errors
strError = myErr.Description
'MsgBox(strError)
Next

objCon.Errors.Clear()

End Sub
End Module

 

[NoCoolHandle]

When you say you are now seeing 1 more error, what are you expecting to get..

How many errors and how many print messages?

What happens when you execute the same command in Query Analyser?

Have you looked at the profiler?

 

[koinkoin]

Hi,

I am expecting 18 errors and 36 print messages (2 per error)
I am getting:
(1) SQLOLEDB : 1 errors and 2 print messages
(2) MSDASQL : 2 errors and 4 print messages
I have noticed that the execution of the procedure ("myRs = myCmd.Execute") just stops after the second error. When I remove the line "On Error GoTo ErrHandler", the VB just stops with the following :
"An unhandled exception of type 'System.Runtime.InteropServices.COMException' occurred in microsoft.visualbasic.dll

Additional information: [Microsoft][ODBC SQL Server Driver][SQL Server]Line 11: Incorrect syntax near '@node_long_name'."

Thanks for your help.

 

[NoCoolHandle]

Open up profiler (sql server tools) and then execute your code with it running a trace.. I think you might be interested in it's results.

Usually I used the infomessage event of the connection to gain access to the print statements, but the code you have should work fine in that situation.

What I am wondering is if a parameter is being missed or if you need to use a rs.close or rs.nextrecordset call that needs to be made to get the next group of errors.. My primary guess however is you have a parameter not being passed.


GOod luck

postback what you find..

Rob