Sage 200 - Using Microsoft SQL Server Profiler to troubleshoot data issues

1. Open a new query in Microsoft SQL Server Management Studio, type and press F5 or select Execute.
2. In the search results, look for the name of the database you are troubleshooting and make a note of the database_id value. In this example, our database_id value will be 8
   

   

3. Within Management Studio, select Tools > SQL Server Profiler.
4. Enter your login details. This can be via SQL Server Authentication or Windows Authentication, depending on your system setup.
5. The Trace Properties window appears. Select the Events Selection tab.
6. Unselect Audit Login, Audit Logout and ExistingConnection options.
   • Under Stored Procedures, on the RPC:Complete event line, ensure the options selected are Application Name, NT User Name, Login Name, CPU, Reads, Writes, Duration, Client Process ID, SPID, Start Time, End Time and Binary Data.
   • Under TSQL, on the SQL:BatchCompleted and SQL:BatchStarting event lines, ensure the options selected are Text Data, Application Name, NT User Name, Login Name, CPU, Reads, Writes, Duration, Client Process ID, SPID, Start Time, End Time and Binary Data.
7. Tick the 'Show all columns' option on the bottom right of the dialog box.
8. Select Column Filters...
9. In the Edit Filter dialog, select 'DatabaseID'.
10. Expand the 'Equals' node in the right-hand panel and type the value of the database ID from the previous section.
    

    

11. Select OK.
12. Select the General tab.
13. At this stage, you can select Run to begin the trace. If you wish, you can tick the 'Save to file' option so you can review the output at a later time. However, we recommend you leave this option deselected for now until you are comfortable with running the trace. The output file can grow substantially and therefore, you may wish to save it as a file (if desired) after you have finished running the trace.
14. The trace has now started. Click the  button to stop the trace and then the  button to clear the results.

You have now prepared SQL Profiler in readiness for monitoring the database activity when you replicate the actions you wish to investigate within Sage 200.

Replicate the steps in Sage 200

At this stage, you should start the trace running again by selecting the button from the toolbar and then replicate the issue/actions you wish to investigate. When you have finished, select the  button.

TIP: Try to start and stop the trace file as quickly as possible before and after replicating. This will ensure you have less activity to monitor in the trace file. You may wish to use the below scenario as an example as to how best to run a trace on the database.

Example

In this example, we are trying to delete what we believe is an unused stock item. The item has no quantity in stock, is not allocated to an order or associated with a bill of material and the item has never been used on a sales or purchase order. The system advises however that the item cannot be deleted.

SQL Profiler will therefore be able to assist us in showing all statements carried out on the database. The results can then be reviewed and will hopefully show us which of the above criteria is actually stopping the stock item from being deleted.

To run the trace

In this scenario, the advisory message appears the moment we select the stock code we wish to delete. Therefore, we must ensure the trace is running before we select the code. Within Sage 200, the Delete Stock Item screen should be launched before starting the trace to minimise on the unnecessary output. So to run the trace, the steps would be as follows...

1. In Sage 200, select Stock Control > Stock Records > Delete Stock Item.
2. In SQL Profiler, start the trace by selecting the  button.
3. In Sage 200 and the Delete Stock Item window, type the stock code and press Tab or select it from the drop-down list. The Deletion Status should then appear.
4. Stop the trace in SQL Profiler by selecting the button.

You should now have a trace output in SQL Profiler. Click 'View Screenshot' below to view the output from the trace file created in the example in the previous section.

You should look through the list of statements ran (in the TextData column) and use them to try and identify which results may be preventing you from carrying out the action you're trying to complete in the software. To check the results of the various statements, copy and paste the text from the bottom panel in SQL Profiler to a new query in Management Studio. Executing the query will return the same result that the program receives when you receive your error/advisory message in the program.

Solution to example

The example scenario involves trying to delete a stock item but the program advises that it's in use. This could be due to stock allocation, an order, a return or a bill of materials.

The trace file shows activity which backs this up, i.e.. the program is checking tables related to stock (WarehouseItem, BinItem), bill of materials (BomBuildProduct, BomComponentLine), sales orders (SOPStandardItemLink) and purchase orders (POPStandardItemLink). Therefore, we can run these queries in Management Studio and use the results to find where the stock item is being used.

The extract below shows the query that is ran when the POPStandardItemLink table is checked. See screenshot below...

Copy the entire text from the bottom window to the clipboard and paste it into a new query in Management Studio and click Execute. See screenshot below...

We can see now that this item appears to be on purchase order number 3285. Searching for this order in the program confirms this to be the case.

The order would need to be archived/deleted before the stock item could also be deleted.

Use the following tips as a guide to help you when reviewing the trace file...

• It's usually more beneficial to start at the bottom of the trace file and work upwards.
• Queries highlighted in yellow are Stored Procedure queries. These will often look at the key tables that relate to your query, passing through a declared variable to return the results to the software so it's usually a good idea to look at these queries first.
• If possible, try to run the trace when no other users are logged into the software you are troubleshooting. The guidance above limits the trace to a single database but will include activity from all users, making location of the key statement a much lengthier process. If necessary, take a backup of the database and restore it into a test environment before running SQL Profiler.
• Think about the areas of the program that are involved with your query - this will immediately narrow down some of the tables that you would want to analyse in the trace file. For example, if you're trying to save a sales order, it's unlikely that the purchase order tables will be the cause of the problem. If using non-traceable stock, the traceable item tables can be ignored if they show in the results.
• Look at the type of queries being performed in the database in relation to the action you're performing in the software. For example, if you're troubleshooting an issue when trying to save a new sales or purchase order, you should look for an INSERT statement first.
• It can be useful to run a trace for a working record (or dataset) and compare the results (for example, to see if the system stops at a certain point in the process on the none working example). It is possible to export SQL Profiler Traces to a file (File, Export, Extract SQL Server Events and then Extract Transact-SQL Events...), the result of comparable traces can then be compared using a tool such as Notepad++ or WinMerge.

If you are still unable to discover the information you're looking for, you can save the trace file as a .trc file via the File > Save As > Trace File... menu option and have the file to hand should you wish to contact Technical Support.