Last week we took a look at when you would use the COM Spy diagnostic tool. This week we will look at DBSpy, a tool that I am sure is familiar to most of you.
What it is
DBSpy records all calls happening at the database layer:
Why do you need it?
You would use DBSpy to diagnose any problems you have with the database server as it records every error even if they don’t make it up to the UI level.
To have DBSpy record additional details, you can turn on enhanced logging:
- Edit the A4W.INI file with Notepad. This file is located in <Sage 300 ERP>\Runtime. (If you are on an older version of Sage 300 ERP, you will look for <accpac>).
- Enter the following at the end of the [A4WSQLS] section:
LogDetails= 271232 - Save and close the file.
Some common functions you will see in a DBSpy log are:
- OPEN-TABLE/CLOSE-TABLE
- SELECT
- FETCH
Return codes for functions could tell you if there is a problem. For example, if you see DBS_NOT_FOUND, this means that it cannot find a record requested and could indicate a problem.
If you see DBS_NO_MORE_DATA: This means that there are no more records to be fetched but does not indicate a problem.
When you will need to run a DBSpy
- Issues with data activation
- If an error message has query related errors in the details such as “SqlExecute failed”.
- To see which SQL queries are executed by a certain process.
- Database load issue like if it stops at a certain table.
- Performance issues. If you are seeing this then better combine your DBSpy log with an RVSpy.
- Message with Insert/Update errors.
We have a video that details how to run a DBSpy log:
Our final part of our technical tour of diagnostic tools, we will take a look at what is RVSpy and when you will need to run it and also situations where a log won’t be useful.
If you have any questions about DBSpy, sound off in the comments.
