sql-server – 捕获在SQL Server扩展会话中传递的参数值?

前端之家收集整理的这篇文章主要介绍了sql-server – 捕获在SQL Server扩展会话中传递的参数值?前端之家小编觉得挺不错的,现在分享给大家,也给大家做个参考。
我已经设置了sql Server扩展事件,以将特定数据库中的最后1000个失败查询捕获到环形缓冲区中:
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

我现在可以这样查询这些错误

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

到现在为止还挺好:

跟踪应用程序级别引发的错误的更多细节已经有了很大的帮助.

但是,如果我们可以看到参数值被传递给这些查询,那么在sql Profiler中可以使用的方法更有用.所以当我们看到这样的错误

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

对于这个sql

(@NewValue nvarchar(10),@KeyValue int)
Update SoMetable 
Set SomeField=@NewValue Where SoMetableID=@KeyValue

…知道@NewValue和@KeyValue参数的值是有用的.

有什么想法可以找到这些信息?

解决方法

我认为只有sqlserver.rpc_completed事件会给你完整的sql命令,包括参数值.

猜你在找的MsSQL相关文章