Happy 4th of July everyone!!   I hope everyone's fourth is a safe one [:D]

This month I would like to take a look at specific error messages and the way I troubleshoot them.  These error messages may happen during Committing Shipments or posting batches throughout the Sage 500 application. The error messages usually begin with "Unexpected error occurred in..." followed by the name of a stored procedure such as spsoProcessInvtCommit.   The technique I use to troubleshoot errors like this is with SQL Server Profiler. If you are not familiar with this tool, you should contact someone from your IT staff. You should be very careful running profiler traces on your production Sage 500 database. It can definitely cause performance degradation. I will provide some tips setting up and what to look for in the SQL Profiler trace. I will not be covering the basics of creating traces. By the way, there is an excellent book by Brad M McGehee named "Mastering SQL Server Profiler"  that will show you everything you could want to know about SQL Server Profiler

To start with, the error message that is displayed on screen is stored in a messages table. This SQL table in the Sage 500 application database called tsmLocalMessage and it holds a lot of messages that are displayed to the screen. If you look at the MessageText field in that table, you see a list like the below.

Some the MessageTexts look like Unexpected {0} Value {1}.  The 0 and 1 are placeholders for other text that is passed into the message, depending where the logic flows.

The way to determine what is causing the error is to run a SQL Profiler Trace. Specific areas to look at. 

1. When creating a Profiler trace you must include Errors and Warnings, T-SQL, and Stored Procedures events. You can create a filter to only select the Login that matches the Sage 500 users login. Once you have the Profiler set, start it and immediately pause it, and clear it.  
2. Now, go back to Sage 500 client and launch the form /  task, related to the reported issue. Then setup the form / task with all the parameters required to be able to click the Proceed (or similar ) button that will cause the error. (But do not click the button just yet).  
3. Go back to the Profiler Trace and start it
4. Immediately go back to the Sage 500 client and click the button that will generate the error message. After the error message occurs, click OK through the error message.
5. Go back to the Profiler trace, stop and save it someplace.  
6. In the Profiler trace file, go to the end and click somewhere on the last line.
   1. Press Ctrl-F to open the Find box.
   2. In the "Find what" task enter spGetLocalMsg.
   3. Change the Search in Column value to TextData and click on the Find Previous button.
   4. What we are looking for is the call to the stored procedure spGetLocalMsg which will ultimately pass the message template from the tsmLocalMessage table to the Sage 500 client. 
   5. Once you find that entry in the Profiler, manually search above that line until you find a hard error(an INSERT into a table error, or arithmetic overflow). 
   6. Instead of searching for spGetLocalMsg, (step 6b) you could also search for Rollback in the TextData column or Exception or Error in the EventClass column.

Once you have located the actual error, you can determine what cause of action to take.  You can ty to lookup the error messages in the Customer / Partner portal too. You can also lookup part of the error message displayed on the screen and match it to the MessageText in the tsmLocalMessage table. Then, search for the MessageNo in the Profiler trace.

I hope this tip helps save you some time finding the cause of the error!

-Tony