SQL Server Profiler not showing SQL statements embedded in ColdFusion components

Question

In our ColdFusion 11 app with SQL Server 2008 R2. A .cfm file is using a CF component that is called with a syntax such as:

<cfscript>
    objMyTable = createobject('component','components.myTable);
    qmyTable2list = objMyTable.loadAllCustomers();
        qresultset = objMyTable.loadCustomersResultset(form.applicant_type, form.customer_name, form.state, form.orderby);
</cfscript>

The CF component has SQL Statements such as the one shown below:

<cfquery name="result_set" dataSource="#request.dsn_name#">
        select name, state from myTable #REReplace(where_clause,"''","'","ALL")#        
</cfquery>

When we click a button on .cfm page, the app correctly displays data from SQL Server 2008-R2. But SQL Profiler does not catch these SQL statements. When we run the profiler on .cfm pages that are using embedded cfquery tags for sql queries (instead of CF components) the SQL profiler displays all the queries executed by the page. Question: How can we make SQL Profiler catch the queries inside the CF component?

UPDATE I've following events selected in the profiler:

Gisarme

Do you track SP:StmtCompleted? My guess is with component it uses sp_executesql instead of simple select – Biome

@lad2025 Yes, I am using SP:StmtCompleted as explained here – Gisarme

SP:* is for stored procs, the snippet above doesn't look like a stored proc. Ensure you right-click on TSQL object, and select all. – Tallent

Good tip. When you are not 100% sure what type of statements to capture, it is good to start with select all. Then filter out the ones you do not need afterward. – Smaragd

@Tallent I've just added an Update section in my post showing the events I am capturing including TSQL. – Gisarme

Nam - it should capture them. - what are your filters like? are you profiling using "sa" or some other username? I use profiler pretty routinely and I can tell you it should be capturing your JDBC queries for sure. – Statfarad

As @greg and leigh said - start with ALL TSQL objects first ... verify you are capturing queries, then remove stuff you don't think you need. – Statfarad

@MarkAKruger Per Greg's and your suggestion, I tried with all TSQL objects as shown in my updated picture above. But still no luck. I've also verified that there is NO filter that I'm using. The same profiler settings as shown above work fine when the queries are in a .cfm file instead of a .cfc file. – Gisarme

Hmmm... that is a mystery. Any chance you are setting the datasource differently somewhere - perhaps your CFC folder has it's own application.cfc? – Statfarad

I'm with @Tallent turn on all events under the Stored Procedures node as well. You should at least capture these events. RPC:Completed, SP:StmtCompleted and SQL:BatchCompleted. Here is a good post for you. mssqltips.com/sqlservertip/2040/… – Incantation

Answer 1

Capture the "RPC:Completed" and/or "RPC:Starting" events, and you will see your ColdFusion queries.

Senile