Have you run into any problems with upgrades and external BAQs? I went from 701 to 702a on the same server. My SQL views stayed in the database but the external BAQs didn't return any data. I opened them up and didn't see the external source field. I ended up just importing the BAQ back in since I had them saved off and it was fine. I haven't reported it to Epicor since it was easy enough for me to fix. Just curious if you anyone else had seen that.
On Thu, Oct 31, 2013 at 4:22 PM, <bruce.d.ordway@...> wrote:Â<div> <p>Just wondering if anybody has any feedback on using External Queries.<br><br>I've been experimenting with building some of my more complex SQL views.<br>I have seen one bug so far...a view name can't be longer than 30 characters.<br>Otherwise they seem to display in BAQs OK.
Thanks</div> <div style="color:#fff;min-height:0;"></div>
Hi Tanya,
>> view to run within 10 seconds in SQL Server
>>to consistently get within 30 seconds when run from Epicor
Good to know.
I wonder, does this mean you work in live instead of using a test system?
I've been using test so far & still need to verify I can export and import an external BAQ.
Thanks
---In vantage@yahoogroups.com, <tdenison@...> wrote:I use external BAQs quite a bit, since the BAQ builder is very limited in functionality. The biggest obstacle is that external BAQs have a hard-stop at a 30 second time-out. What I have found is that I have to get my view to run within 10 seconds in SQL Server in order to consistently get within 30 seconds when run from Epicor. When the external BAQ times out, a BAQ report will error out with "No records selected" as the error. Epicor is unable to explain to me why this is inconsistent. They were able to tell me that being able to modify the time-out value is in an unscheduled SCR.One thing that I am going to experiment with is using Transact-SQL to provide data back to Epicor, in order to improve performance.Tanya Denison | IT Manager | NRS, Inc.2009 S. Main Street, Moscow, ID 83843c: 208.301.0599 | p: 208.883.7834 Ext 277 | f: 208.882.1744On Thu, Oct 31, 2013 at 4:22 PM, <bruce.d.ordway@...> wrote:<div> <p>Just wondering if anybody has any feedback on using External Queries.<br><br>I've been experimenting with building some of my more complex SQL views.<br>I have seen one bug so far...a view name can't be longer than 30 characters.<br>Otherwise they seem to display in BAQs OK.
Thanks</div> <div style="color:#fff;min-height:0;"></div>
On Fri, Nov 1, 2013 at 11:50 AM, <bruce.d.ordway@...> wrote:Â<div> <p></p><p>Hi Tanya,</p><p> <br></p><p>>> view to run within 10 seconds in SQL Server<br></p><p>>>to consistently get within 30 seconds when run from Epicor</p><p>Good to know.<br></p><p style="font-style:normal;font-size:13px;background-color:transparent;font-family:arial, helvetica, clean, sans-serif;">I wonder, does this mean you work in live instead of using a test system?
I've been using test so far & still need to verify I can export and import an external BAQ.
Thanks
---In vantage@yahoogroups.com, <tdenison@...> wrote:I use external BAQs quite a bit, since the BAQ builder is very limited in functionality. The biggest obstacle is that external BAQs have a hard-stop at a 30 second time-out. Â What I have found is that I have to get my view to run within 10 seconds in SQL Server in order to consistently get within 30 seconds when run from Epicor. Â When the external BAQ times out, a BAQ report will error out with "No records selected" as the error. Â Epicor is unable to explain to me why this is inconsistent. Â They were able to tell me that being able to modify the time-out value is in an unscheduled SCR. Â
One thing that I am going to experiment with is using Transact-SQL to provide data back to Epicor, in order to improve performance.Tanya Denison | IT Manager | NRS, Inc.2009 S. Main Street, Moscow, IDÂ 83843On Thu, Oct 31, 2013 at 4:22 PM, <bruce.d.ordway@...> wrote:Â<div> <p>Just wondering if anybody has any feedback on using External Queries.<br><br>I've been experimenting with building some of my more complex SQL views.<br>I have seen one bug so far...a view name can't be longer than 30 characters.<br>Otherwise they seem to display in BAQs OK.
Thanks</div> <div style="color:#fff;"></div></div> <div style="color:#fff;min-height:0;"></div>
I did some SQL tracing on this and believe that Epicor is building a temp table of the full result set. So, depending on how complex your views are you can bump up against Epicor's time out to build the temp table. Epicor custom solution group doesn't support SQL views so they wouldn't take it any further even with my trace results. If you want to see if you find the same thing maybe we can get someone on it since it wouldn't just be me... Just an optimistic thought :)
---In vantage@yahoogroups.com, <tdenison@...> wrote:I use external BAQs quite a bit, since the BAQ builder is very limited in functionality. The biggest obstacle is that external BAQs have a hard-stop at a 30 second time-out. What I have found is that I have to get my view to run within 10 seconds in SQL Server in order to consistently get within 30 seconds when run from Epicor. When the external BAQ times out, a BAQ report will error out with "No records selected" as the error. Epicor is unable to explain to me why this is inconsistent. They were able to tell me that being able to modify the time-out value is in an unscheduled SCR.One thing that I am going to experiment with is using Transact-SQL to provide data back to Epicor, in order to improve performance.Tanya Denison | IT Manager | NRS, Inc.2009 S. Main Street, Moscow, ID 83843c: 208.301.0599 | p: 208.883.7834 Ext 277 | f: 208.882.1744On Thu, Oct 31, 2013 at 4:22 PM, <bruce.d.ordway@...> wrote:<div> <p>Just wondering if anybody has any feedback on using External Queries.<br><br>I've been experimenting with building some of my more complex SQL views.<br>I have seen one bug so far...a view name can't be longer than 30 characters.<br>Otherwise they seem to display in BAQs OK.
Thanks</div> <div style="color:rgb(255, 255, 255);min-height:0px;"></div>
I have used external SQL views extensively however depending on the data being calculated in the view we kept hitting timeout issues which is due to the ODBC connection timeouts (hardcoded by Microsoft so Epicor can't control/amend).
To resolve this we have resorted to:
1) create custom tables to hold data we want to see in the BAQ
2) create stored procedures to insert data into the custom tables
3) create sql jobs to run periodically to execute the stored procedures
4) create views (essentially a select * from the custom table)
5) create the BAQ linked to the views
This now resolves all issues with timeouts as E9 is just reading from our table (and not performing all the calculations)
One big problem we found was the way E9 interacts with SQL Functions - basically it executes the function against each row of data being returned to the BAQ which slows things down substantially.
Hope this information helps