Sql-server – Tracking errors from prepared SQL statements

prepared-statementsql serversql-server-2016

I use extended events to store database errors, like this:

CREATE EVENT SESSION [ErrorCapture] 
ON SERVER 
ADD EVENT sqlserver.error_reported
(
    ACTION
    (
        sqlserver.client_hostname,
        sqlserver.database_id,
        sqlserver.sql_text,
        sqlserver.username
    )
    WHERE 
    (
        [severity] >= (11)
    )
) 
ADD TARGET package0.asynchronous_file_target
(
    SET filename='J:\ServerXmlOutput\ErrorCapture.xel'
)
WITH 
(
    MAX_MEMORY=4096 KB,
    EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,
    MAX_DISPATCH_LATENCY=10 SECONDS,
    MAX_EVENT_SIZE=0 KB,
    MEMORY_PARTITION_MODE=NONE,
    TRACK_CAUSALITY=OFF,
    STARTUP_STATE=ON
);

This helps me to easily trace which application is causing them.
I have something similar to track slow queries and other special cases.
However, I noticed from the SQL Server Profiles that a PHP/Laravel application is not sending some statements directly, but using RPC:Completed: they look like prepared statements, receiving the argument at run time.
How do I expand my ErrorCapture to include errors caused by statements executed via RPC:Completed?

Best Answer

The code you have does return errors from prepared statements. The below PowerShell executes a prepared statement against SQL Server that will intentionally fail. This appears in Profiler with RPC:Completed as you've described:

$SqlConn = [System.Data.SqlClient.SqlConnection]::New("Server=localhost;Database=master;Trusted_connection=true;")

$SqlConn.Open()

$SqlCmd = [System.Data.SqlClient.SqlCommand]::New($null, $SqlConn)

$SqlCmd.CommandText = "SELECT @Val/0"

$idParam = [System.Data.SqlClient.SqlParameter]::New("@Val", [System.Data.SqlDbType]::Int, 0);
$idParam.Value = 1;
$SqlCmd.Parameters.Add($idParam);

$SqlCmd.Prepare()
$SqlCmd.ExecuteNonQuery()

$SqlConn.Close()

When I run this with Profiler and your Extended Events session running, I can see the error reported in both tools:

Extended Events:

Extended Events

Profiler:

Profiler

One issue may be that the messages are below severity 11, and so your EE session is excluding them. If you run a Profiler trace using the TSQL_Replay template and include "User Error Messages" under Errors and Warnings do your errors from prepared statements appear then?

If so, check the error column in Profiler and run the below against SQL Server replacing 8134 with the error number from Profiler:

SELECT * FROM sys.messages WHERE message_id = 8134

Check the severity column in that to identify the severity of the error returned by your prepared statements.

Related Question