Currently, I am able to directly cut/past the BAQ query into SQL studio, but it does not always work. I always end up rewriting it. Is there any easier method?
1 Like
Haha, our local SQL ninja wants this also. I’d suggest putting this in feature requests 
… Edit wait, you can cut and paste? I’ve never tried it but our BA says she can’t PASTE SQL from SSMS. We are on 10.2.300.9. hmm. Maybe the rational is, create the query directly in the editor to avoid any naming conflicts etc? I always just write mine in the editor. I use SQL when it’s a quick easy query. Going from C# Dev to SQL developer is hard for me 
We are running 10.1.400.38, I am able copy/past from BAQ> Summary> Query Phrase area. I even tried, capturing the SQL by turning on tracing but it did not produce
the desired result.
Mazin
Epicor references tables that are actually views, so that’s more than likely the changes you need to make in ssms.
2 Likes
I mis read your question. I thought it was SSMS into the BAQ. Yes, use the dbo.[tablename] to replicate the BAQ in SSMS if you have UD columns on said table. Otherwise use the ERP or ICE.[tablename] @Aaron_Moreng thanks for pointing that obvious thing out!
1 Like
Calculated fields will also trip you up, so you have to convert those. The way Epicor relates them isn’t the same in SQL.
2 Likes
What’s odd is in the BAQ I have several inner joins and left outer joins. But in the Query Phrase, I see that Epicor placed cross joins to three tables.
Don’t forget tenant, territory and payroll magic filtering as needed
Not sure if there are additional subsequent filters appended automatically for security reasons, don’t remember.
FYI, the baq architect will be at the booth and presenting at insights first time. Also I am dragging him to the E10 help dinner.
#BringYourQuestions
4 Likes
A while back I did a spreadsheet with VBA which took pasted SQL from a BAQ and turned it into SSMS SQL. It worked for most things that anyone did, but never really proved worth the effort getting totally foolproof and the need for it has mostly gone away for us now.
I think I proved to my own satisfaction that it is possible to translate automatically, it’s just a bit of a sledgehammer to crack a nut.
Hi @asmar
i do not know if you are a member of EUG or not, but i have uploaded a post there which will help you on this subject, other members have tried and add some tips to it as well, this is the link
[IMPORTING BAQ DATA TO 3RD PARTY APP E.G. EXCEL, MSSMS, ETC]
if you are not an EUG member, please let me know and i will email the whole information to you.
1 Like
Hi @asmar ,
Epicor 10 appends extra query checks to your BAQ query in the background.
If you want to get the final query, you can do so using SQL Server Profiler, and that’s assuming you have granted access to the tooling and db.
Open an SQL Profiler session and change the template to Tuning:
Add a filter to reduce unrelated activity
Open your BAQ and run a test
Go back to SQL Profiler trace and stop it. Then search for a meaningful keyword
Once found, copy and paste it on SSMS, and compare it to your BAQ Phrase.
Run both queries and compare the results to ensure they give the same data.
VS 
VoilĂ , notice that your query is only a part of the executed query.
I have compared couple more and the same principle applies.
Also, it looks like every time you add Customer table, the same territory and salesrep checks will be added to the final query. This is probably similar to what @Bart_Elia said about Payroll magic.
Carlos
PSE
7 Likes
I (and other configurator users) want that XML data!
3 Likes
Hi @carlosqt,
this will help tracking the BAQ executions, but won’t give you the SQL code that needed to run the query on UI of MSSMS, looking at your print screen, it is the same code issues coming from BAQ Phrase
Once you have tested a BAQ you can run “Get Query Execution Plan” from the actions menu.
This can then be opened with SSMS.
I had a to debug a finance BAQ that running out of memory and failing and used this. Turned out the SQL being executed bore no resemblance to what shown in the BAQ designer “Query Phrase” window!
I copied the query phrase directly to SSMS and it ran in a few msec 
2 Likes
Thanks @richardh and welcome to our Forum,
i have done what you suggested, the outcome file will certainly help analysing the performance of the BAQ query, but i need the SQL code to use it in different App as well as here, could you please explain how can be generated/viewed from this file?
I did the former already a few days ago. However, the execution plan is a graphical representation of the executed SQL. Is there a way to extract the requested query vs. the executing query? An Oracle database can track the submitted query and the executing query.
My end Goal is to convert the BAQ query to SQLserver SQL query and define a database View that can be accessed by an ASP application, SharePoint App, or MS Power BI.
Thank you all for your prompt response and suggestions.
You need to develop and test the SQL first. Then you can create a database View if you have DBA privileges. You will need to create a schema to contain all your custom views and to isolate them from Epicor views. Additionally, you will need to grant specific access to the view. The command for creating a view is below:
USE [Epicor10]
GO
SET ANSI_NULLS ON
GO
CREATE VIEW [SCHEMA].[VIEW_NAME] AS
Insert your SQL statement here...….
GO
`Isn’t that already solved by calling the REST API BAQSvc? You can call the BAQ query from any client consuming the rest api service.
Carlos
2 Likes
We are currently running 10.1.400.38, is REST API BAQSvc available for this release? In the meantime, I need to create the VIEW for these existing applications. Thanks for your idea and suggestion. I will need to test this in the very near feature, and after I attend my scheduled training on REST at Insights 2019.
1 Like
I had hoped REST would be useful for Power BI. So far I haven’t found a single situation where it isn’t too slow and unresponsive.
If you have any tips to make it work effectively, please share!