Copying chart of accounts from one company to another using Microsoft SQL Server in Microsoft Dynamics GP

Issue

How can I copy the chart of accounts from Company 'A' to Company 'B'? I am using Microsoft SQL Server.

Resolution


This is possible by using Data Transformation Services (DTS). Be sure to make a backup of your databases before you begin.


SQL Server 2000

To copy your chart of accounts by using SQL Server 2000:


1. Go into Enterprise Manager.

2. From Databases, expand the company you wish to copy the chart of accounts TO (Company B), and select Tables.


3. From Tables, find the GL00100 table.


4. Right-click on the GL00100 table, choose All Tasks, then Import Data.


5. Then click on Next.


6. From Choose a Data Source, verify your server and database are selected (Company A) where you will be copying the data FROM. Choose to Use SQL Server Authentication, and enter the SA user and password. Click Next.


7. In the Choose a Destination window, select the appropriate Server and the database you want to import the tables TO (Company B).


8. Choose 'Copy tables from the source database' and select Next.


9. Mark the following tables for your chart of accounts (this includes MDA tables):

GL00100

GL00102

GL00103

GL00104

GL00105

GL40200

DTA00100

DTA00200

DTA00300

DTA00301

SY00300


10. For each of the tables selected, go into the ellipse (...) button and mark 'Delete rows in destination table' and unmark 'Enable identity insert.'


11. Choose Next and 'run immediately.'


Note -The default settings for DTS can cause duplicate records, so it is important to mark 'Delete' instead of 'Append.'

SQL Server 2005

To copy the chart of accounts by using SQL Server 2005, follow these steps:

1. Open SQL Server Management Studio.

2. Expand Databases to find the company (Company B) to which you want to copy the chart of accounts.

3. Right-click Company B, click Tasks, and then click Import Data.

4. Click Next.

5. In the "Choose a Data Source" window, enter the following information about the database from which you want to copy data.

a.Enter the server name.
b.Click Use SQL Server Authentication.
c.Enter the sa user name and password.
d.Select the database from which you want to copy data.
f.Click Next.

6. In the "Choose a Destination" window, enter the following information about the database to which you want to copy data.

a.Enter the server name.
b.Click Use SQL Server Authentication.
c.Enter the sa user name and password.
d.Select the database to which you want to copy data.
f.Click Next.

7. In the "Specify Table Copy or Query" dialog box, click "Copy data from one or more tables or views," and then click Next.

8. In the "Select Source Tables and Views" window, select all the following tables:
GL00100
GL00102
GL00103
GL00104
GL00105
GL40200
DTA00100
DTA00200
DTA00300
DTA00301
SY00300

9. For each table that you selected, click the Edit Mappings button, click to select the "Delete rows in destination table" check box, and then click to clear the "Enable identity insert" check box.

10. Click Next, and then click "Execute immediately."

11. Click Finish, and then click Finish.



This article was TechKnowledge Document ID:7255