You’re offline. This is a read only version of the page.
I have a requirement to mirage 2 GP 2013 Servers with there own GP databases company 1 and Company 2 into single GP 2013 Server (company 1), is there a easy way to move the company databases from company 2 to the other server Company1.
I try the simple approach,
made sure both servers are on the same GP 2013 version
Make sure they are on the same SQL 2008 R2 level
Backup the company database from company 2
created a new company with the same name on company 1's server
restored the database in the newly created company
changed the CMPANYID in the DYNAMCIS DB to match the Newly created Company
I can login in to the system but can't run any reports
*This post is locked for comments
All good advice. I have done several consolidation projects. The most important consideration I have found is the compatibility of the Account Framework. You need to review the following tables to determine compatibility:
The account framework can be found in SY003001 and SY00302 in the Dynamics Database.
The SY_Account_Format_Setup (SY00300) table is maintained in each company Database.
Hi,
Have you ran the SQL script as per above.
Looks like i have the database imported, but i get the following error when attemptng to run a report
A get/change operation on table 'GL_Options_TEMP' failed accessing SQL DATA
failed. Number of results columns doesn't match table definition.
I am totally agree with Mr.Beat Bucher. The company setup details are saved in DYNAMICS database. If you restore the company database to new server company, it may differ for some standard system level settings.
Normally, I wont recommend to do merge the two GP server to one. Better you can use both GP server in single instance SQL server with different name system database like (DYNAMICS1 & DYNAMICS2). Because, GP2013 provide the feature to use custom name of GP system database.
For moving SQL server & copy live company to another follow the below links.
support.microsoft.com/.../878449
support.microsoft.com/.../871973
Hope this helps!!!
To add up to Babu's and Frank's suggestion (which are correct), you need to be very careful when doing a merger of GP companies from different SQL servers... Several settings that the GP companies share are hold in the system DB DYNAMICS...now this database obviously you cannot restore, otherwise you risk to scrap your existing GP companies from the server #1.
Before doing anything critical to your restored companies from server #2, you need to validate that the various system parameters are identical. especially if you're using Multi-Currency.
Did you use GP utilities to create the new company database or did you just create the DB on the server? Using GP Utilities is required to create all the proper objects in the DYNAMICS DB.
Hi,
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 system database SY01500 table for this test company.
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
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.
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
#1
André Arnaud de Cal...
291,188
Super User 2024 Season 2
#2
Martin Dráb
230,030
Most Valuable Professional
#3
nmaenpaa
101,156
Share
Report
All responses (
Answers (