I have one External BAQ that pulls data from an outside database. I have another BAQ that pulls data from Epicor. I need to create a join based on PartNum between the results of the two BAQs. Today I am doing this convoluted process where I export the results of one BAQ to CSV, then import that CSV into a UD Table in Epicor, then run the second BAQ and do the join there.
Is there a better way to do this? Maybe a BAQ within a BAQ? Or a way to query from both an external data source and from Epicor at the same time?
Linked Server in SSMS is new to me but looks promising. When you did this, were you able to run BAQs from Epicor against the linked server? Or does it only give you access to the linked server from within the SSMS console?
It was a bit convoluted, I’ll admit. We didn’t access the linked server directly, but you can query it from within a standard SQL Server database, which lets you create a view which IS accessible to Epicor as an external BAQ. The difference is that you can then access the Epicor tables from that same database and make them accessible in the eBAQ as well.
It all works fine as long as you’re viewing and don’t want to update Epicor that way.
I’ve also used linked server to allow a BAQ to access to some data sources. In a few case I also created views to get some of the weirder aggregation out of the way before it gets to Epicor.
There used to be a timeout issue in E9 with some external BAQs if they didn’t get a response from the source in time. Not sure if that still exists in E10.
Ok, I got it working using these steps: 1) create a linked server in SMSS that points to the old server 2) create a View in my Epicor database that points to a table in my linked server 3) create an “External” Datasource that points to my local Epicor database (necessary to see the new View in BAQ Designer) 4) I can create an External BAQ using this “External” datasource (which is really just my local database) and can see the newly created View
When you say “a view in my Epicor database”, is that in the actual Epicor database, or do you mean in the same SQL Server instance Epicor is on? I had understood it was a bad idea to put anything extra in the actual Epicor databases, so we have an additional one of our own we put things like this in. Just checking.
The actual Epicor database. As long as the view is assigned a unique name that will never be used by Epicor, it’s just fine there. It’s just a view, not like we are adding extra tables and data to the Epicor database, which I would never do. There is no risk of data loss here. The greatest risk I could imagine is that some future Epicor change somehow removes the view, and if that happens I haven’t lost any data, just the view, which takes 30 seconds to recreate.
Mark the root of the issue is there in your description of problem: “The view is in an Epicor schema name”.
While I initially said “As long as the view is assigned a unique name that will never be used by Epicor, it’s just fine there”, I should have added more detail to articulate my unstated assumption. I assumed that no one would ever be putting anything custom into the ERP schema as kind of common sense, because ERP schema can change over time, but maybe it needs to be stated. My custom view is not in the ERP schema and so will be unaffected. No one should be creating custom anything in the ERP schema. Thank you for helping me clarify.
I think I sometimes come across as being dogmatic and that’s not my intention, so apologies if I came across as a jerk. I was responding to the “What can possibly go wrong?” statement. It’s that unforeseen situation that usually burns me.
Moreover, what I’d like to see as a SaaS user is a way to do what you’re doing WITHIN the framework so I can regain some capabilities lost when moving to the cloud. Product Configurator now hides input values in an XML field that we just cannot access but there is a workaround one can do with a view and an external BAQ.
I have done something that you have described using the executive query.
Note - Executive query was broken with 10.1.X - and is now back and working in 10.2.300
Worked great in 8.03
Are you familiar with the way the Executive query works, I could dig in my tool box for the cheat sheet if you need it.
I’m sure what you’ve done is fine for the reasons you give.
We do put anything like this in a separate database, though, even so, for three reasons: first it’s tidier, especially as anything you do once ends up happening more than once, second it really isn’t any more difficult … and third, even if something we do is logically totally ok but saying we’ve done it causes surprise to Epicor employees, we try not to do that thing. It adds friction and complexity to any support calls that we can do without.
Back to my very first statement though - your original suggestion of a BAQ within a BAQ is high on my wish list. Then we wouldn’t even be discussing this.
