Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

in my report i have::


from mytable

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:

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!

share|improve this question

migrated from Mar 25 '13 at 10:41

This question came from our site for professional and enthusiast programmers.

@marc_s i definitely do make stupid mistakes, but thankfully not with this! (not this time at least) – l--''''''---------'''''''''''' Mar 25 '13 at 5:52
Just wanted to be sure - sometimes it's the most basic, most silly things that stop us :-) – marc_s Mar 25 '13 at 5:53
please definitely feel free to ask about more of those basic things, i wouldnt be surprised if it's something dumb – l--''''''---------'''''''''''' Mar 25 '13 at 5:56
assuming you had set up the events correctly, try executing the same query using SQLMS and see if it is traceable. – GayanSanjeewa Mar 25 '13 at 5:57
what is sqlms?? – l--''''''---------'''''''''''' Mar 25 '13 at 5:59

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.

    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))

You can start it like this:


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


Then you can query it like this:

    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)')  
        SELECT CAST(event_data AS XML) AS event_data
        FROM sys.fn_xe_file_target_read_file(N'D:\temp\monitor*.xel', 
                                            NULL, NULL)
    ) events 

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

SELECT AS package_name, AS action_name,
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)),


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 }}')
share|improve this answer

The table name or from-clause looks illegal:

from my table

That space character is either ill-placed, or you should separate your table-names with a comma (from my, table) if you have two tables named my and table.

share|improve this answer
thanks for catching that. it was only an error in the question, not in my actual work – l--''''''---------'''''''''''' Mar 25 '13 at 17:01

This sounds like an issue with the report design, not the query; I would try building a new report from scratch, using the wizards and text output to verify, then add the graph to the report.

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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