I've previously posted some queries for retrieving values from multiple company databases. I tried a few query designs, but ended up settling with cursors to loop through the different company databases and assemble the results. Here are two examples:
https://dynamicsgpland.blogspot.com/2016/03/query-functional-currency-for-all.html
https://dynamicsgpland.blogspot.com/2015/10/finding-batches-with-errors-across-all.html
Given the importance of the queries and how infrequently I used them, I didn't worry about the cursor or spend much additional time trying to figure out an alternative.
While these ad hoc, one-time queries are probably just fine using cursors, many SQL folks (myself included) are not fans of cursors, and it's often a fun challenge to figure out a way to use a proper set-based query to replace an iterating cursor in a query.
Well, Ian Grieve has come up with a very clever technique for assembling a query that can run against multiple Dynamics GP company databases. Rather than executing a query multiple times and storing the results each time, he's figured out an intriguing method for dynamically assembling the queries for multiple companies, and then running that entire dynamic query string using sp_executesql.
Here is his blog post:
http://www.azurecurve.co.uk/2017/05/sql-script-to-return-functional-currencies-for-all-companies-without-a-cursor/
While I understand the general technique, I am not familiar with some of the commands and syntax--particularly the STUFF and FOR XMLPATH statements--so it is going to take me a while to deconstruct and fully understand the entire query.
But hats off to Ian for the creativity! I think he's come up with a new standard template for multi-company Dynamics GP queries!
Steve Endow is a Microsoft MVP
for Dynamics GP and a Dynamics GP Certified IT Professional in Los Angeles.
He is the owner of Precipio Services, which provides Dynamics GP
integrations, customizations, and automation solutions.
No comments:
Post a Comment