(SQL Server 2000, SP3a)
Hello all!
I'm using ADO to reference a SQL Server 2000 database and was curious about the ADO
Connection Errors collection. It's my understanding that this object will contain both
errors *and* messages (like those produced by the Transact-SQL "print" statement). Is
there any way to differentiate between an error and a message from the ADO Error object?
Thanks for any help you can provide!
John PetersonHi John,
Try to add SET NOCOUNT ON as a very first statement inside of your SP. It
will remove all the messages from the result
--
Val Mazur
Microsoft MVP
Check Virus Alert, stay updated
http://www.microsoft.com/security/incident/blast.asp
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:OQgk5hNwDHA.2076@.TK2MSFTNGP09.phx.gbl...
> (SQL Server 2000, SP3a)
> Hello all!
> I'm using ADO to reference a SQL Server 2000 database and was curious
about the ADO
> Connection Errors collection. It's my understanding that this object will
contain both
> errors *and* messages (like those produced by the Transact-SQL "print"
statement). Is
> there any way to differentiate between an error and a message from the ADO
Error object?
> Thanks for any help you can provide!
> John Peterson
>|||Thanks Val! I was looking for some way to determine whether the Error item was a bonafide
error, or just an informational message. I think I can make that determination by the
NativeError property. For messages, it seems to be 0 (sort of analogous to @.@.ERROR = 0).
"Val Mazur" <group51a@.hotmail.com> wrote in message
news:u1ZTJZSwDHA.2308@.TK2MSFTNGP11.phx.gbl...
> Hi John,
> Try to add SET NOCOUNT ON as a very first statement inside of your SP. It
> will remove all the messages from the result
> --
> Val Mazur
> Microsoft MVP
> Check Virus Alert, stay updated
> http://www.microsoft.com/security/incident/blast.asp
>
> "John Peterson" <j0hnp@.comcast.net> wrote in message
> news:OQgk5hNwDHA.2076@.TK2MSFTNGP09.phx.gbl...
> > (SQL Server 2000, SP3a)
> >
> > Hello all!
> >
> > I'm using ADO to reference a SQL Server 2000 database and was curious
> about the ADO
> > Connection Errors collection. It's my understanding that this object will
> contain both
> > errors *and* messages (like those produced by the Transact-SQL "print"
> statement). Is
> > there any way to differentiate between an error and a message from the ADO
> Error object?
> >
> > Thanks for any help you can provide!
> >
> > John Peterson
> >
> >
>|||Hi John!
I think that what constitutes an error vs message is pretty much up to each
individual's interpretation. QA, for instance only show text (and not the
red error stuff) for "messages" with severity level > 10. Perhaps ADO has
the same distinction regarding whether an exception is produced in the
application? (Should be easy to test.)
I would use the severity level if I would need to make such distinction
myself. A quick test with RAISERROR suggests that @.@.ERROR is indeed 0 for
severity level < 11, however.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:uULJ99SwDHA.2712@.TK2MSFTNGP11.phx.gbl...
> Thanks Val! I was looking for some way to determine whether the Error
item was a bonafide
> error, or just an informational message. I think I can make that
determination by the
> NativeError property. For messages, it seems to be 0 (sort of analogous
to @.@.ERROR = 0).
>
> "Val Mazur" <group51a@.hotmail.com> wrote in message
> news:u1ZTJZSwDHA.2308@.TK2MSFTNGP11.phx.gbl...
> > Hi John,
> >
> > Try to add SET NOCOUNT ON as a very first statement inside of your SP.
It
> > will remove all the messages from the result
> >
> > --
> > Val Mazur
> > Microsoft MVP
> > Check Virus Alert, stay updated
> > http://www.microsoft.com/security/incident/blast.asp
> >
> >
> > "John Peterson" <j0hnp@.comcast.net> wrote in message
> > news:OQgk5hNwDHA.2076@.TK2MSFTNGP09.phx.gbl...
> > > (SQL Server 2000, SP3a)
> > >
> > > Hello all!
> > >
> > > I'm using ADO to reference a SQL Server 2000 database and was curious
> > about the ADO
> > > Connection Errors collection. It's my understanding that this object
will
> > contain both
> > > errors *and* messages (like those produced by the Transact-SQL "print"
> > statement). Is
> > > there any way to differentiate between an error and a message from the
ADO
> > Error object?
> > >
> > > Thanks for any help you can provide!
> > >
> > > John Peterson
> > >
> > >
> >
> >
>
Showing posts with label collection. Show all posts
Showing posts with label collection. Show all posts
Thursday, February 16, 2012
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.
>> >
>> >
>> >.
>> >
>
>.
>
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.
>> >
>> >
>> >.
>> >
>
>.
>
ADO does not add to Errors collection after the second FETCH NEXT in a SP
I'm having trouble obtaining errors raised in a stored
procedure via the ADO Errors collection after the second
FETCH NEXT statement from within that stored procedure.
Consider the following table created in a SQL Server
database:
CREATE TABLE TestTable
(
TestInt int
)
go
INSERT TestTable(TestInt) values(1)
INSERT TestTable(TestInt) values(2)
INSERT TestTable(TestInt) values(3)
This is a very simple table with one column, and three
rows containing the values 1, 2 and 3.
Consider this stored procedure:
CREATE PROCEDURE TestStoredProc
as
BEGIN
set rowcount 0
Set NoCount ON
declare @.TestInt int
declare @.ErrMsg char(7)
declare TestCursor cursor forward_only for
select * from TestTable
open TestCursor
Fetch next from TestCursor into @.TestInt
While @.@.fetch_status<>-1
Begin
select @.ErrMsg = 'Error ' + convert(char, @.testint)
raiserror(@.ErrMsg, 16, 1)
raiserror(@.ErrMsg, 16, 1)
Fetch next from TestCursor into @.TestInt
end
Close TestCursor
DeAllocate TestCursor
return
END
This stored procedure simply defines a cursor on all rows
in TestTable. For each row fetched from the cursor, the
error message 'Error n' is raised twice, where n is the
integer that had just been fetched from the cursor.
Finally, consider this VB code using ADO to execute the
above stored procedure. After the stored procedure is
executed, the code loops through the errors collection,
and creates a message box for each error in the collection:
Private Sub Form_Load()
Dim cn As Connection
Dim cm As Command
Dim oErr As Error
On Error Resume Next
Set cn = CreateObject("ADODB.Connection")
cn.Open "Data Source=<Some SQL Server>; Initial
Catalog=<Some Database Name>; Provider=SQLOLEDB; Persist
Security Info=False; Integrated Security=SSPI"
Set cm = CreateObject("ADODB.Command")
Set cm.ActiveConnection = cn
cm.CommandType = adCmdStoredProc
cm.CommandText = "TestStoredProc"
cm.Execute
For Each oErr In cn.Errors
MsgBox oErr.Description
Next
End
End Sub
When this code is executed, only two message boxes appear
with the message "Error 1".
Any help on this matter would be greatly appreciated :)Does anybody has any suggestions?|||Hi Ivan
You can bet your bottom dollar the focus of any replies will be on the use of the cursor rather than the ADO errors collection.
I know this is just an example - is this curiosity about a quirk you have spotted or a serious problem for you? If the latter, would you mind briefly explaining what your production cursor does as there are limited instances where it is as efficient as a set based solution. It may be that your sproc can be made more effective and your ADO errors issue made irrelevent.|||Of course, it is a serious problem for me. SP where this mechanism is used is a part of the big accounting system. Thus I can’t change it logic.
procedure via the ADO Errors collection after the second
FETCH NEXT statement from within that stored procedure.
Consider the following table created in a SQL Server
database:
CREATE TABLE TestTable
(
TestInt int
)
go
INSERT TestTable(TestInt) values(1)
INSERT TestTable(TestInt) values(2)
INSERT TestTable(TestInt) values(3)
This is a very simple table with one column, and three
rows containing the values 1, 2 and 3.
Consider this stored procedure:
CREATE PROCEDURE TestStoredProc
as
BEGIN
set rowcount 0
Set NoCount ON
declare @.TestInt int
declare @.ErrMsg char(7)
declare TestCursor cursor forward_only for
select * from TestTable
open TestCursor
Fetch next from TestCursor into @.TestInt
While @.@.fetch_status<>-1
Begin
select @.ErrMsg = 'Error ' + convert(char, @.testint)
raiserror(@.ErrMsg, 16, 1)
raiserror(@.ErrMsg, 16, 1)
Fetch next from TestCursor into @.TestInt
end
Close TestCursor
DeAllocate TestCursor
return
END
This stored procedure simply defines a cursor on all rows
in TestTable. For each row fetched from the cursor, the
error message 'Error n' is raised twice, where n is the
integer that had just been fetched from the cursor.
Finally, consider this VB code using ADO to execute the
above stored procedure. After the stored procedure is
executed, the code loops through the errors collection,
and creates a message box for each error in the collection:
Private Sub Form_Load()
Dim cn As Connection
Dim cm As Command
Dim oErr As Error
On Error Resume Next
Set cn = CreateObject("ADODB.Connection")
cn.Open "Data Source=<Some SQL Server>; Initial
Catalog=<Some Database Name>; Provider=SQLOLEDB; Persist
Security Info=False; Integrated Security=SSPI"
Set cm = CreateObject("ADODB.Command")
Set cm.ActiveConnection = cn
cm.CommandType = adCmdStoredProc
cm.CommandText = "TestStoredProc"
cm.Execute
For Each oErr In cn.Errors
MsgBox oErr.Description
Next
End
End Sub
When this code is executed, only two message boxes appear
with the message "Error 1".
Any help on this matter would be greatly appreciated :)Does anybody has any suggestions?|||Hi Ivan
You can bet your bottom dollar the focus of any replies will be on the use of the cursor rather than the ADO errors collection.
I know this is just an example - is this curiosity about a quirk you have spotted or a serious problem for you? If the latter, would you mind briefly explaining what your production cursor does as there are limited instances where it is as efficient as a set based solution. It may be that your sproc can be made more effective and your ADO errors issue made irrelevent.|||Of course, it is a serious problem for me. SP where this mechanism is used is a part of the big accounting system. Thus I can’t change it logic.
Subscribe to:
Posts (Atom)