I have this XQuery for my extended event session which works fine but when I try joining the plan_handle to dm_exec_query_plan to get the query plan, it says

Msg 257, Level 16, State 3, Line 1 Implicit conversion from data type varchar to varbinary is not allowed. Use the CONVERT function to run this query.

If I convert to varbinary, this is the error I receive.

"Msg 569, Level 16, State 5, Line 1 The handle that was passed to dm_exec_query_plan was invalid. "

If I manually pass the handle into the DMF, it works just fine. This is how it looks like in varchar format (after adding the 0x)


        ,q.[CPU (ms)]
        ,q.[Duration (s)]
        ,q.[SQL Statement]
        data.value(     '(/event[@name="sql_statement_completed"]/@timestamp)[1]', 'DATETIME') AS [Time]
        ,data.value(        '(/event/data[@name=''cpu'']/value)[1]', 'INT') AS [CPU (ms)]
        ,CONVERT(float, data.value('(/event/data[@name="duration"]/value)[1]', 'BIGINT')) / 1000000
        AS [Duration (s)]
        '(/event/action[@name=''sql_text'']/value)[1]', 'VARCHAR(MAX)') AS [SQL Statement]
        --,data.value('(/event/action[@name=''plan_handle'']/value)[1]', 'varchar(4000)')       AS [PlanHandle]
        ,'0x' + CAST(data.value('(/event/action[@name="plan_handle"]/value)[1]', 'varchar(4000)') AS varchar(4000)) AS plan_handle
    FROM (
            CONVERT(xml, event_data) AS data
        FROM sys.fn_xe_file_target_read_file
        ('H:\SQLskills\EE_ExpensiveQueries*.xel', 'H:\SQLskills\EE_ExpensiveQueries*.xem', NULL, NULL)
    ) entries
    CROSS APPLY sys.dm_exec_query_plan(plan_handle)
  • Yes, that's it. I discovered it just a minute ago coincidentally. This can be closed or whatever needs to be done. Thank you. – Gabe Jun 26 '15 at 17:14

This is still a string, regardless of how it looks to you when you copy the output:

'0x' + CAST(...) AS varchar(4000)) AS plan_handle

Try using a style parameter on the CONVERT, so that the string isn't converted to a binary representation of the string, but rather converted to the string's literal binary value:

CONVERT(VARBINARY(32), '0x' + CAST(...) AS varchar(4000), 1) AS plan_handle

Not the answer you're looking for? Browse other questions tagged or ask your own question.