Developing with the Microsoft Dynamics GP 2013 Named System Database
Microsoft Dynamics GP 2013 introduced another long sought after feature....
The Named System Database.
Instead of the System database being hardcoded to DYNAMICS, a different name can be selected when installing GP.
So what does that mean? Well, it means that more than one Dynamics GP system can reside on a single instance of SQL Server. Before when a second Dynamics GP system was needed, you would have to install a new instance of SQL Server. Now you can have multiple System Databases on a single server without needing additional instances of SQL Server.
For hosting partners, it means that they can host multiple clients off a single SQL Server instance. As a side benefit for consultants supporting multiple versions and multiple customers, it means no longer having to have lots of SQL Server instances on their machines
As a developer, how can I make sure my code uses the correct System Database for my instance?
From a Dexterity perspective it means updating your code to use a call to the function GetSystemDatabaseName() rather than using the constant SQL_SYSTEM_DBNAME to obtain the name of the System Database.
If you have code shared between previous versions of Dynamics, you can create your own function which has conditionally compiled code based on the value of a "version" constant. See the code below for an example:
Code MBS_GetSystemDatabaseName()
function returns string sSystemDBName;
optional in boolean fConnected = true;
#if MBS_PROD_MAJ >= 12 then
sSystemDBName = GetSystemDatabaseName(fConnected);
#else
sSystemDBName = SQL_SYSTEM_DBNAME;
#end if
But how can you write SQL code which can run against a company database, but reference data in the System Database?
You can't just prefix the table names with "DYNAMICS.." anymore as that might not be the correct name for the System Database.
I discussed this issue with Patrick Roth who pointed out that the GP 2013 company databases now have a new sySystemDB (SY00100) table with a single record in it that provides the Company ID and the name of the System Database. So if you need to know the Company ID of the current company database or the name of the System Database, just get the first record in the table (ie. from Dexterity: get first table sySystemDB;).
From SQL, try the query below to see the table.
select top 1 * from SY00100
So armed with this knowledge I created some code to build a query as a string to return some data from a table in the System Database. Then I used the exec() SQL function to call it. This works, but how can I return the value from the pass through SQL code to my script? The exec() function cannot do this, it has no mechanism to pass data back to the calling script.
I then asked my friend and SQL guru, Robert Cavill, "How you can execute pass through SQL code AND return data from the code?". He told me about the sp_executesql stored procedure which allows for parameters to be defined and passed to and from the pass through SQL code.
The final piece to the puzzle was identifying whether to use the hardcoded "DYNAMICS" (for pre GP 2013) or to use the data in the SY00100 table for the System Database name. This was fairly easy to solve as the SY00100 table will only exist in a company database from GP 2013 or later.
The code below should be executed in the context of a company database. It will identify the associated System Database and then look at the Company Master table and return whether the Use Security Checkbox in the Company Setup window is selected:
SQL Code Example
-- Define Variables
declare @UseSecurity INT
declare @SQLCode NVARCHAR(4000)
declare @SYSDBNAME CHAR(80)
-- Identify System Database Name
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SY00100]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
select top 1 @SYSDBNAME = DBNAME from SY00100
else
set @SYSDBNAME = 'DYNAMICS'
-- Define and Execute pass through SQL Code
set @SQLCode = N'select @pUseSecurity = USESCRTY from ' + rtrim(@SYSDBNAME) + '..SY01500 (nolock)
where INTERID = DB_NAME()'
exec sp_executesql @SQLCode, N'@pUseSecurity int output', @UseSecurity output
-- Display result from Variable
select @UseSecurity AS UseSecurity
/* Copyright © Microsoft Corporation. All Rights Reserved. */
/* This code released under the terms of the */
/* Microsoft Public License (MS-PL, https://opensource.org/licenses/ms-pl.html.) */
I hope you find these techniques useful. I know I did. In my next post you will see why....
David