NOTE: The fact that you are following this article should mean that you have established root cause and have eliminated the problem you are experiencing as a Known Issue or being covered in the Help Files or Help Centre .

Before you begin

Any troubleshooting or investigation should be done on a copy of the data, preferably offsite. 

Run a Verify Data to establish if the system can identify any problems and is able to fix them automatically.  

Re-index the database in SQL prior to beginning an in-depth investigation.

Establish the expected behavior? - it is important that you understand what ‘normal’ looks like) Use your own software and the Help Files to gain a good understanding.  It is important that replicate the settings and process followed on the affected system to determine what the results should look like (both in terms of enquiry screens within Sage 200 and in the SQL database).

Familiarise yourself with the SQL tables involved.  Understanding the Sage 200 Database is a good place to start but is also possible to create a database diagram in SQL to gain a better understanding of the relationships between the tables involved.

Clear any searches or filters which may be applied to any lists that you are looking at which may have an impact on what you expect to see. 

Troubleshooting

Determine if the affected records have anything in common (i.e. same customer or stock item etc.) – if so compare these records (in the program and in SQL) to none affected records. 

Re-enter and re-process using the same and different records does the problem re-occur? (For example, if you can’t invoice Sales Order try amending despatch and un-allocating; try entering a new order; try adding new stock).

Try alternative processes that should have the same end result (i.e. you are experiencing an issue despatching a sales order for a stock item, try writing off the stock).

Consider if the problem could be occurring earlier in the process – for example, you have an issue when despatching certain sales order could the problem be apparent at the point of creating the order.

Data Investigations

Compare affected and none affected records in the application and in SQL.

Compare the difference in processes between affected and none affected records.  Microsoft SQL Server Profiler is an extremely useful tool for determining where a process may be falling down including which tables and records are involved.

Compare the difference between an affected and a none affected database (for example this you could compare the problem data to the demo data or an older set of the same data that doesn’t exhibit the problem).  Here we provide instruction on comparing two databases in SQL (there are a number of commercially available products available that can offer additional functionality in this area).

There are a number of useful SQL scripts which can be invaluable in data investigations, for example, here is a script that allows you to search keywords, this can be useful for finding which database table and column a particular field is stored.

Further advice on investigating problems with traceable stock. Read more >