Is it possible to get the actual SQL Query generated by SBM to display a specific report created using the SBM UI?
I tried the SQL Profiler, but I am wondering if I could use something else!
Thanks!
Cybersecurity
DevOps Cloud
IT Operations Cloud
If an answer to your question is correct, click on "Verify Answer" under the "More" button. The answer will now appear with a checkmark. Please be sure to always mark answers that resolve your issue as verified. Your fellow Community members will appreciate it!  Learn more
Is it possible to get the actual SQL Query generated by SBM to display a specific report created using the SBM UI?
I tried the SQL Profiler, but I am wondering if I could use something else!
Thanks!
A hack way to do it is add an Advanced SQL condition to the report that is malformed. The entire SQL will be logged to the server's application event log and then you can just ignore the bit that you added.
if I add ( where 1=1) in the advanced SQL condition, will that log the full query to the application event viewer?
That looks close to being syntactically correct. Just put "bad" or something otherwise very non-sql :)
I just noticed that we can't use 'Use Basic Conditions' with 'Use Advanced SQL Conditions' in the search filter section of the report, if the report has already some defined conditions using the' Basic Conditions' option then how can we handle that, please?
Use Composer to identify the database name of the field in your query. For example, if it is WASINPROCESSAUDIT, then the resulting where clause would be roughly TS_WASINPROCESSAUDIT=?.
If you can be more precise about your goal, I can try to comment further.
Otherwise, you can resort to actual database tricks like SQL Profiler, tracing etc.
Thanks , for sharing these ideas. The point is our reporting team want to know the SQL query of any report they want so that they can utilize it in their BI reporting.
The most complete answer would be to have them familiarize themselves with the SBM Schema Documentation. It is part of the documentation set and can be found here: https://docs.microfocus.com/doc/Solutions_Business_Manager/12.2/SBM_Database_Schema_Reference_Guide
Support may be able to provide additional SQL tracing options. For example, you can trace your ODBC connection. I would recommend doing ODBC tracing in a non-production environment as it will affect performance.
You appear to be on an old version of SBM here. In at least 11.8 and newer, the option of using Advanced SQL is a check box that can be used in addition to the basic conditions.
Yes, unfortunately, we have 11.4
I use this "malformed advanced SQL" approach all the time.