We’ve created some views via SSMS. Is there any way to query these created views through BAQ within EPICOR? Thanks.
Yes using External BAQ/datasource.
1 Like
I hope you put this outside of your epicor DB… potential issues.
What are the potential issues? Thank you, Jose.
Thank you, Aaron. So even in the same database, we still need to use the External Data Source?
Like Jose said, you’ll want to create your view in SSMS on a different database than your Epicor database. Of course it will be built from your Epicor database, but it should live on the custom database.
The views you’ll create in SSMS are only available via the External Data Source/BAQ
1 Like
The views were created on the same database as our Epicor database. So I need to move them to a different database in order to use the external BAQ? Thanks.
Yes that should be just fine
I am also very curious about what potential issues we would have if putting them in the same database as Epcior.
Also, if they are put in a different database, additional SQL scripts are needed and they need to be scheduled to run frequently in order to move the data from one database to another.
Fair question that I do not know the “right” answer too other than that it’s not recommended.
You would not need to store the data in a separate database nor would you need additional scripts. The view would be called which queries the Epicor db and returns back the data to the view.
If I had to take a stab at it; it’s probably unwise to build anything custom in the Epicor db as schema/stored procedure/view changes can/will occur on upgrade to the db and Epicor will overwrite during an upgrade.
Having your own separate little db living on the same server is a great way to house/backup/organize custom views/tables/stored procedures/functions that are used to interact with Epicor data.
Aaron, thank you very much for your information and time.
During upgrades the Epicor tooling assumes the database hasn’t been touched beyond what they do.
I have ran into issues where adding your own indexes breaks upgrades
adding your own views overlap views they are trying to create
adding your own tables again overlaps their own tables that they are adding in the future.
So avoid any potential issues and move all the stuff to your own Db.
1 Like
Thanks a lot, Jose.
Hi Aaron, I have creategd external datasource Type, external datasource. I could not see the views in the datasource metadata. How can I make the created views to be seen and chosen in the metadata and then in BAQ
So you created the Type, established your datasource, and tested your connection? I bet it’s not looking at the right catalog
Also, just because the button says “Retrieve External Tables” doesn’t mean it excludes Views. In this example, I search the External Tables and was able to retrieve my view
Yes, the connection tested successfully, but I can not get in views.
My views show up with I search…did you create that view to your custom database?
What should be entered for initial Catalog in Datasource maintenance? BTW, I am on 10.1.500.
Yes, I created the view in a custom database.
The initial catalog is the database on your database server instance that you want this datasource to point to. This should be your custom database