To set up the test company, follow these steps:
1. In Great Plains Utilities, create a new company database that you can use as the test company. Make sure that you give the database a unique DB/company ID and company name that will designate the database as a test company. For example, you could use a DB/company ID of "TEST" and a company name of "TEST COMPANY."
2. Log in to the test company. To do this, click Tools , click Setup , click System , and then click User Access .
3. In the User Access area, select the user to whom you want to grant access to the test company database. Then, click to select the check box next to the test company name to grant access to the test company database. Repeat this step for all users to whom you want to grant access to the test company database.
4. Make a database backup of the live company database.
5. Restore the live company backup file that you created in step 4 into the test company database
6. After the live company database has been restored over the top of the test company database, the test company contains references that have the same COMPANYID and INTERID information that the live company has. To correctly reflect the information for the test company, run the following script below against the test company in Query Analyzer or in SQL Server Management Studio. This script updates the COMPANYID and INTERID in the test database with the information that is listed in the DYNAMICS database SY01500 table for this test company.
/******************************************************************************/
/* 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
/******************************************************************************/
/* end of script*/
/******************************************************************************/
Note If this script fails with a duplicate key error, you must manually change the INTERID and COMPANYID columns in the table on which you are receiving the primary key error in the test company.
7. Verify that the database owner of the test database is DYNSA. To do this, run the following script against the test company in Query Analyzer or in SQL Server Management Studio:
sp_changedbowner 'DYNSA'
The test company should now have a copy of the live company data and be ready for use in Microsoft Great Plains.
