ADO Error Collection in VB With SQL Prints

In Visual Basic using ADO, I am trying to get print
statements from SQL into my VB project. How does query
analyzer get the print statements from the server? I am
able to display the first TWO print statements using the
[recordset].NextRecordset command, but I get an error if
I try to go past the second recordset. Please help if
you can.
[CODE]
Dim objerr As ADODB.Error
On Error GoTo err_check
Do
For Each objerr In gcn.Errors
GetSQLPrints = GetSQLPrints & vbCrLf &
objerr.Description
rs.NextRecordset
Next
Loop
[/CODE]
Only Prints two... I don't know why. Please help.It looks like you're executing a NextRecordset during your Errors collection
iteration. I suggest you complete the iteration before NextRecordset since
multiple messages can be returned along with a recordset.
Below is a VBScript example that shows one method to process multiple
recordsets with messages. In VB, another method is to handle the ADO
Connection InfoMessage event and process the messages in your event handler.
SqlScript = _
"PRINT 'test message 1'" & vbCrLf & _
"PRINT 'test message 2'" & vbCrLf & _
"SELECT 3" & vbCrLf & _
"PRINT 'test message 4'" & vbCrLf & _
"SELECT 5" & vbCrLf & _
"PRINT 'test message 6'" & vbCrLf
Set MyRecordset = MyConnection.Execute(SqlScript)
Message = ""
Do While Not MyRecordset Is Nothing
RecordsetNumber = RecordsetNumber + 1
Message = Message & "Recordset " & _
RecordsetNumber & ":" & VbCrLf
If MyConnection.Errors.Count > 0 Then
For Each SqlError In MyConnection.Errors
Message = Message & vbTab & "Message: " & _
SqlError.Description & vbCrLf
Next
Else
Message = Message & vbTab & _
"No messages." & vbCrLf
End If
If MyRecordset.State = adStateOpen Then
Message = Message & vbTab & _
"Rowset returned." & vbCrLf
Else
Message = Message & vbTab & _
"No rowset returned." & vbCrLf
End If
Message = Message & vbCrLf
Set MyRecordset = MyRecordset.NextRecordset
Loop
Message = Message & "Recordset is Nothing."
MyConnection.Close
MsgBox Message
Hope this helps.
Dan Guzman
SQL Server MVP
"Mike B" <BGates@.Microsoft.com> wrote in message
news:034801c3c671$449addf0$a101280a@.phx.gbl...
> In Visual Basic using ADO, I am trying to get print
> statements from SQL into my VB project. How does query
> analyzer get the print statements from the server? I am
> able to display the first TWO print statements using the
> [recordset].NextRecordset command, but I get an error if
> I try to go past the second recordset. Please help if
> you can.
> [CODE]
> Dim objerr As ADODB.Error
> On Error GoTo err_check
> Do
> For Each objerr In gcn.Errors
> GetSQLPrints = GetSQLPrints & vbCrLf &
> objerr.Description
> rs.NextRecordset
> Next
> Loop
> [/CODE]
> Only Prints two... I don't know why. Please help.|||For our connection, we are using SQLOLEDB...
When we run you code example, getting an error saying:
"Current Provider does not support returning multiple
recordsets from a single execution"
If we take the do-while loop out, we get a message box
saying:
Recordset 1:
Message: text message 1
No recordset returned
Recordset is nothing
We did add the following Dim's:
Dim SqlScript as string
dim Myrecordset as new adodb.recordset
dim recordsetnumber as long
dim sqlerror as error
dim message as string
>--Original Message--
>It looks like you're executing a NextRecordset during
your Errors collection
>iteration. I suggest you complete the iteration before
NextRecordset since
>multiple messages can be returned along with a recordset.
>Below is a VBScript example that shows one method to
process multiple
>recordsets with messages. In VB, another method is to
handle the ADO
>Connection InfoMessage event and process the messages in
your event handler.
>SqlScript = _
> "PRINT 'test message 1'" & vbCrLf & _
> "PRINT 'test message 2'" & vbCrLf & _
> "SELECT 3" & vbCrLf & _
> "PRINT 'test message 4'" & vbCrLf & _
> "SELECT 5" & vbCrLf & _
> "PRINT 'test message 6'" & vbCrLf
>Set MyRecordset = MyConnection.Execute(SqlScript)
>Message = ""
>Do While Not MyRecordset Is Nothing
> RecordsetNumber = RecordsetNumber + 1
> Message = Message & "Recordset " & _
> RecordsetNumber & ":" & VbCrLf
> If MyConnection.Errors.Count > 0 Then
> For Each SqlError In MyConnection.Errors
> Message = Message & vbTab & "Message: " & _
> SqlError.Description & vbCrLf
> Next
> Else
> Message = Message & vbTab & _
> "No messages." & vbCrLf
> End If
> If MyRecordset.State = adStateOpen Then
> Message = Message & vbTab & _
> "Rowset returned." & vbCrLf
> Else
> Message = Message & vbTab & _
> "No rowset returned." & vbCrLf
> End If
> Message = Message & vbCrLf
> Set MyRecordset = MyRecordset.NextRecordset
>Loop
>Message = Message & "Recordset is Nothing."
>MyConnection.Close
>MsgBox Message
>
>--
>Hope this helps.
>Dan Guzman
>SQL Server MVP
>
>"Mike B" <BGates@.Microsoft.com> wrote in message
>news:034801c3c671$449addf0$a101280a@.phx.gbl...
>> In Visual Basic using ADO, I am trying to get print
>> statements from SQL into my VB project. How does query
>> analyzer get the print statements from the server? I am
>> able to display the first TWO print statements using
the
>> [recordset].NextRecordset command, but I get an error
if
>> I try to go past the second recordset. Please help if
>> you can.
>> [CODE]
>> Dim objerr As ADODB.Error
>> On Error GoTo err_check
>> Do
>> For Each objerr In gcn.Errors
>> GetSQLPrints = GetSQLPrints & vbCrLf &
objerr.Description
>> rs.NextRecordset
>> Next
>> Loop
>> [/CODE]
>> Only Prints two... I don't know why. Please help.
>
>.
>|||It looks like there are some differences in behavior between VB and
VBScript. On my system, the VB code below uses Recordset.Open instead of
Connection.Execute and returns the same result as the original VBScript I
posted.
Dim SqlScript As String
Dim MyConnection As New ADODB.Connection
Dim MyRecordset As New ADODB.Recordset
Dim recordsetnumber As Long
Dim sqlerror As Error
Dim message As String
Dim ConnectionString As String
Dim lastError As Integer
ConnectionString = _
"Provider=SQLOLEDB" & _
";Data Source=MyServer" & _
";Integrated Security=SSPI"
MyConnection.Open ConnectionString
SqlScript = _
"SET NOCOUNT ON" & vbCrLf & _
"PRINT 'test message 1'" & vbCrLf & _
"PRINT 'test message 2'" & vbCrLf & _
"SELECT 3" & vbCrLf & _
"PRINT 'test message 4'" & vbCrLf & _
"SELECT 5" & vbCrLf & _
"PRINT 'test message 6'" & vbCrLf
MyRecordset.Open SqlScript, MyConnection
message = ""
Do While Not MyRecordset.ActiveCommand Is Nothing
recordsetnumber = recordsetnumber + 1
message = message & "Recordset " & _
recordsetnumber & ":" & vbCrLf
If MyConnection.Errors.Count > 0 Then
For Each sqlerror In MyConnection.Errors
message = message & vbTab & "Message: " & _
sqlerror.Description & vbCrLf
Next
Else
message = message & vbTab & _
"No messages." & vbCrLf
End If
If MyRecordset.State = adStateOpen Then
message = message & vbTab & _
"Rowset returned." & vbCrLf
Else
message = message & vbTab & _
"No rowset returned." & vbCrLf
End If
message = message & vbCrLf
On Error Resume Next
Set MyRecordset = MyRecordset.NextRecordset
Loop
message = message & "Recordset is Nothing."
MyConnection.Close
MsgBox message
Hope this helps.
Dan Guzman
SQL Server MVP
"Mike B/Steve Z" <szlamany@.antarescomputing.com> wrote in message
news:10e801c3c70e$22406160$a301280a@.phx.gbl...
> For our connection, we are using SQLOLEDB...
> When we run you code example, getting an error saying:
> "Current Provider does not support returning multiple
> recordsets from a single execution"
> If we take the do-while loop out, we get a message box
> saying:
> Recordset 1:
> Message: text message 1
> No recordset returned
> Recordset is nothing
> We did add the following Dim's:
> Dim SqlScript as string
> dim Myrecordset as new adodb.recordset
> dim recordsetnumber as long
> dim sqlerror as error
> dim message as string
>
> >--Original Message--
> >It looks like you're executing a NextRecordset during
> your Errors collection
> >iteration. I suggest you complete the iteration before
> NextRecordset since
> >multiple messages can be returned along with a recordset.
> >
> >Below is a VBScript example that shows one method to
> process multiple
> >recordsets with messages. In VB, another method is to
> handle the ADO
> >Connection InfoMessage event and process the messages in
> your event handler.
> >
> >SqlScript = _
> > "PRINT 'test message 1'" & vbCrLf & _
> > "PRINT 'test message 2'" & vbCrLf & _
> > "SELECT 3" & vbCrLf & _
> > "PRINT 'test message 4'" & vbCrLf & _
> > "SELECT 5" & vbCrLf & _
> > "PRINT 'test message 6'" & vbCrLf
> >
> >Set MyRecordset = MyConnection.Execute(SqlScript)
> >
> >Message = ""
> >Do While Not MyRecordset Is Nothing
> > RecordsetNumber = RecordsetNumber + 1
> > Message = Message & "Recordset " & _
> > RecordsetNumber & ":" & VbCrLf
> > If MyConnection.Errors.Count > 0 Then
> > For Each SqlError In MyConnection.Errors
> > Message = Message & vbTab & "Message: " & _
> > SqlError.Description & vbCrLf
> > Next
> > Else
> > Message = Message & vbTab & _
> > "No messages." & vbCrLf
> > End If
> > If MyRecordset.State = adStateOpen Then
> > Message = Message & vbTab & _
> > "Rowset returned." & vbCrLf
> > Else
> > Message = Message & vbTab & _
> > "No rowset returned." & vbCrLf
> > End If
> > Message = Message & vbCrLf
> > Set MyRecordset = MyRecordset.NextRecordset
> >Loop
> >Message = Message & "Recordset is Nothing."
> >MyConnection.Close
> >MsgBox Message
> >
> >
> >--
> >Hope this helps.
> >
> >Dan Guzman
> >SQL Server MVP
> >
> >
> >"Mike B" <BGates@.Microsoft.com> wrote in message
> >news:034801c3c671$449addf0$a101280a@.phx.gbl...
> >> In Visual Basic using ADO, I am trying to get print
> >> statements from SQL into my VB project. How does query
> >> analyzer get the print statements from the server? I am
> >> able to display the first TWO print statements using
> the
> >> [recordset].NextRecordset command, but I get an error
> if
> >> I try to go past the second recordset. Please help if
> >> you can.
> >>
> >> [CODE]
> >> Dim objerr As ADODB.Error
> >> On Error GoTo err_check
> >> Do
> >> For Each objerr In gcn.Errors
> >> GetSQLPrints = GetSQLPrints & vbCrLf &
> >>
> objerr.Description
> >> rs.NextRecordset
> >> Next
> >> Loop
> >> [/CODE]
> >> Only Prints two... I don't know why. Please help.
> >
> >
> >.
> >|||Thank you - your example worked great in VB as well.
Not sure where our problem was - went in circles for a
while...
>--Original Message--
>It looks like there are some differences in behavior
between VB and
>VBScript. On my system, the VB code below uses
Recordset.Open instead of
>Connection.Execute and returns the same result as the
original VBScript I
>posted.
>Dim SqlScript As String
>Dim MyConnection As New ADODB.Connection
>Dim MyRecordset As New ADODB.Recordset
>Dim recordsetnumber As Long
>Dim sqlerror As Error
>Dim message As String
>Dim ConnectionString As String
>Dim lastError As Integer
>ConnectionString = _
> "Provider=SQLOLEDB" & _
> ";Data Source=MyServer" & _
> ";Integrated Security=SSPI"
>MyConnection.Open ConnectionString
>SqlScript = _
> "SET NOCOUNT ON" & vbCrLf & _
> "PRINT 'test message 1'" & vbCrLf & _
> "PRINT 'test message 2'" & vbCrLf & _
> "SELECT 3" & vbCrLf & _
> "PRINT 'test message 4'" & vbCrLf & _
> "SELECT 5" & vbCrLf & _
> "PRINT 'test message 6'" & vbCrLf
>MyRecordset.Open SqlScript, MyConnection
>message = ""
>Do While Not MyRecordset.ActiveCommand Is Nothing
> recordsetnumber = recordsetnumber + 1
> message = message & "Recordset " & _
> recordsetnumber & ":" & vbCrLf
> If MyConnection.Errors.Count > 0 Then
> For Each sqlerror In MyConnection.Errors
> message = message & vbTab & "Message: " & _
> sqlerror.Description & vbCrLf
> Next
> Else
> message = message & vbTab & _
> "No messages." & vbCrLf
> End If
> If MyRecordset.State = adStateOpen Then
> message = message & vbTab & _
> "Rowset returned." & vbCrLf
> Else
> message = message & vbTab & _
> "No rowset returned." & vbCrLf
> End If
> message = message & vbCrLf
> On Error Resume Next
> Set MyRecordset = MyRecordset.NextRecordset
>Loop
>message = message & "Recordset is Nothing."
>MyConnection.Close
>MsgBox message
>
>--
>Hope this helps.
>Dan Guzman
>SQL Server MVP
>
>"Mike B/Steve Z" <szlamany@.antarescomputing.com> wrote
in message
>news:10e801c3c70e$22406160$a301280a@.phx.gbl...
>> For our connection, we are using SQLOLEDB...
>> When we run you code example, getting an error saying:
>> "Current Provider does not support returning multiple
>> recordsets from a single execution"
>> If we take the do-while loop out, we get a message box
>> saying:
>> Recordset 1:
>> Message: text message 1
>> No recordset returned
>> Recordset is nothing
>> We did add the following Dim's:
>> Dim SqlScript as string
>> dim Myrecordset as new adodb.recordset
>> dim recordsetnumber as long
>> dim sqlerror as error
>> dim message as string
>>
>> >--Original Message--
>> >It looks like you're executing a NextRecordset during
>> your Errors collection
>> >iteration. I suggest you complete the iteration
before
>> NextRecordset since
>> >multiple messages can be returned along with a
recordset.
>> >
>> >Below is a VBScript example that shows one method to
>> process multiple
>> >recordsets with messages. In VB, another method is to
>> handle the ADO
>> >Connection InfoMessage event and process the messages
in
>> your event handler.
>> >
>> >SqlScript = _
>> > "PRINT 'test message 1'" & vbCrLf & _
>> > "PRINT 'test message 2'" & vbCrLf & _
>> > "SELECT 3" & vbCrLf & _
>> > "PRINT 'test message 4'" & vbCrLf & _
>> > "SELECT 5" & vbCrLf & _
>> > "PRINT 'test message 6'" & vbCrLf
>> >
>> >Set MyRecordset = MyConnection.Execute(SqlScript)
>> >
>> >Message = ""
>> >Do While Not MyRecordset Is Nothing
>> > RecordsetNumber = RecordsetNumber + 1
>> > Message = Message & "Recordset " & _
>> > RecordsetNumber & ":" & VbCrLf
>> > If MyConnection.Errors.Count > 0 Then
>> > For Each SqlError In MyConnection.Errors
>> > Message = Message & vbTab & "Message: " &
_
>> > SqlError.Description & vbCrLf
>> > Next
>> > Else
>> > Message = Message & vbTab & _
>> > "No messages." & vbCrLf
>> > End If
>> > If MyRecordset.State = adStateOpen Then
>> > Message = Message & vbTab & _
>> > "Rowset returned." & vbCrLf
>> > Else
>> > Message = Message & vbTab & _
>> > "No rowset returned." & vbCrLf
>> > End If
>> > Message = Message & vbCrLf
>> > Set MyRecordset = MyRecordset.NextRecordset
>> >Loop
>> >Message = Message & "Recordset is Nothing."
>> >MyConnection.Close
>> >MsgBox Message
>> >
>> >
>> >--
>> >Hope this helps.
>> >
>> >Dan Guzman
>> >SQL Server MVP
>> >
>> >
>> >"Mike B" <BGates@.Microsoft.com> wrote in message
>> >news:034801c3c671$449addf0$a101280a@.phx.gbl...
>> >> In Visual Basic using ADO, I am trying to get print
>> >> statements from SQL into my VB project. How does
query
>> >> analyzer get the print statements from the server?
I am
>> >> able to display the first TWO print statements using
>> the
>> >> [recordset].NextRecordset command, but I get an
error
>> if
>> >> I try to go past the second recordset. Please help
if
>> >> you can.
>> >>
>> >> [CODE]
>> >> Dim objerr As ADODB.Error
>> >> On Error GoTo err_check
>> >> Do
>> >> For Each objerr In gcn.Errors
>> >> GetSQLPrints = GetSQLPrints & vbCrLf &
>> >>
>> objerr.Description
>> >> rs.NextRecordset
>> >> Next
>> >> Loop
>> >> [/CODE]
>> >> Only Prints two... I don't know why. Please help.
>> >
>> >
>> >.
>> >
>
>.
>