Solved

xevent trace error Error converting data type varchar to varbinary.

Posted on 2016-09-22
4
77 Views
Last Modified: 2016-11-14
Hello,

When I execute
IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE name='Trace')
    DROP EVENT SESSION [Trace] ON SERVER;
    
    
CREATE EVENT SESSION [Trace]
ON SERVER
ADD EVENT sqlserver.sp_statement_completed
      (
     ACTION (
                        sqlserver.client_app_name, 
                        sqlserver.client_hostname, 
                        sqlserver.database_id, 
                        sqlserver.nt_username,
                        sqlserver.username,
                        sqlserver.plan_handle, 
                        sqlserver.sql_text
            )
      
      --WHERE (reads >= 512 OR duration >= 1000)
      WHERE duration > 1000
      )
ADD TARGET package0.asynchronous_file_target
      (
     SET filename='c:\temp\Trace.xet', metadatafile='c:\temp\Trace.xem'
    )
WITH (      MAX_MEMORY = 4096KB, 
            EVENT_RETENTION_MODE = ALLOW_MULTIPLE_EVENT_LOSS, 
            MAX_DISPATCH_LATENCY = 30 SECONDS, 
            MAX_EVENT_SIZE = 0KB, 
            MEMORY_PARTITION_MODE = NONE, 
            TRACK_CAUSALITY = OFF, 
            STARTUP_STATE = OFF
      )
Go
      
-- Enable Event
ALTER EVENT SESSION Trace ON SERVER
STATE=START
GO


-- Stop the event
ALTER EVENT SESSION Trace ON SERVER
STATE=STOP
GO

      
      
-- Script to query the target file
SELECT 
event_data_XML.value('(/event[@name=''sql_statement_completed'']/@timestamp)[1]','datetime') time,
event_data_XML.value('(event/data[2])[1]','INT')                 AS OBJECT_ID,
event_data_XML.value('(event/data[3])[1]','INT')                 AS object_type,
event_data_XML.value('(event/data[4])[1]','INT')                 AS cpu,
event_data_XML.value('(event/data[5])[1]','INT')                 AS duration,
event_data_XML.value('(event/data[6])[1]','INT')                 AS reads,
event_data_XML.value('(event/data[7])[1]','INT')                 AS writes,
event_data_XML.value('(event/action[1])[1]','VARCHAR(255)') AS client_app_name, 
event_data_XML.value('(event/action[2])[1]','VARCHAR(255)') AS client_hostname, 
event_data_XML.value('(event/action[3])[1]','INT')          AS database_id, 
event_data_XML.value('(event/action[4])[1]','VARCHAR(255)') AS nt_username,
event_data_XML.value('(event/action[5])[1]','VARCHAR(255)') AS username,
event_data_XML.value('(event/action[7])[1]','VARCHAR(255)') AS sql_text,
qp.query_plan 
FROM
(
SELECT CAST(event_data AS XML) event_data_XML, *
FROM sys.fn_xe_file_target_read_file
('c:\Temp\Trace*.xet',
'c:\Temp\Trace*.xem',
NULL, NULL)) T
CROSS APPLY sys.dm_exec_query_plan ( CONVERT(varbinary,SUBSTRING(event_data_XML.value('(event/action[6])[1]','VARCHAR(255)'),15,LEN(event_data_XML.value('(event/action[6])[1]','VARCHAR(255)'))-17),1) ) qp
WHERE qp.objectid = '1872725724'
Go

Open in new window


The following error is generated :
Error converting Data type varchar to varbinary is generated.

Why ?

Thanks

Regards
0
Comment
Question by:bibi92
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
4 Comments
 
LVL 11

Accepted Solution

by:
CraigYellick earned 250 total points (awarded by participants)
ID: 41811242
Must be the call to sys.dm_exec_query_plan, it expects a varbinary(64) plan handle and that expression must be producing something else.

Store the result of the CONVERT(varbinary,SUBSTRING(event_data_XML.value( ... ) expression to a variable and print it out, see what it yields.
0
 
LVL 51

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 250 total points (awarded by participants)
ID: 41840409
Review the event_data_XML.value lines. One of them must be varbinary and not varchar.
0
 
LVL 51

Expert Comment

by:Vitor Montalvão
ID: 41861674
bibi92, a feedback will be appreciated.
Cheers
0
 
LVL 51

Expert Comment

by:Vitor Montalvão
ID: 41886021
Recommendation to close this question by accepting the above comments as solutions.
0

Featured Post

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

636 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question