Sql-server – why would command text not execute at all

sql serverssrs

in my report i have::

<CommandText>SELECT 

    column1,
        column2, 
        'poop'
from mytable
</CommandText>

i am using sql server profiler to see exactly what statement is being set.

i have set only two filters:

  1. databaseName
  2. enter image description here

yet after running the report, no statement gets intercepted.

i suspect that because i am a beginner at SSRS, i am missing something crucial here.

for what reason would commandtext not be executed at all?

i did follow this question, to make sure that i am using sql profiler correctly, and indeed, i am: https://stackoverflow.com/questions/9107383/sql-server-profiler-capture-calls-to-your-databases-stored-procs-during-ssrs

another bit of important information is although the chart shows no data:

enter image description here

i actually am indeed showing data when i run the commandtext from ssms!

Best Answer

Based on past interactions, I'm pretty sure that you're at least on SQL Server 2008R2. You could try filtering on the output of an Extended Event session. Here's a basic one to get you started.

  CREATE EVENT SESSION query_check ON SERVER
    ADD EVENT sqlserver.sql_statement_starting(
        ACTION (sqlserver.database_id, sqlserver.sql_text)),
    ADD EVENT sqlserver.sql_statement_completed(
        ACTION (sqlserver.database_id, sqlserver.sql_text)),
    ADD EVENT sqlserver.sp_statement_starting(
        ACTION (sqlserver.database_id, sqlserver.sql_text)),
    ADD EVENT sqlserver.sp_statement_completed(
        ACTION (sqlserver.database_id, sqlserver.sql_text))
    ADD TARGET package0.asynchronous_file_target(SET filename=N'D:\temp\monitor.xel',max_file_size=(5),max_rollover_files=(4))
    --,ADD TARGET package0.ring_buffer(SET max_events_limit=(5000),max_memory=(4096))
    WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=120 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=ON);

You can start it like this:

ALTER EVENT SESSION query_check ON SERVER STATE=START;

Run your query a few times and then stop the event session:

ALTER EVENT SESSION query_check ON SERVER STATE=STOP;

Then you can query it like this:

SELECT  
    fired_event = event_data.value('(/event/@name)[1]','nvarchar(25)'),
    fired_event_time = event_data.value('(/event/@timestamp)[1]','datetime2(0)'),
    event_database_id = event_data.value('(/event/action[@name=''database_id''])[1]','int'),
    event_sql_text = event_data.value('(/event/action[@name=''sql_text''])[1]','nvarchar(max)')  
FROM (
        SELECT CAST(event_data AS XML) AS event_data
        FROM sys.fn_xe_file_target_read_file(N'D:\temp\monitor*.xel', 
                                            N'D:\temp\monitor*.xem', 
                                            NULL, NULL)
    ) events

You can capture more data, you can choose what you'd like from here:

SELECT p.name AS package_name,
       o.name AS action_name,
       o.description
FROM sys.dm_xe_packages AS p
JOIN sys.dm_xe_objects AS o 
     ON p.guid = o.package_guid
WHERE (p.capabilities IS NULL OR p.capabilities & 1 = 0)
  AND (o.capabilities IS NULL OR o.capabilities & 1 = 0)
  AND o.object_type = 'action'

So if you wanted to capture login information, you'd modify your ACTIONs from

ACTION (sqlserver.database_id, sqlserver.sql_text)),

to

ACTION (sqlserver.database_id, sqlserver.sql_text, sqlserver.username)),

I'd actually recommend against filtering on the query text because it's expensive. Instead, I'd try to find the correct username. If you don't think this will work, then I'd filter based on the query text of the result XML file. In fact, only in SQL Server 2012 can you filter on the statement text anyway (it's called a predicate).

If you wanted to perhaps capture data from a specific username you'd add WHERE to each EVENT that you'd like to filter. 

ADD EVENT sqlserver.sql_statement_starting(
    ACTION (sqlserver.database_id, sqlserver.sql_text)
    WHERE (sqlserver.username = N'{{ your username here }}')
)
Related Question