Friday, June 12, 2009

How to transfer data between two Microsoft Dynamics GP companies using Microsoft SQL Server Import and Export Wizard

One of the tasks that is often necessary in any Dynamics GP implementation is to get data across from the test company to the production "Go Live" company once we are ready to make the cut over. Those records are usually configuration and master records, but can also be beginning balances for subsidiary modules and General Ledger. This article will show you how to use Microsoft SQL Server 2008 Import and Export Wizard to transfer data across company databases.

The article assumes you are familiar with SQL Server Management Studio and that you know your way around databases and objects.

1. Click Start, point to All Programs, point to Microsoft SQL Server 2008, and then click SQL Server Management Studio.

2. In the Connect to Server window, click SQL Server Authentication in the Authentication list, and then type the sa user name and sa password. Click Connect.

3. In Object Explorer, double-click Databases. Right-click the database from which you want to export the data, point to Tasks, and then click Export Data.






4. In the Welcome window, click Next.



5. In the Data Source window, you can choose between Windows Authentication and SQL Server Authentication. It's always been my preference to use Windows Authentication, especially if your Windows user belongs to the SQL Server sysadmin role. If you are not sure, click Use SQL Server Authentication in the Authentication area, and then type the sa user name and sa password. Click Next.



6. In the Destination window, type the server name in the Server Name area. In the Authentication area, click Use SQL Server Authentication, and then type the sa user name and sa password. In the Database list, click to select the destination database. Then, click Next.



7. In the Specify Table Copy or Query window, click Next to accept the default entries.



NOTE: The table(s) to which you are exporting data must exist in the destination database. If the table does not exist, you must follow the installation routines for the add-on or third party to which the tables are related. You can also use the SQL Maintenance window to identify and create the missing table(s). Start Microsoft Dynamics GP and go to File > Maintenance > SQL. While the SQL Server Import and Export Wizard has the ability to create the missing destination tables, this feature will not replicate certain table constraints and indexes that may exist in the source table.

8. Select the check boxes next to the tables that you want to copy to the destination database.



9. Highlight all tables to be exported, then click on the Edit Mappings button. Select Delete rows in existing destination tables and Enable identity insert. If you use Analytical Accounting, and you transfer Analytical Accounting tables, you cannot ignore DEX_ROW_ID for the following tables: Customer Master - RM00101, Vendor Master - PM00200, Item Master - IV00101, and Site Setup - IV40700. Hence, as a standard practice I enable this option to transfer data. Click on OK to continue.




In the Select Source Tables and Views window, click Next.

10. In the Save and Run Package window, click Next to accept the default entries.




11. Now, sit back and watch your data go across!

video



General Notes

These steps were performed using Microsoft SQL Server 2008. The Import and Export Wizard is fairly standard between this version of SQL Server and Microsoft SQL Server 2005 and you should be able to obtain very similar results. Consult your Microsoft SQL Server documentation for further information on the Import and Export Wizard feature.

Related Articles

KB article 874208 - How to transfer setup information between company databases by using SQL Server 2005 or SQL Server 2000.

Moving data between Microsoft Dynamics GP companies - This site, click here.

The often overlooked, yet powerful Table Import - This site, click here.

Until next post!

MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/

No comments: