SQL ADO Get Print Messages?

[buymeapc]

Hello all,

 

I  have a SQL database that I'm running scripts on in order to update the contents of the database. Some of these scripts have try/catch statements, which include print statements. I can see the print statements if I use SSMS, but I'm not sure how to get them using AutoIt while running the script. I was originally using SQL.au3, but I have also been trying to use ADO.au3. With the very simplified script below using ADO.au3, how can I display the print statement result of 'hello'? Thanks for your help!

#include "ADO.au3"

; SetUP internal ADO.au3 UDF COMError Handler
_ADO_ComErrorHandler_UserFunction(_ADO_COMErrorHandler)

$sDatabase = 'mydb' ; change this string to YourDatabaseName
$sServer = 'localhost' ; change this string to YourServerLocation
$sUser = 'sa' ; change this string to YourUserName
$sPassword = 'MyPassword' ; change this string to YourPassword


;_Example_4_MSSQL_SQLAuth($sServer, $sDatabase, $sUser, $sPassword, "Select * from MY_TABLE"); this works fine!
_Example_4_MSSQL_SQLAuth($sServer, $sDatabase, $sUser, $sPassword, "if 1 = 1 print 'hello'"); how do I return the print message?

Func _Example_4_MSSQL_SQLAuth($sServer, $sDatabase, $sUser, $sPassword, $sQUERY)

    ; Create connection object
    Local $oConnection = _ADO_Connection_Create()

    ; Open connection with $sConnectionString
    _ADO_Connection_OpenMSSQL($oConnection, $sServer, $sDatabase, $sUser, $sPassword, 'YourAppName', @ComputerName & '_' & 'YourProgram_UID')
    If @error Then Return SetError(@error, @extended, $ADO_RET_FAILURE)

    ; Executing some query directly to Array of Arrays (instead to $oRecordset)
    Local $aRecordset = _ADO_Execute($oConnection, $sQUERY, True)

    ; Clean Up
    _ADO_Connection_Close($oConnection)
    $oConnection = Null

    ; Display Array Content with column names as headers
    _ADO_Recordset_Display($aRecordset, 'Recordset content')

EndFunc   ;==>_Example_4_MSSQL_SQLAuth

 

[mLipok]

https://stackoverflow.com/questions/53070871/capture-t-sql-print-output-in-ado

https://support.microsoft.com/en-us/help/194792/how-to-retrieve-values-in-sql-server-stored-procedures-with-ado

 

 

 

[mLipok]

https://www.mssqltips.com/sqlservertip/6359/how-does-sql-server-management-studio-get-its-data/

 

[mLipok]

https://docs.microsoft.com/en-us/sql/ado/reference/ado-api/errors-collection-ado?view=sql-server-2017

Quote

Some properties and methods return warnings that appear as Error objects in the Errors collection but do not halt a program's execution. Before you call the Resync, UpdateBatch, or CancelBatch methods on a Recordset object, the Open method on a Connection object, or set the Filter property on a Recordset object, call the Clear method on the Errors collection. That way you can read the Count property of the Errors collection to test for returned warnings.

 

 

[buymeapc]

Thank you so much for your helpful links, mLipok!! I am finally able to display the output now.

Here's my example which I'm finally able to see the output in the console.

expandcollapse popup

#include-once
#include <array.au3>
#include "ADO.au3"

Global $oADODB_Connection = ObjCreate("ADODB.Connection")

_Example()
Func _Example()
    ; Error monitoring. This will trap all COM errors while alive.
    ; This particular object is declared as local, meaning after the function returns it will not exist.
    Local $oErrorHandler = ObjEvent("AutoIt.Error", "_ErrFunc")

    Local $sPassword ='MyPassword'
    $oADODB_Connection.Open("PROVIDER=SQLOLEDB.1;SERVER=localhost;DATABASE=mydb;uid=sa;pwd="&$sPassword&";")

    Local $objquery = $oADODB_Connection.Execute("if 1 = 1 print 'this is a test - hello'")

    Local $test = $objquery.Parent

    ;ConsoleWrite('Before CommitTrans' & @CRLF)
    ;$oADODB_Connection.CommitTrans

EndFunc   ;==>_Example


; User's COM error function. Will be called if COM error occurs
Func _ErrFunc($oError)
    Local $iErrorCol_Max = $oADODB_Connection.errors.Count
    If $iErrorCol_Max = 0 Then
        ConsoleWrite( @CRLF & _
                "! ==> COM Error intercepted ==> NOT ADO related Error " & @CRLF & _
                "-" & @TAB & "$oError.description is: " & @TAB & $oError.description & @CRLF & _
                "-" & @TAB & "$oError.number is: " & @TAB & @TAB & $oError.number & "   in HEX is  0x" & Hex($oError.number) & @CRLF & _
                "-" & @TAB & "$oError.source is: " & @TAB & @TAB & $oError.source & @CRLF & _
                "-" & @TAB & "$oError.windescription:" & @TAB & @TAB & $oError.windescription & @CRLF & _
                "-" & @TAB & "$oError.helpfile is: " & @TAB & $oError.helpfile & @CRLF & _
                "-" & @TAB & "$oError.helpcontext is: " & @TAB & $oError.helpcontext & @CRLF & _
                "-" & @TAB & "$oError.lastdllerror is: " & @TAB & $oError.lastdllerror & @CRLF & _
                "-" & @TAB & "$oError.scriptline is: " & @TAB & @TAB & $oError.scriptline & @CRLF & _
                "-" & @TAB & "$oError.retcode is: " & @TAB & @TAB & "0x" & Hex($oError.retcode) & @CRLF & _
                "! ==> COM End of Error dump." & @CRLF & _
                @CRLF)

    Else
        Local $oErr
        For $iErrorCol_idx = 0 To $iErrorCol_Max - 1
            $oErr = $oADODB_Connection.errors.Item($iErrorCol_idx)
            ConsoleWrite( @CRLF & _
                    "! ==> COM Error intercepted ==> ADO Error Collection: .errors.Count = " & $iErrorCol_Max & "   $oErr = .errors.Item(" & $iErrorCol_idx & ") " & @CRLF & _
                    ">" & @TAB & "$oErr.description is: " & @TAB & @TAB & $oErr.description & @CRLF & _
                    ">" & @TAB & "$oErr.number is: " & @TAB & @TAB & $oError.number & "   in HEX is  0x" & Hex($oErr.number) & @CRLF & _
                    ">" & @TAB & "   ErrorValueEnum description:" & @TAB & _ADO_MSDNErrorValueEnum_Description("0x" & Hex($oErr.number)) & @CRLF & _
                    ">" & @TAB & "$oErr.source is: " & @TAB & @TAB & $oErr.source & @CRLF & _
                    ">" & @TAB & "$oErr.SQLState is: " & @TAB & @TAB & $oErr.SQLState & '  Description: ' &_SQLState_Description($oErr.SQLState) & @CRLF & _
                    ">" & @TAB & "$oErr.NativeError is: " & @TAB & @TAB & $oErr.NativeError & @CRLF & _
                    "-" & @TAB & "$oError.windescription:" & @TAB & @TAB & $oError.windescription & @CRLF & _
                    "-" & @TAB & "$oError.scriptline is: " & @TAB & @TAB & $oError.scriptline & @CRLF & _
                    "-" & @TAB & "$oError.helpfile is: " & @TAB & @TAB & $oError.helpfile & @CRLF & _
                    "-" & @TAB & "$oError.helpcontext is: " & @TAB & $oError.helpcontext & @CRLF & _
                    "-" & @TAB & "$oError.lastdllerror is: " & @TAB & $oError.lastdllerror & @CRLF & _
                    "-" & @TAB & "$oError.retcode is: " & @TAB & @TAB & "0x" & Hex($oError.retcode) & @CRLF & _
                    "-" & @TAB & "$oError.number is: " & @TAB & @TAB & "0x" & Hex($oError.number) & @CRLF & _
                    "! ==> COM End of Error Collections dump." & @CRLF & _
                    @CRLF)
        Next
        $oADODB_Connection.errors.clear
    EndIf

EndFunc   ;==>_ErrFunc

 

[mLipok]

do you especially/intentionally fire error:

Local $test = $objquery.Parent

?

[mLipok]

Here is your script (little modified by me):

expandcollapse popup

#include <array.au3>
#include "__UDF_Forum\ADO.au3" ; your ADO.au3 localization

Global $oADODB_Connection = ObjCreate("ADODB.Connection")

_Example()
Func _Example()
    ; Error monitoring. This will trap all COM errors while alive.
    ; This particular object is declared as local, meaning after the function returns it will not exist.
    Local $oErrorHandler = ObjEvent("AutoIt.Error", "_ErrFunc")
    #forceref $oErrorHandler

    Local $sPassword = 'AutoIt'
    $oADODB_Connection.Open("PROVIDER=SQLOLEDB.1;SERVER=localhost\SQLExpress;uid=sa;pwd=" & $sPassword & ";")

    Local $objquery = $oADODB_Connection.Execute("if 1 = 1 print 'this is a test - hello'")
    #forceref $objquery

    Local $test = $objquery.Parent
    #forceref $test

EndFunc   ;==>_Example


; User's COM error function. Will be called if COM error occurs
Func _ErrFunc($oError)
    Local $iErrorCol_Max = $oADODB_Connection.errors.Count
    If $iErrorCol_Max = 0 Then
        ConsoleWrite(@CRLF & _
                "! ==> COM Error intercepted ==> NOT ADO related Error " & @CRLF & _
                "-" & @TAB & "$oError.description is: " & @TAB & $oError.description & @CRLF & _
                "-" & @TAB & "$oError.number is: " & @TAB & @TAB & $oError.number & "   in HEX is  0x" & Hex($oError.number) & @CRLF & _
                "-" & @TAB & "$oError.source is: " & @TAB & @TAB & $oError.source & @CRLF & _
                "-" & @TAB & "$oError.windescription:" & @TAB & @TAB & $oError.windescription & @CRLF & _
                "-" & @TAB & "$oError.helpfile is: " & @TAB & $oError.helpfile & @CRLF & _
                "-" & @TAB & "$oError.helpcontext is: " & @TAB & $oError.helpcontext & @CRLF & _
                "-" & @TAB & "$oError.lastdllerror is: " & @TAB & $oError.lastdllerror & @CRLF & _
                "-" & @TAB & "$oError.scriptline is: " & @TAB & @TAB & $oError.scriptline & @CRLF & _
                "-" & @TAB & "$oError.retcode is: " & @TAB & @TAB & "0x" & Hex($oError.retcode) & @CRLF & _
                "! ==> COM End of Error dump." & @CRLF & _
                @CRLF)

    Else
        Local $oErr
        For $iErrorCol_idx = 0 To $iErrorCol_Max - 1
            If $iErrorCol_idx = 0 Then ConsoleWrite(@CRLF & _
                    "! ==> COM Error intercepted ==> ADO Error Collection: .errors.Count = " & $iErrorCol_Max & @CRLF & _
                    "")
            $oErr = $oADODB_Connection.errors.Item($iErrorCol_idx)
            ConsoleWrite(@CRLF & _
                    ">" & @TAB & "$oErr(" & $iErrorCol_idx & ").description is: " & @TAB & $oErr.description & @CRLF & _
                    ">" & @TAB & "$oErr(" & $iErrorCol_idx & ").number is: " & @TAB & @TAB & $oError.number & "   in HEX is  0x" & Hex($oErr.number) & @CRLF & _
                    ">" & @TAB & "$oErr(" & $iErrorCol_idx & ").source is: " & @TAB & @TAB & $oErr.source & @CRLF & _
                    ">" & @TAB & "$oErr(" & $iErrorCol_idx & ").SQLState is: " & @TAB & @TAB & $oErr.SQLState & '  Description: ' & _SQLState_Description($oErr.SQLState) & @CRLF & _
                    ">" & @TAB & "$oErr(" & $iErrorCol_idx & ").NativeError is: " & @TAB & $oErr.NativeError & @CRLF & _
                    ">" & @TAB & "   ErrorValueEnum description:" & @TAB & _ADO_MSDNErrorValueEnum_Description("0x" & Hex($oErr.NativeError)) & @CRLF & _
                    "-" & @TAB & "$oError.windescription:" & @TAB & @TAB & $oError.windescription & @CRLF & _
                    "-" & @TAB & "$oError.scriptline is: " & @TAB & @TAB & $oError.scriptline & @CRLF & _
                    "-" & @TAB & "$oError.helpfile is: " & @TAB & @TAB & $oError.helpfile & @CRLF & _
                    "-" & @TAB & "$oError.helpcontext is: " & @TAB & $oError.helpcontext & @CRLF & _
                    "-" & @TAB & "$oError.lastdllerror is: " & @TAB & $oError.lastdllerror & @CRLF & _
                    "-" & @TAB & "$oError.retcode is: " & @TAB & @TAB & "0x" & Hex($oError.retcode) & @CRLF & _
                    "-" & @TAB & "$oError.number is: " & @TAB & @TAB & "0x" & Hex($oError.number) & @CRLF & _
                    "")
        Next
        $oADODB_Connection.errors.clear
        ConsoleWrite("! ==> COM End of Error Collections dump." & @CRLF)
    EndIf
EndFunc   ;==>_ErrFunc

 

How many errors you see ?
Why you need to fire Error to get Print Output ?

 

Check this new Article on WIKI:

https://www.autoitscript.com/wiki/ADO_Event_handling#First_example

and here:

 

Edited September 7, 2020 by mLipok

[mLipok]

@buymeapc

Current ADO.au3 UDF v2.1.18 supports catching this information by default.

Take a look.

 

[Humanzee]

On 10/18/2020 at 9:49 AM, mLipok said:

Current ADO.au3 UDF v2.1.18 supports catching this information by default.

I verified this print thing in v2.1.19 B   But what I am after are the other errors from SQL.  If you run a faulty query like   "Select cast('0503\03\03' as date)" in SSMS I want to be able to get the returned.

Msg 241, Level 16, State 1, Line 1
Conversion failed when converting date and/or time from character string.

I've been looking around, but errors like that don't seem to trigger the error handler.  using  the below in the ADO_EXAMPLE.au3

when run from within an altered _Example_MSSQL_SQLServerAuthorization()

_Example_1_RecordsetToConsole($sConnectionString, "Select cast('0503\03\03' as date)")

Nothing that I can see happens for that relative to the error handling

$oADO_COMErrorHandler = ObjEvent("AutoIt.Error", __ADO_ComErrorHandler_WrapperFunction)

in  _ADO_EXECUTE(), where as other queries return data.

are these not com errors, or AutoIt.Error , or Events?  how do we get them. 

I have an app that was trying to set a date in an update statement based on badly parsed input syntax.  That bug, stopped the rest of the update statement values from being set and we didn't notice that records weren't always getting updated for a month because not every update included a date.   Since there was no validation error presented to the user for the bad syntax, we only found it later.  

Am I missing something,  are those messages from SSMS not from the Sql server?

 

[mLipok]

9 hours ago, Humanzee said:

Msg 241, Level 16, State 1, Line 1
Conversion failed when converting date and/or time from character string.

 

9 hours ago, Humanzee said:

are these not com errors, or AutoIt.Error , or Events?  how do we get them. 

It was discussed some time ago.
Will check it again.

 

[mLipok]

Using this simple example:

Global $__g_oConnection = ObjCreate("ADODB.Connection")
Global $__g_oErrorHandler = ObjEvent("AutoIt.Error", _ErrFunc)

_Example()
Func _Example()
    Local $sPassword = 'AutoIt' ; change to your own pass
    Local $sInstance = 'localhost\SQLExpress' ; change to your own SQL server Instances
    $__g_oConnection.Open("PROVIDER=SQLOLEDB.1;SERVER=" & $sInstance & ";uid=sa;pwd=" & $sPassword & ";")
    Local $objquery = $__g_oConnection.Execute("print 'this is a test - hello - Line 1'")
    $objquery.parent2

    $__g_oConnection.Execute("SELECT CAST('0503\03\03' AS DATE)")
    #forceref $objquery

EndFunc   ;==>_Example

Func _ErrFunc(ByRef $oError)
    ConsoleWrite(@CRLF & _
            "! ==> COM Error intercepted ==>" & @CRLF & _
            "-" & @TAB & "$oError.description is: " & @TAB & $oError.description & @CRLF & _
            "-" & @TAB & "$oError.number is: " & @TAB & @TAB & $oError.number & "   in HEX is  0x" & Hex($oError.number) & @CRLF & _
            "-" & @TAB & "$oError.source is: " & @TAB & @TAB & $oError.source & @CRLF & _
            "-" & @TAB & "$oError.windescription:" & @TAB & @TAB & $oError.windescription & @CRLF & _
            "-" & @TAB & "$oError.helpfile is: " & @TAB & $oError.helpfile & @CRLF & _
            "-" & @TAB & "$oError.helpcontext is: " & @TAB & $oError.helpcontext & @CRLF & _
            "-" & @TAB & "$oError.lastdllerror is: " & @TAB & $oError.lastdllerror & @CRLF & _
            "-" & @TAB & "$oError.scriptline is: " & @TAB & @TAB & $oError.scriptline & @CRLF & _
            "-" & @TAB & "$oError.retcode is: " & @TAB & @TAB & "0x" & Hex($oError.retcode) & @CRLF & _
            "! ==> COM End of Error dump." & @CRLF & _
            @CRLF)
EndFunc   ;==>_ErrFunc

I get this:

Quote

! ==> COM Error intercepted ==>
-    $oError.description is:     Conversion failed when converting date and/or time from character string.
-    $oError.number is:         -2147352567   in HEX is  0x80020009
-    $oError.source is:         Microsoft OLE DB Provider for SQL Server
-    $oError.windescription:        Wyst¹pi³ wyj¹tek.

-    $oError.helpfile is:     
-    $oError.helpcontext is:     0
-    $oError.lastdllerror is:     0
-    $oError.scriptline is:         23
-    $oError.retcode is:         0x80040E07
! ==> COM End of Error dump.

Is it enough ? or you are expecting to get also:

Quote

Msg 241, Level 16, State 1, Line 1

?

 

[mLipok]

btw.

as to :

Quote

Msg 241, Level 16, State 1, Line 1

Msg 241 it is:

Quote

>    $oErr(0).NativeError is:     241

 

Edited January 25, 2023 by mLipok

[mLipok]

Take a look also here:

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/79a316c7-e112-42a3-aa11-24533098f8c5/msg-level-state-line?forum=transactsql

https://learn.microsoft.com/en-us/sql/relational-databases/errors-events/understanding-database-engine-errors?redirectedfrom=MSDN&view=sql-server-ver16

learn.microsoft.com/en-us/sql/relational-databases/errors-events/database-engine-error-severities?redirectedfrom=MSDN&view=sql-server-ver16

Quote

Levels of Severity

The following table lists and describes the severity levels of the errors raised by the SQL Server Database Engine.

Severity level	Description
0-9	Informational messages that return status information or report errors that are not severe. The Database Engine does not raise system errors with severities of 0 through 9.
10	Informational messages that return status information or report errors that are not severe. For compatibility reasons, the Database Engine converts severity 10 to severity 0 before returning the error information to the calling application.
11-16	Indicate errors that can be corrected by the user.
11	Indicates that the given object or entity does not exist.
12	A special severity for queries that do not use locking because of special query hints. In some cases, read operations performed by these statements could result in inconsistent data, since locks are not taken to guarantee consistency.
13	Indicates transaction deadlock errors.
14	Indicates security-related errors, such as permission denied.
15	Indicates syntax errors in the Transact-SQL command.
16	Indicates general errors that can be corrected by the user.
17-19	Indicate software errors that cannot be corrected by the user. Inform your system administrator of the problem.
17	Indicates that the statement caused SQL Server to run out of resources (such as memory, locks, or disk space for the database) or to exceed some limit set by the system administrator.
18	Indicates a problem in the Database Engine software, but the statement completes execution, and the connection to the instance of the Database Engine is maintained. The system administrator should be informed every time a message with a severity level of 18 occurs.
19	Indicates that a nonconfigurable Database Engine limit has been exceeded and the current batch process has been terminated. Error messages with a severity level of 19 or higher stop the execution of the current batch. Severity level 19 errors are rare and must be corrected by the system administrator or your primary support provider. Contact your system administrator when a message with a severity level 19 is raised. Error messages with a severity level from 19 through 25 are written to the error log.
20-24	Indicate system problems and are fatal errors, which means that the Database Engine task that is executing a statement or batch is no longer running. The task records information about what occurred and then terminates. In most cases, the application connection to the instance of the Database Engine may also terminate. If this happens, depending on the problem, the application might not be able to reconnect. Error messages in this range can affect all of the processes accessing data in the same database and may indicate that a database or object is damaged. Error messages with a severity level from 19 through 24 are written to the error log.
20	Indicates that a statement has encountered a problem. Because the problem has affected only the current task, it is unlikely that the database itself has been damaged.
21	Indicates that a problem has been encountered that affects all tasks in the current database, but it is unlikely that the database itself has been damaged.
22	Indicates that the table or index specified in the message has been damaged by a software or hardware problem. Severity level 22 errors occur rarely. If one occurs, run DBCC CHECKDB to determine whether other objects in the database are also damaged. The problem might be in the buffer cache only and not on the disk itself. If so, restarting the instance of the Database Engine corrects the problem. To continue working, you must reconnect to the instance of the Database Engine; otherwise, use DBCC to repair the problem. In some cases, you may have to restore the database. If restarting the instance of the Database Engine does not correct the problem, then the problem is on the disk. Sometimes destroying the object specified in the error message can solve the problem. For example, if the message reports that the instance of the Database Engine has found a row with a length of 0 in a nonclustered index, delete the index and rebuild it.
23	Indicates that the integrity of the entire database is in question because of a hardware or software problem. Severity level 23 errors occur rarely. If one occurs, run DBCC CHECKDB to determine the extent of the damage. The problem might be in the cache only and not on the disk itself. If so, restarting the instance of the Database Engine corrects the problem. To continue working, you must reconnect to the instance of the Database Engine; otherwise, use DBCC to repair the problem. In some cases, you may have to restore the database.
24	Indicates a media failure. The system administrator may have to restore the database. You may also have to call your hardware vendor.

 

Edited January 25, 2023 by mLipok

[mLipok]

Btw try with this:

BEGIN TRY  
    -- Generate error.  
    Select '',
    cast('0503\03\03' 
    as date)

END TRY  
BEGIN CATCH
    SELECT
        ERROR_NUMBER() AS ErrorNumber,  
        ERROR_SEVERITY() AS ErrorSeverity,  
        ERROR_STATE() AS ErrorState,  
        ERROR_PROCEDURE() AS ErrorProcedure,  
        ERROR_LINE() AS ErrorLine,  
        ERROR_MESSAGE() AS ErrorMessage;  
END CATCH;  
GO

I get such results:

Quote

ErrorNumber	ErrorSeverity	ErrorState	ErrorProcedure	ErrorLine	ErrorMessage
241	16	1	NULL	4	Conversion failed when converting date and/or time from character string.

 

Edited January 25, 2023 by mLipok

[Humanzee]

Your simple example works, yes.  As it turns out, my error handler in my project was working in the console, I just wasn't trapping the "Exception occurred" and presenting it to the user when compiled.

Because it wasn't being presented to the user, I assumed it wasn't being detected, and I started looking for solutions.  After not being able to get the _errfunc to trigger from within your  ADO_EXAMPLE.au3  using the same bad SQL statement, I asked the question.  So maybe it just doesn't work with my database and your ADO_EXAMPLE.au3

Editing the values within Func _Example_MSSQL_SQLServerAuthorization()  I ran

     _Example_1_RecordsetToConsole($sConnectionString, "Select top 1 ID from table1")
     _Example_1_RecordsetToConsole($sConnectionString, "if 1 = 1 print 'this is a test - hello'")
     _Example_1_RecordsetToConsole($sConnectionString, "Select top 1 ID from table2'")
    _Example_1_RecordsetToConsole($sConnectionString, "Select cast('0503\03\03' as date)")

In return I got

>> [ADO InfoMessage]=[Microsoft][ODBC SQL Server Driver][SQL Server]Changed database context to 'MY Database Name'.
==================================================================
["A legitimate value for ID from table 1"]
>> [ADO InfoMessage]=[Microsoft][ODBC SQL Server Driver][SQL Server]this is a test - hello
>> [ADO InfoMessage]=[Microsoft][ODBC SQL Server Driver][SQL Server]Changed database context to 'My Database Name'.
! ---> @error=2  @extended=-2147352567 : _Example_MSSQL_SQLServerAuthorization()

So maybe @error 2  is stopping the script before the __ADO_ComErrorHandler_WrapperFunction registered all over ADO.au3 picks it up and reports it.   Interesting though that query 1 & 2 worked and then the error 2 at query 3, even though its valid, and query 4 didn't appear to run 

If I just run the 3rd or 4th query  I get

>> [ADO InfoMessage]=[Microsoft][ODBC SQL Server Driver][SQL Server]Changed database context to 'My Database Name'.
! ---> @error=2  @extended=-2147352567 : _Example_MSSQL_SQLServerAuthorization()

So, I'll go and trap the error in my actual project code, and stop playing with trying to get your example to work.   I saw elsewhere that someone else had issue with SQL Server Authentication method and getting the server name syntax correct.    Odd that it produces date sometimes out of the example and sometimes error 2.

Thanks for the comprehensive reply.   

"Msg 241, Level 16, State 1, Line 1" Was not needed, thank you though,

The BEGIN CATCH is something I have not experimented with, however it returns two record sets.  I don't think QueryResults.MoveNext  just continue with the next query result set with out a UNION and the same number of fields returned ?

     _Example_1_RecordsetToConsole($sConnectionString, "Select 'A' ;Select 'B'")   Just returns "A"

 

[Humanzee]

Different question can you detect 

(0 rows affected)

from sql Messages where no records were updated.   e.g.   Update TableName set Field1 = 'Orange' where 1=2

This would be useful for confirming the number of records you expected would be updated.

 

[mLipok]

11 hours ago, Humanzee said:

Different question can you detect 

(0 rows affected)

from sql Messages where no records were updated.   e.g.   Update TableName set Field1 = 'Orange' where 1=2

This would be useful for confirming the number of records you expected would be updated.

 

  focus on:

; Name ..........: _ADO_Execute
; Description ...: Executes an SQL Query
.....
.....
.....
; Return values .: On Success - $oRecordset object or $aRecordsetAsArray and set @extended to __ADO_RowAffected()
;                  On Failure - $ADO_RET_FAILURE and set @error to $ADO_ERR_*
.....
.....
.....
    Return SetError($ADO_ERR_SUCCESS, __ADO_RowAffected(), $oRecordset)
EndFunc   ;==>_ADO_Execute

 

[mLipok]

17 hours ago, Humanzee said:

The BEGIN CATCH is something I have not experimented with, however it returns two record sets.  I don't think QueryResults.MoveNext  just continue with the next query result set with out a UNION and the same number of fields returned ?

     _Example_1_RecordsetToConsole($sConnectionString, "Select 'A' ;Select 'B'")   Just returns "A"

I just starts to read:

https://stackoverflow.com/questions/34662952/how-to-return-multiple-recordsets-in-a-single-execution-using-adodb

https://learn.microsoft.com/en-us/sql/ado/guide/data/receiving-multiple-recordsets?view=sql-server-ver16

 

Edited January 26, 2023 by mLipok

[mLipok]

as to your example:

_Example_1_RecordsetToConsole($sConnectionString, "Select 'A' ;Select 'B'")

I was able to do this with:

Local $iRecordsetAffected
    Local $o_recordset1 = $oADODB_Connection.Execute("Select 'A' ;Select 'B'")
    Local $array1 = $o_recordset1.GetRows()
    Local $o_recordset2 = $o_recordset1.NextRecordset($iRecordsetAffected)
    ConsoleWrite("- $iRecordsetAffected = " & $iRecordsetAffected & @CRLF)

    Local $array2 = $o_recordset2.GetRows()

    _ArrayDisplay($array1, '$array1')
    _ArrayDisplay($array2, '$array2')

but I got error using:

Local $sQuery = _
            "BEGIN TRY  " & @CRLF & _
            "    -- Generate error.  " & @CRLF & _
            "    Select ''," & @CRLF & _
            "    cast('0503\03\03' " & @CRLF & _
            "    as date)" & @CRLF & _
            "END TRY  " & @CRLF & _
            "BEGIN CATCH" & @CRLF & _
            "    SELECT" & @CRLF & _
            "        ERROR_NUMBER() AS ErrorNumber,  " & @CRLF & _
            "        ERROR_SEVERITY() AS ErrorSeverity,  " & @CRLF & _
            "        ERROR_STATE() AS ErrorState,  " & @CRLF & _
            "        ERROR_PROCEDURE() AS ErrorProcedure,  " & @CRLF & _
            "        ERROR_LINE() AS ErrorLine,  " & @CRLF & _
            "        ERROR_MESSAGE() AS ErrorMessage;  " & @CRLF & _
            "END CATCH;  " & @CRLF & _
            ""

    Local $o_recordset3 = $oADODB_Connection.Execute($sQuery)

    Local $array3 = $o_recordset3.GetRows()
    _ArrayDisplay($array3, '$array3')

    Local $o_recordset4 = $o_recordset1.NextRecordset($iRecordsetAffected)
    ConsoleWrite("- $iRecordsetAffected = " & $iRecordsetAffected & @CRLF)

    Local $array4 = $o_recordset4.GetRows()
    _ArrayDisplay($array4, '$array4')

Wil back to this ASAP

[mLipok]

my modified example (unfortunately it still doesn't work) :

expandcollapse popup

#include <array.au3>
;~ @Humanzee question from   ; https://www.autoitscript.com/forum/topic/203766-sql-ado-get-print-messages/?do=findComment&comment=1512399
;~ https://www.autoitscript.com/forum/topic/203766-sql-ado-get-print-messages/?do=findComment&comment=1512424
;~ https://stackoverflow.com/questions/66211062/ado-in-case-of-error-how-to-get-information-about-query-line-number

Global $oADODB_Connection = ObjCreate("ADODB.Connection")

_Example()
Func _Example()
    ; Error monitoring. This will trap all COM errors while alive.
    ; This particular object is declared as local, meaning after the function returns it will not exist.
    Local $oErrorHandler = ObjEvent("AutoIt.Error", _ErrFunc)
    Local $_EventHandler = ObjEvent($oADODB_Connection, "__ADO_EVENT__")
    #forceref $oErrorHandler, $_EventHandler

    ; put your SQL sa password here
    Local $sPassword = 'AutoIt'

    $oADODB_Connection.Open("PROVIDER=SQLOLEDB.1;SERVER=localhost\SQLExpress;uid=sa;pwd=" & $sPassword & ";")
;~  $oADODB_Connection.Open("DRIVER=SQL Server;SERVER=localhost\SQLExpress;uid=sa;pwd=" & $sPassword & ";")
;~  $oADODB_Connection.Open("PROVIDER=SQL Server Native Client 11.0;SERVER=localhost\SQLExpress;uid=sa;pwd=" & $sPassword & ";")

    $oADODB_Connection.Execute("IF 1 = 1 PRINT 'this is a test - hello'")

    #Region - @Humanzee question

    Local $o_recordset1 = $oADODB_Connection.Execute("Select 'A';" & @CRLF & "Select 'B';")
    Local $array1 = $o_recordset1.GetRows()

    Local $iRecordsetAffected
    Local $o_recordset2 = $o_recordset1.NextRecordset($iRecordsetAffected)
    ConsoleWrite("- $iRecordsetAffected = " & $iRecordsetAffected & @CRLF)
    Local $array2 = $o_recordset2.GetRows()

    _ArrayDisplay($array1, '$array1')
    _ArrayDisplay($array2, '$array2')

    ; final test
    Local $sQuery = _
            "BEGIN TRY" & @CRLF & _
            "    -- Generate error." & @CRLF & _
            "    SELECT ''," & @CRLF & _
            "    CAST('0503\03\03' AS DATE)" & @CRLF & _
            "END TRY" & @CRLF & _
            "BEGIN CATCH" & @CRLF & _
            "    SELECT" & @CRLF & _
            "        ERROR_NUMBER() AS ErrorNumber," & @CRLF & _
            "        ERROR_SEVERITY() AS ErrorSeverity," & @CRLF & _
            "        ERROR_STATE() AS ErrorState," & @CRLF & _
            "        ERROR_PROCEDURE() AS ErrorProcedure," & @CRLF & _
            "        ERROR_LINE() AS ErrorLine," & @CRLF & _
            "        ERROR_MESSAGE() AS ErrorMessage;" & @CRLF & _
            "END CATCH;" & @CRLF & _
            ""

    Local $o_recordset3 = $oADODB_Connection.Execute($sQuery)
    #forceref $o_recordset3

    #Region ; this following part will throw error as the query never ends succesfully
;~  Local $array3 = $o_recordset3.GetRows()
;~  _ArrayDisplay($array3, '$array3')

;~  Local $o_recordset4 = $o_recordset1.NextRecordset($iRecordsetAffected)
;~  ConsoleWrite("- $iRecordsetAffected = " & $iRecordsetAffected & @CRLF)

;~  Local $array4 = $o_recordset4.GetRows()
;~  _ArrayDisplay($array4, '$array4')
    #EndRegion ; this following part will throw error as the query never ends succesfully

    #EndRegion - @Humanzee question

EndFunc   ;==>_Example


; User's COM error function. Will be called if COM error occurs
Func _ErrFunc($oError)
    Local $iErrorCol_Max = $oADODB_Connection.errors.Count
    If $iErrorCol_Max = 0 Then
        ConsoleWrite(@CRLF & _
                "! ==> COM Error intercepted ==> NOT ADO related Error " & @CRLF & _
                "-" & @TAB & "$oError.description is: " & @TAB & $oError.description & @CRLF & _
                "-" & @TAB & "$oError.number is: " & @TAB & @TAB & $oError.number & "   in HEX is  0x" & Hex($oError.number) & @CRLF & _
                "-" & @TAB & "$oError.source is: " & @TAB & @TAB & $oError.source & @CRLF & _
                "-" & @TAB & "$oError.windescription:" & @TAB & @TAB & $oError.windescription & @CRLF & _
                "-" & @TAB & "$oError.helpfile is: " & @TAB & $oError.helpfile & @CRLF & _
                "-" & @TAB & "$oError.helpcontext is: " & @TAB & $oError.helpcontext & @CRLF & _
                "-" & @TAB & "$oError.lastdllerror is: " & @TAB & $oError.lastdllerror & @CRLF & _
                "-" & @TAB & "$oError.scriptline is: " & @TAB & @TAB & $oError.scriptline & @CRLF & _
                "-" & @TAB & "$oError.retcode is: " & @TAB & @TAB & "0x" & Hex($oError.retcode) & @CRLF & _
                "! ==> COM End of Error dump." & @CRLF & _
                @CRLF)

    Else
        Local $oErr
        For $iErrorCol_idx = 0 To $iErrorCol_Max - 1
            If $iErrorCol_idx = 0 Then ConsoleWrite(@CRLF & _
                    "! ==> COM Error intercepted ==> ADO Error Collection: .errors.Count = " & $iErrorCol_Max & @CRLF & _
                    "")
            $oErr = $oADODB_Connection.errors.Item($iErrorCol_idx)
            ConsoleWrite(@CRLF & _
                    ">" & @TAB & "$oErr(" & $iErrorCol_idx & ").description is: " & @TAB & $oErr.description & @CRLF & _
                    ">" & @TAB & "$oErr(" & $iErrorCol_idx & ").number is: " & @TAB & @TAB & $oError.number & "   in HEX is  0x" & Hex($oErr.number) & @CRLF & _
                    ">" & @TAB & "$oErr(" & $iErrorCol_idx & ").source is: " & @TAB & @TAB & $oErr.source & @CRLF & _
                    ">" & @TAB & "$oErr(" & $iErrorCol_idx & ").SQLState is: " & @TAB & @TAB & $oErr.SQLState & @CRLF & _
                    ">" & @TAB & "$oErr(" & $iErrorCol_idx & ").NativeError is: " & @TAB & $oErr.NativeError & @CRLF & _
                    "-" & @TAB & "$oError.windescription:" & @TAB & @TAB & $oError.windescription & @CRLF & _
                    "-" & @TAB & "$oError.scriptline is: " & @TAB & @TAB & $oError.scriptline & @CRLF & _
                    "-" & @TAB & "$oError.helpfile is: " & @TAB & @TAB & $oError.helpfile & @CRLF & _
                    "-" & @TAB & "$oError.helpcontext is: " & @TAB & $oError.helpcontext & @CRLF & _
                    "-" & @TAB & "$oError.lastdllerror is: " & @TAB & $oError.lastdllerror & @CRLF & _
                    "-" & @TAB & "$oError.retcode is: " & @TAB & @TAB & "0x" & Hex($oError.retcode) & @CRLF & _
                    "-" & @TAB & "$oError.number is: " & @TAB & @TAB & "0x" & Hex($oError.number) & @CRLF & _
                    "")
        Next
        $oADODB_Connection.errors.clear
        ConsoleWrite("! ==> COM End of Error Collections dump." & @CRLF)
    EndIf
EndFunc   ;==>_ErrFunc


; #INTERNAL_USE_ONLY# ===========================================================================================================
; Name ..........: __ADO_EVENT__InfoMessage
; Description ...:
; Syntax ........: __ADO_EVENT__InfoMessage(Byref $oError, $i_adStatus, Byref $oConnection)
; Parameters ....: $oError              - [in/out] an object.
;                  $i_adStatus          - an integer value.
;                  $oConnection         - [in/out] an object representing ADO Connection.
; Return values .: None
; Author ........: mLipok
; Modified ......:
; Remarks .......: TODO - description
; Related .......:
; Link ..........: https://msdn.microsoft.com/en-us/library/windows/desktop/ms675859(v=vs.85).aspx
; Example .......: No
; ===============================================================================================================================
Func __ADO_EVENT__InfoMessage(ByRef $oError, $i_adStatus, ByRef $oConnection)
    #forceref $i_adStatus, $oConnection

;~  Local $oConnection_param1 = $oConnection, $oCommand_param2 = Null, $oRecordset_param3 = Null, $oError_param4 = $oError
;~  ConsoleWrite('InfoMessage', $oConnection_param1, $oCommand_param2, $oRecordset_param3, $oError_param4, $i_adStatus)

    If @Compiled Then Return
    ConsoleWrite(' [ADO InfoMessage]=' & $oError.description & @CRLF)
EndFunc   ;==>__ADO_EVENT__InfoMessage

 

The main issue is that this following code is not showing SQL QUERY error line :

Local $sQuery = _
            "BEGIN TRY" & @CRLF & _
            "    -- Generate error." & @CRLF & _
            "    SELECT ''," & @CRLF & _
            "    CAST('0503\03\03' AS DATE)" & @CRLF & _
            "END TRY" & @CRLF & _
            "BEGIN CATCH" & @CRLF & _
            "    SELECT" & @CRLF & _
            "        ERROR_NUMBER() AS ErrorNumber," & @CRLF & _
            "        ERROR_SEVERITY() AS ErrorSeverity," & @CRLF & _
            "        ERROR_STATE() AS ErrorState," & @CRLF & _
            "        ERROR_PROCEDURE() AS ErrorProcedure," & @CRLF & _
            "        ERROR_LINE() AS ErrorLine," & @CRLF & _
            "        ERROR_MESSAGE() AS ErrorMessage;" & @CRLF & _
            "END CATCH;" & @CRLF & _
            ""

    Local $o_recordset3 = $oADODB_Connection.Execute($sQuery)

 

 

Edited May 13 by mLipok