Capture passed parameter values in SQL Server extended sessions?

Question

I've set up SQL Server extended events to catch the last 1000 failed queries in a specific database into a ring buffer:

Create Event Session [Errors] on Server
Add Event sqlserver.error_reported (
    Action(
        sqlos.task_time,
        sqlserver.sql_text
    )
    Where sqlserver.database_name=N'MyDatabase'
    And error_number<>5701 -- Ignore changed db context messages
)
Add target package0.ring_buffer(Set max_memory=102400)
With (
    MAX_MEMORY=4096 KB,
    EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,
    MAX_DISPATCH_LATENCY=30 SECONDS,
    MAX_EVENT_SIZE=0 KB,
    MEMORY_PARTITION_MODE=NONE,
    TRACK_CAUSALITY=OFF,
    STARTUP_STATE=ON
)
Go

I can now query these errors like so:

Select 
    xmldata = Cast(xet.target_data as xml) 
Into #rbd
From sys.dm_xe_session_targets xet 
Join sys.dm_xe_sessions xe ON (xe.address = xet.event_session_address)
Where xe.name='errors' and target_name='ring_buffer'

; With Errors as (
    Select 
        e.query('.').value('(/event/@timestamp)[1]', 'datetime') as "TimeStamp",
        e.query('.').value('(/event/data[@name="message"]/value)[1]', 'nvarchar(max)') as "Message",
        e.query('.').value('(/event/action[@name="sql_text"]/value)[1]', 'nvarchar(max)') as "SqlText",
        e.query('.').value('(/event/action[@name="task_time"]/value)[1]', 'bigint')/@@TIMETICKS  as "Duration"
    From #rbd
    Cross Apply XMLData.nodes('/RingBufferTarget/event') as Event(e)
)
Select * from Errors
Where SqlText is not null

Drop Table #rbd

So far, so good:

This has already been a big help in tracking down more detail on errors that get thrown at the application level.

What would make it even more useful, though, would be if we could see the parameter values being passed to these queries, the way you can in SQL Profiler. So when we see an error like this...

Conversion failed when converting date and/or time from character string.   

for this SQL...

(@NewValue nvarchar(10),@KeyValue int)
Update SomeTable 
Set SomeField=@NewValue Where SomeTableID=@KeyValue

... it would be informative to know what the values of the @NewValue and @KeyValue parameters were.

Any ideas where I might find this information?

Answer 1

I think only sqlserver.rpc_completed event will give you complete SQL command including parameter values.