6

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: enter image description here

nam
  • 15,516
  • 23
  • 104
  • 228
  • 1
    Do you track `SP:StmtCompleted`? My guess is with component it uses `sp_executesql` instead of simple `select` – Lukasz Szozda Nov 06 '15 at 16:38
  • @lad2025 Yes, I am using `SP:StmtCompleted` as explained [here](https://www.petefreitag.com/item/674.cfm) – nam Nov 06 '15 at 16:41
  • 6
    `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. – Greg Nov 06 '15 at 17:20
  • 2
    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. – Leigh Nov 06 '15 at 17:38
  • @Greg I've just added an `Update` section in my post showing the events I am capturing including TSQL. – nam Nov 06 '15 at 18:20
  • 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. – Mark A Kruger Nov 06 '15 at 18:52
  • 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. – Mark A Kruger Nov 06 '15 at 18:54
  • @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. – nam Nov 10 '15 at 21:07
  • Hmmm... that is a mystery. Any chance you are setting the datasource differently somewhere - perhaps your CFC folder has it's own application.cfc? – Mark A Kruger Nov 13 '15 at 17:43
  • I'm with @Greg 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. https://www.mssqltips.com/sqlservertip/2040/use-sql-server-profiler-to-trace-database-calls-from-third-party-applications/ – cameront Feb 29 '16 at 07:14

1 Answers1

0

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

Leigh
  • 28,424
  • 10
  • 49
  • 96
Paul Wehland
  • 104
  • 6