February 8, 2013 at 3:29 pm
Hi,
I am trying to collect some DMV data using Data Collector. I am using the SQL statement listed below:
select convert(varchar(19),getdate(),121) as ctime,
er.session_id as session_id,
ses.login_name as login_name,
CASE er.transaction_isolation_level
WHEN null THEN 'UNSPECIFIED'
WHEN 0 THEN 'UNSPECIFIED'
WHEN 1 THEN 'READ_UNCOMMITTED'
WHEN 2 THEN 'READ_COMMITTED'
WHEN 3 THEN 'REPEATABLE'
WHEN 4 THEN 'SERIALIZABLE'
WHEN 5 THEN 'SNAPSHOT'
END as transaction_level,
er.lock_timeout as lock_timeout,
er.scheduler_id as scheduler_id,
er.status as er_status,
er.command as command,
er.blocking_session_id as blocking_session_id,
er.wait_type as wait_type,
er.wait_time as wait_time,
er.last_wait_type as last_wait_type,
DB_Name(er.database_id) as dbname ,
OBJECT_SCHEMA_NAME(qt.objectid,dbid) + '.' + OBJECT_NAME(qt.objectid, qt.dbid) as object_name,
er.total_elapsed_time as total_elapsed_time,
er.cpu_time as cpu_time,
er.reads as reads,
er.logical_reads as logical_reads,
er.writes as writes,
er.row_count as row_count,
convert(varchar(26),er.start_time,121) as start_time,
SUBSTRING
(cast(qt.text as varchar(max)),
er.statement_start_offset/2,
(CASE WHEN er.statement_end_offset = -1
THEN LEN(CONVERT(nvarchar(MAX), qt.text)) * 2
ELSE er.statement_end_offset
END - er.statement_start_offset)/2
) as sql_text ,
er.request_id as request_id,
ses.host_name as host_name,
er.transaction_isolation_level as transaction_isolation_level, etc...
FROM sys.dm_exec_requests er
LEFT JOIN sys.dm_exec_sessions ses
ON ses.session_id = er.session_id
OUTER APPLY sys.dm_exec_sql_text(er.sql_handle) as qt
The SQL is incomplete ( as it collects a few extra fields). The statement is incoroporated into a SP and SP is executed by the Data collector process. Out of all the fileds collected, the following one:
SUBSTRING
(cast(qt.text as varchar(max)),
er.statement_start_offset/2,
(CASE WHEN er.statement_end_offset = -1
THEN LEN(CONVERT(nvarchar(MAX), qt.text)) * 2
ELSE er.statement_end_offset
END - er.statement_start_offset)/2
) as sql_text
is not accepted. The error messages are:
Message
The %1 has forbidden the requested use of the input column with lineage ID %2!d!.
Message
The column sql_text cannot be used. The raw adapters do not support image, text, or ntext data.
I tried to different conversion methods and even tried to select the result into a temp table and the select * from #my_temp table. Still didn't work.
If I exclude this field (sql_text) all other fields are collected properly.
Does anyone know a workaround for this issue?
Thank you,
Liviu
February 8, 2013 at 3:37 pm
Error Message reads:
The column sql_text cannot be used. The raw adapters do not support image, text, or ntext data.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.February 8, 2013 at 4:12 pm
I noticed what the message says, But if you look at the sql_text column you can see it is converted to a varchar:
SUBSTRING
(cast(qt.text as varchar(max)),
er.statement_start_offset/2,
(CASE WHEN er.statement_end_offset = -1
THEN LEN(CONVERT(nvarchar(MAX), qt.text)) * 2
ELSE er.statement_end_offset
END - er.statement_start_offset)/2
) as sql_text ,
So I am not sending it as a text anymore. What kind of conversion should I use in order for the data collector to accept it?
Tx, Liviu
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply