Dynamics GP: Creating a Test Company for Safe Exploration and Configuration

Table of Contents

Creating a test company within Microsoft Dynamics GP is an essential practice for businesses looking to implement changes, test new configurations, or train users without risking data integrity in the live production environment. A test company serves as a secure sandbox, allowing administrators and users to experiment freely with different scenarios, modules, and customizations. This process typically involves restoring a backup of your live company database to a new database designated for testing. However, simply restoring the database is often not sufficient; specific adjustments are required to ensure the test company functions correctly and independently from the live environment.

Why a Test Company is Crucial¶

Using a dedicated test environment prevents accidental data modification or corruption in your operational database. It provides a safe space to validate customizations, apply service packs or hotfixes, train new employees using realistic data, and troubleshoot issues without impacting ongoing business processes. Before making any significant changes to your live Dynamics GP system, testing them thoroughly in a replicated environment is highly recommended. This proactive approach minimizes risks and helps ensure smooth transitions for updates and configurations.

Post-Creation Adjustments and Considerations¶

After restoring a live company database backup to create your test environment, several critical steps are necessary to isolate the test company and correct references that still point to the live database or system-wide settings. Failing to perform these steps can lead to unexpected behavior, data inconsistencies, or even unintended modifications in your live system. Attention to detail during this phase is paramount for a functional and safe test company.

Addressing Human Resources Specifics¶

For organizations utilizing the Human Resources module in Microsoft Dynamics GP, a particular area requiring attention after creating a test company is the Attendance Setup. While the core data for Attendance Setup (stored in table TAST0130) is usually copied during the database restore, a field within this table often retains a reference to the original live company database. This can cause issues or incorrect behavior within the test environment.

To correct this, you have two primary options. The first involves navigating to the Attendance Setup window within the new test company (accessible via Tools > Setup > Human Resources > Attendance > Setup). Simply open this window, verify the settings against your live company’s configuration, and save the data. This action effectively updates the internal company reference within the TAST0130 table to point to the test company’s database ID. Alternatively, a more direct approach involves using SQL. You can update the COMPANYCODE_I field in the TAST0130 table directly, changing its value to match the INTERID value of your test company database, which can be found in the Dynamics..SY01500 system table.

Managing Audit Trails in the Test Environment¶

If your Dynamics GP implementation includes Audit Trails, special caution is needed when setting up a test company. Audit Trails typically function using database triggers that record changes and log them, often to a separate audit database or table. When you restore your live company database, these triggers are copied along with the data. Crucially, these copied triggers may still be configured to write audit information back to the live audit database or table, not one associated with the test company.

Attempting to disable or remove Audit Trails for the test company through the standard Audit Trail Maintenance window within the Dynamics GP front-end is highly discouraged and potentially dangerous. Performing this action in the test company interface could inadvertently affect the audit configuration or history in your live production environment. Instead, you must use SQL to identify and remove the audit triggers specifically from the test company database. This ensures that no activity within the test environment is logged to or interferes with the live audit data. Specific SQL scripts and procedures are required for this task, often detailed in Microsoft support resources focused on managing Audit Trails in test environments.

Correcting Company References with SQL Script¶

A fundamental issue after restoring a live company database is that many internal references, such as COMPANYID and INTERID, will still hold the values corresponding to the live company. This can lead to misidentification of the company within Dexterity-based applications, third-party products, and even some core Dynamics GP functions that rely on these identifiers. To align these references with the test company’s actual ID and database name as registered in the system-wide Dynamics..SY01500 table, a specific SQL script must be executed against the newly created test company database.

This script systematically searches for columns named COMPANYID, CMPANYID, INTERID, DB_NAME, and DBNAME across various tables within the test database. For the company ID columns (COMPANYID, CMPANYID), it updates the value to the correct company ID for the test company as found in SY01500. For the database name columns (INTERID, DB_NAME, DBNAME), it updates the value to the actual name of the test company database. Executing this script ensures that applications and modules correctly identify the test environment.

Here is the SQL script commonly used for this purpose:

if exists (select 1 from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = 'SY00100') begin
  declare @Statement varchar(850)
  select @Statement = 'declare @cStatement varchar(255)
declare G_cursor CURSOR for
select case when UPPER(a.COLUMN_NAME) in (''COMPANYID'',''CMPANYID'')
  then ''update ''+a.TABLE_NAME+'' set ''+a.COLUMN_NAME+'' = ''+ cast(b.CMPANYID as char(3))
  else ''update ''+a.TABLE_NAME+'' set ''+a.COLUMN_NAME+'' = ''''''+ db_name()+'''''''' end
from INFORMATION_SCHEMA.COLUMNS a, '+rtrim(DBNAME)+'.dbo.SY01500 b
  where UPPER(a.COLUMN_NAME) in (''COMPANYID'',''CMPANYID'',''INTERID'',''DB_NAME'',''DBNAME'')
    and b.INTERID = db_name() and COLUMN_DEFAULT is not null
 and rtrim(a.TABLE_NAME)+''-''+rtrim(a.COLUMN_NAME) <> ''SY00100-DBNAME''
  order by a.TABLE_NAME
set nocount on
OPEN G_cursor
FETCH NEXT FROM G_cursor INTO @cStatement
WHILE (@@FETCH_STATUS <> -1)
begin
  exec (@cStatement)
  FETCH NEXT FROM G_cursor INTO @cStatement
end
close G_cursor
DEALLOCATE G_cursor
set nocount off'
  from SY00100
  exec (@Statement)
end
else begin
  declare @cStatement varchar(255)
  declare G_cursor CURSOR for
  select case when UPPER(a.COLUMN_NAME) in ('COMPANYID','CMPANYID')
    then 'update '+a.TABLE_NAME+' set '+a.COLUMN_NAME+' = '+ cast(b.CMPANYID as char(3))
    else 'update '+a.TABLE_NAME+' set '+a.COLUMN_NAME+' = '''+ db_name()+'''' end
  from INFORMATION_SCHEMA.COLUMNS a, DYNAMICS.dbo.SY01500 b
    where UPPER(a.COLUMN_NAME) in ('COMPANYID','CMPANYID','INTERID','DB_NAME','DBNAME')
      and b.INTERID = db_name() and COLUMN_DEFAULT is not null
    order by a.TABLE_NAME
  set nocount on
  OPEN G_cursor
  FETCH NEXT FROM G_cursor INTO @cStatement
  WHILE (@@FETCH_STATUS <> -1)
  begin
    exec (@cStatement)
    FETCH NEXT FROM G_cursor INTO @cStatement
  end
  close G_cursor
  DEALLOCATE G_cursor
  set nocount off
end

Troubleshooting Script Execution Errors¶

Occasionally, when running the SQL script to update company references, you might encounter a primary key error. This indicates that updating a COMPANYID or INTERID value on a specific table would result in a duplicate key violation, meaning a row with that new company identifier already exists where the primary key constraint requires unique combinations.

If you receive such an error (e.g., a primary key constraint error on PKRVLPD033), you must investigate the table mentioned in the error message. Note that error messages often prefix the table name with “PK” to indicate a Primary Key constraint; the actual table name will be the part following “PK” (e.g., RVLPD033). You need to manually inspect this specific table in the test company database using SQL Server Management Studio or Query Analyzer. Identify the rows causing the conflict and manually adjust the INTERID and COMPANYID columns to resolve the duplicate key issue before rerunning the script or manually correcting the affected rows.

Remember, as noted previously, if you are using Human Resources, double-check the COMPANYCODE_I value specifically in the TAST0130 table, as the general script might not correct this particular reference or it might be the source of a duplicate key error if not addressed.

Conclusion¶

Creating and properly configuring a test company in Microsoft Dynamics GP is a vital step for safe and effective system management. By understanding the necessary post-restoration adjustments, particularly concerning Human Resources, Audit Trails, and core company identifier references, you can ensure your test environment is isolated, functional, and accurately reflects your live system for testing purposes. Always perform these steps meticulously to avoid unintended consequences in your production environment.

Have you encountered other specific modules or configurations that require special attention when creating a Dynamics GP test company? Share your experiences and tips in the comments below!