Adventures in Microsoft Dynamics GP 2013: Working with the Dynamics Named Instance in SQL

Posted By
BDO

Many years ago a young developer toiled away writing customizations for an asset management application. There came a time when the application introduced a new feature which allowed multiple databases to be merged by way of a seemingly magical “company id” column. Being naïve to the cruel realities of the world, the developer chuckled at the trivial task of removing hard-coded database references.  It was not until many weeks of frustration had passed that the cold truth dawned on him – DO NOT hard-code database names! Fast forward several years and the now less-than-young and somewhat cynical developer carried the harsh lesson with him into the Dynamics GP world. That is, of course, with one notable exception – the DYNAMICS database.

So, the advent of Microsoft Dynamics GP 2013 is upon us. With it comes several exciting changes. Amongst these is the ability to have one or more named instances of the DYNAMICS database.  There is one notable exception to all the fanfare – remember those years worth of SQL stored procedures that you wrote with blissful ignorance thinking that the DYNAMICS database would always be the DYNAMICS database?

As it happens, it is relatively simple to determine the name of the configuration database associated with a specific Dynamics GP company.

 

The trickier aspect of this conundrum is getting the results of the above query to replace that well-intentioned but ill conceived hard-coded reference to the DYNAMICS database.  After some thought, the following SQL function was created.

While having a function to automate the process of retrieving the name of the configuration database associated with the current Dynamics GP company is a definite advantage it unfortunately does not remove the necessity of the arduous process of revising existing code.  There are several possibilities but the current personal favorite is to use sp_executesql.

The above can be used across Dynamics GP versions. Let this be a subtle hint for those just thinking about upgrading their code. Further upgrade adventures are sure to follow!

 

 

 

BDO eBook - The Cloud Changes the Game

There are times in the course of your business when you have the opportunity to dramatically accelerate growth and improve day-to-day efficiencies. Recognizing

Download