Bob
Fri Mar 24 08:24:53 CST 2006
Bob Barrows [MVP] wrote:
> Bob Barrows [MVP] wrote:
>> ekkis wrote:
>>>> There is a trick. In order for PRINT statements to appear in the
>>>> connection's Errors collection, the procedure or batch has to raise
>>>> an error
>>>
>>> I've taken your code and suggestion but am failing to make it
>>> work... In
>>> the code below, I have tried 3 variations on the SQL to execute with
>>> the following results:
>>
>> I don't know how I missed this.
>> I'm a little busy right now but I will look at it later.
>>
>
> I was away from the keyboard all day yesterday so I will not be able
> to address this until later today. Sorry about that.
>
OK, I had a few minutes this morning and I did make some progress (I was
able to see content of the recordset as well as the error message from the
raiserror, but i was not able to get the PRINT). You will find my code at
the end. As you will see, I had to jump through quite a few hoops.
I am out of time, but I do want to mention:
1. The T-SQL Print statement is NOT stdout. If it was, it would print to a
DOS window, correct? It is intended to print output to the Query Analyzer
application.
2. ODBC and OLE DB have a long history of not being able to reliably handle
the results of multiple PRINT and RAISERROR statements. See Erland
Sommarskog's error-handling series at
http://www.sommarskog.se/index.html
I would suggest using a different technique to get the strings you want to
use PRINT to return, either an output parameter or select statements (you
can use NextRecordset to retrieve multiple resultsets.). Instead of
set rs = srv.Execute("print 'YAYA'; " & _
"select v=@@version; raiserror('ERR', 11,1, 1)",,1)
Use
set rs = srv.Execute("select 'YAYA'; " & _
"select v=@@version; raiserror('ERR', 11,1, 1)",,1)
dim srv,objRS, ar(), i, adoerr, errs
s = "UA.Merlin"
x = Split(s, ".")
Set srv = CreateObject("ADODB.Connection")
'DSN = "Provider = SQLOLEDB;Data Source=" & x(0) & ";"
'DSN = DSN & "Trusted_Connection=Yes;"
'DSN = DSN & "Initial Catalog=" & x(1) & ";"
srv.open DSN
on error resume next
dim rs
set rs = srv.Execute("print 'YAYA'; " & _
"select v=@@version; raiserror('ERR', 11,1, 1)",,1)
if err<> 0 then
msgbox err.description,,"ADO Error After Initial Open"
end if
for j=0 to 4
if rs.state=0 then
err.clear
set rs=rs.nextrecordset
if err<>0 then
msgbox err.description,,"ADO Error After MoveNext"
errs = srv.errors.count
if errs > 1 then
for i = 0 to errs - 1
set adoerr = srv.errors(i)
desc = adoerr.description
if desc <> "ERR" then
wscript.echo "srv.errors(i) contained " & desc
end if
next
end if
end if
else
exit for
end if
next
if not rs.eof then
err.clear
wscript.echo "Results: " & rs(0)
set rs=rs.nextrecordset
if err<>0 then
wscript.echo "ADO error contained " & vbcrlf & err.description
errs = srv.errors.count
wscript.echo "Error count - " & errs
if errs > 1 then
for i = 0 to errs - 1
set adoerr = srv.errors(i)
desc = adoerr.description
if desc <> "ERR" then
wscript.echo "srv.errors(i) contained " & desc
end if
next
end if
end if
rs.Close
errs = srv.errors.count
wscript.echo "Error count - " & errs
if errs > 1 then
for i = 0 to errs - 1
set adoerr = srv.errors(i)
desc = adoerr.description
if desc <> "ERR" then
wscript.echo "srv.errors(i) contained " & desc
end if
next
end if
end if
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.