Skip to content

From The Community #4 – Creating A Test Company

April 5, 2010

A question in the Dynamics GP forums was posted on the following issue:

We’re in the midst of our GP implementation. I would love to be able to copy a "production" (of course, we’re not really in production yet) company into a test company for testing and maybe training. Should we do the following?

  • Create a new "blank" company in GP Utilities

  • Backup the production company’s DB

  • Restore the production company’s DB to the test company’s DB.

Can anyone help me out with acceptable procedures, and please stop me from make huge mistakes? Lol.”

https://community.dynamics.com/forums/t/32456.aspx

As Richard Whaley points out in the post, these would be the proper steps if you were not using manufacturing and would only allow the SA account to be logged in.  The additional steps needed to make a test company live for your folks to “play” in would be to grant each user access to the test company and run the following script provided by Microsoft:

/******************************************************************************/
/*    Description:    */
/*    Updates any table that contains a company ID or database name value    */
/*    with the appropriate values as they are stored in the DYNAMICS.dbo.SY01500 table    */
/*    */
/******************************************************************************/

if not exists(select 1 from tempdb.dbo.sysobjects where name = ‘##updatedTables’)
     create table [##updatedTables] ([tableName] char(100))
truncate table ##updatedTables
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, INFORMATION_SCHEMA.TABLES c
where UPPER(a.COLUMN_NAME) in (‘COMPANYID’,’CMPANYID’,’INTERID’,’DB_NAME’,’DBNAME’, ‘COMPANYCODE_I’)
     and b.INTERID = db_name() and a.TABLE_NAME = c.TABLE_NAME and c.TABLE_CATALOG = db_name() and c.TABLE_TYPE = ‘BASE TABLE’
set nocount on
OPEN G_cursor
FETCH NEXT FROM G_cursor INTO @cStatement
WHILE (@@FETCH_STATUS <> -1)
begin
         insert ##updatedTables select
substring(@cStatement,8,patindex(‘%set%’,@cStatement)-9)
         Exec (@cStatement)
     FETCH NEXT FROM G_cursor INTO @cStatement
end
DEALLOCATE G_cursor
select [tableName] as ‘Tables that were Updated’ from ##updatedTables

The only thing that I have found about this script is that it does not “catch” the company’s main address id.  The company’s address id can be recreated by going into Tools->Setup->Company->Company and clicking the “OK” button once.  This will make GP realize that it doesn’t have the address information saved and it will save it.

Advertisements

From → Dynamics GP

Leave a Comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: