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!



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/

Comments

Post a Comment

Popular posts from this blog

DBMS: 12 Microsoft Dynamics GP: 0 error when updating to Microsoft Dynamics GP 2013 R2

Image
Hello all! It's been quite a while since I've actually written anything of remote interest to anyone who follows my blog, but at the same time, you will be pleased to know that I've been quite busy in the consulting front, with upgrades, server migrations, complex multi-instance postings involving 10's of thousands of transactions, wrapping up some Field Service Automation projects, and the list goes on and on. This time around I want to bring to the forefront, an issue I encountered updating  from Microsoft Dynamics GP 2013 SP2 to Microsoft Dynamics GP 2013 R2 plus the latest service pack. Background My client requested a server migration to a new environment where they wanted to deploy Dynamics GP 2013 R2 web client (plus the latest service pack) and upgrade their relational database management system to Microsoft SQL Server 2014. This is something I'm absolutely comfortable with (for the most part) given also that my client was sitting at Microsoft Dynamics
Read more

Cannot insert the value NULL into column 'CONTACT' error when clicking on Items List in Navigation Pane

Image
Moving on from my previous article on a similar subject - see Cannot insert the value NULL into column 'BASEUOFM' error when clicking on Items List in Navigation Pane , I recently came across this error,  Cannot insert the value NULL into column 'CONTACT' when clicking on the All Purchasing Transactions list under the Purchasing Navigation Pane option, after performing an upgrade from Microsoft Dynamics GP 9.0 to Microsoft Dynamics GP 2010 R2. All Purchasing Transactions list error - Purchasing Navigation List The name of the global temp table - in this case, tempdb.dbo.##2093338- varies in almost all cases, but the end result of the error is the same. The issue has been identified running Microsoft Dynamics GP 2010 RTM, SP1 or SP2. Upon further review, the issue is due to bad data in the Vendor ID (VENDORID) column in the Purchasing Receipt History table (POP30300). In summary, if you have a purchasing receipt with a blank vendor ID or a vendor ID that doe
Read more

How to uninstall Microsoft Dynamics GP 10.0 if you cannot uninstall it by using the "Add or Remove Programs" feature

INTRODUCTION This article describes how to uninstall an existing Microsoft Dynamics GP v10 system if you cannot uninstall it by using the Add or Remove Programs feature (or Programs and Features in Windows Vista) in Control Panel. It is recommended that you verify that you cannot uninstall by using Add or Remove Programs first. MORE INFORMATION First, verify that you cannot uninstall Microsoft Dynamics GP by using Add or Remove Programs (or Programs and Features) To verify that you cannot uninstall Microsoft Dynamics GP by using Add or Remove Programs (or Programs and Features in Windows Vista), follow these steps. For Windows XP or Windows Server 2003: 1. Click Start, click Run, type control appwiz.cpl in the Open box, and then press ENTER. 2. Click to select Microsoft Dynamics GP 10.0 product from the application list, and then click Remove. For Windows Vista: 1. Click Start, type Programs and Features in the Search box, and then press ENTER. 2. Click to select the product to be un
Read more