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

 

SQL Server Profiler button

Once in SQL Server Profiler, start a new trace by going to File > New Trace…

Creating a new trace in SQL Server Profiler

The “Connect to Server” dialog opens, where you select your SQL Server instance.  Then click on Connect.

Connection string window in SQL Server

The Trace Properties window will open.

  1. Enter a name for “Trace name”.
  1. On the “Use the template” lookup, select “Tuning”.
  1. If you want to view your trace later, check the “Save to file” button and enter a location for the trace file.
  1. When you are done, click on the “Events Selection” tab at the top.

New Trace parameters dialog

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.

Events in the Trace properties window

Expand the Cursors area and select the CursorExecute and CursorPrepare checkboxes.

Selecting CursorExecute and CursorPrepare

Scroll down and expand the Performance area, and select the ShowplanAll and the ShowplanXML checkboxes.

Selecting the Showplan trace properties

Under the Stored Procedures area, select the RPC: Completed, RPC: Starting, SP: StmtCompleted, and SP: StmtStarting checkboxes.

Trace properties in SQL Server

And finally, under the TSQL area, select the SQL: StmtCompleted and SQL: StmtStarting checkboxes.

Selecting the StmtCompleted and StmtStarting events

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.

Adding a database name filter

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.

Stopping the trace to analyze the results

Now looking at the trace results, you can find your select statement and the cursor (along with the parameters passed) that AX created.

Looking at all the trace results

The details of the trace show the parameters

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.

Recent Posts

Contact us

Tags