Analyzing SELECT statements using SQL Server Profiler

I recently had an issue when trying to create a new integration for the Dynamics Connector between Dynamics AX 2012 and CRM.  The Connector integration would run, but no records were returned from the AX service that I had created for the integration.  I needed to be able to see the SELECT statement that was being created when the Connector consumed the AX service.  So I turned to the SQL Server Profiler to find what was being generated by the Connector to help in debugging the cause. If you’re sick of dealing with the Dynamics Connector, maybe think about upgrading to Dynamics 365 for Finance and Operations and Dynamics 365 for Sales.  Click here to learn more. These steps can be used to find the SELECT statement being generated, and also to see the parameters that are being passed into SQL. To get started with SQL Server Profiler, start in SSMS (SQL Server Mgmt Studio). Click on Tools > SQL Server Profiler Once in SQL Server Profiler, start a new trace by going to File > New Trace… The “Connect to Server” dialog opens, where you select your SQL Server instance.  Then click on Connect. The Trace Properties window will open.

1. Enter a name for “Trace name”.

2. On the “Use the template” lookup, select “Tuning”.

3. If you want to view your trace later, check the “Save to file” button and enter a location for the trace file.

4. When you are done, click on the “Events Selection” tab at the top.

On the Events Selection window, only a small subset of Events will be listed.  To see the full list, check the “Show all events” checkbox. Expand the Cursors area and select the CursorExecute and CursorPrepare checkboxes. Scroll down and expand the Performance area, and select the ShowplanAll and the ShowplanXML checkboxes. Under the Stored Procedures area, select the RPC: Completed, RPC: Starting, SP: StmtCompleted, and SP: StmtStarting checkboxes. And finally, under the TSQL area, select the SQL: StmtCompleted and SQL: StmtStarting checkboxes. Now something else you can do.  If you want to narrow down your search to a specific database, you can click on “Column Filters…”.  This will open the Edit Filter window and you can filter on DatabaseName.  Then only trace items specific to the database you specify will be generated.  Another useful filter is the “TextData” field, that can be used to narrow the results down to specific tables, fields, etc., depending on the Event and the information in that field. Once you have everything set up and you are prepared to start the trace, click on “Run” in the Trace Properties window.  Note that this will return a large amount of data, so try to keep the interval that you are tracing short. After clicking on “Run”, perform the function that needs to be traced (for example, a process in AX, a job in AX, or in my case the integration in Connector that consumes the AX service).  Once you have finished the task you want to trace, come back into the Trace Profiler and click on the Stop Trace button. Now looking at the trace results, you can find your select statement and the cursor (along with the parameters passed) that AX created. Note the value of the parameters that were passed into SQL at the end of the cursor. In my case, I was able to copy the SELECT statement into SSMS and determine what was causing the SELECT to not find any records in AX, and I was on my way to fixing the Connector integration. SQL Server Profiler is a powerful tool that AX developers can use to analyze hard to find SELECT statements and cursors.