If you’re working with Dynamics SL and anywhere near the technical side, you can’t miss out on SQL Server Profiler – the tool that can often answer “what the heck is SL doing?”

First off – SQL Server Management Studio (SSMS) is FREE and a separate download .  You can use SSMS v16 (or another current version) to connect to any previous version of SQL Server.  Yes, even your SQL Server 2005.

The connection dialog box asks for the server name and authentication type. More than likely in a Dynamics SL environment, you can connect with **Windows Authentication. **The server name is going to be the server name that you enter in the Find Database screen (98.000.01) in SL.  Within SSMS, you can do/create/break a myriad of things – lots of topics for another day.

Now, to SQL Server Profiler.  SQL Server Profiler is a listener that captures queries (trace) run by a SQL Server, which you can replay at a later date (today, tomorrow, a year from now) to diagnose or deconstruct the issue.  Profiler can be opened from the SSMS Tools menu and will ask you to connect to a server.

Now the Trace Properties windows pops up – these are the settings for the query net that you’re casting out to collect information.  For purposes of gathering some diagnostic information about Dynamics SL to use immediately, we’re going to go to the **Events Selection **tab and click the Column Filters… button.  Under LoginName, enter your Windows username.  This will narrow the net of queries down to only those coming from your login. Of course, as a good administrator, you would have never used your login to run any other services or application logins.

Additionally, you want to deselect the Security Audit and_ Sessions._ These items are helpful when diagnosing how many connects a login is opening or who is connecting – but we’re focusing on looking at where on earth SL is getting its data from or where is it putting it.

You hit Run and you go back to the Dynamics SL interface and do a few things.  In my example below, I opened the _VendorID _PV window and loaded the screen for a vendor.

The bulk of the queries show in the TextData column are run against the application database, but some are run against the system database.  If you’re loosely familiar with T-SQL, you’re probably a little off-put by the plethora of short statements like:

SubAcct_Descr ‘100’

The short statements are stored procedures on the SQL Server that Dynamics SL can call by name.  In SSMS you can find the stored procedures under the database>Programmability>Stored Procedures.  If you right-click on the stored procedure entry in SSMS, there is an option to Modify – which opens a window with a templated ALTER statement for the procedure.

If you know what you’re doing, you can change the stored procedure by running the TSQL in SSMS.  If you’re looking for where information is coming from – the displayed query will have it for you.

[sql]

Select Descr from Subacct
where Sub = @parm1

[/sql]

While this isn’t an incredibly lengthy query, it’s about twice as long as the stored procedure call that came over from Dynamics SL.  In terms of performance, it is better to be sending shorter queries from an application/web server to a SQL server.  Well done, SL. (I’ll just ignore the fact that Subacct.Descr is a fixed width char(30) and let you have the moment)

Further along is RPC (remote procedure call) that runs:

[sql]

declare @p1 int
set @p1=180150021
declare @p3 int
set @p3=2
declare @p4 int
set @p4=4
declare @p5 int
set @p5=-1
exec sp_cursoropen @p1 output,N’ EXEC APBalances_Vend_Cpny ”10013”,”IECIS” ‘,@p3 output,@p4 output,@p5 output
select @p1, @p3, @p4, @p5

[/sql]

Ignoring the appetizer, soup, salad, and dessert of the T-SQL call – you’re left with:

[sql]

EXEC APBalances_Vend_Cpny ‘10013’,’IECIS’

[/sql]

I took the text from line 9 and removed the escaped single quotes (find-replace is your friend on longer queries).  Over in SSMS I can run the query to see the data as passed from the SQL Server (helpful when troubleshooting masking!) or examine the stored procedure APBalances_Vend_Cpny to find the root table is _AP_Balances _ for use in custom reports, Quick Queries, etc.

What fun stuff can you pull out of SQL Server Profiler?