How to transfer setup information between company databases by using Microsoft SQL Server

SOURCE:  http://support.microsoft.com/kb/874208

Microsoft SQL Server 2005 or Microsoft SQL Server 2008


To transfer setup information by using SQL Server 2005 or Microsoft SQL Server 2008, follow these steps:

1.Click Start, point to All Programs, point to Microsoft SQL Server 2005 or 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, 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.

7.In the Authentication area, click Use SQL Server Authentication, and then type the sa user name and sa password.

8.In the Database list, click to select the destination database. Then, click Next.

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

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



Note The table to which you are exporting must exist in the database. If the table does not exist, create the table. To do this, start Microsoft Dynamics GP, click File, point to Maintenance, and then click SQL.

11.For each table that you selected in step 10, follow these steps:

•Select the table, and then click Edit Mappings.

•Verify that you have selected Delete rows in destination table. Verify that the Enable identity insert check box is not selected.

•Select the DEX_ROW_ID in the Destination column, and then point to ignore in the list. Click the OK button at the bottom of the Column Mappings window.



Note 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

■Site Setup - IV40700

For these tables, you must click to select the Enable Identity Insert check box.

•Click OK.

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

13.In the "Save and Execute Package" window, click Next to accept the default entries.

14.In the Complete the Wizard window, review the information, and then click Finish.

After you click Finish, the data starts to move from the source database to the destination database. You can track the progress in the Executing Package window. When the data move is completed, you receive a message.

Back to the top

SQL Server 2000

Note The DTS Export Wizard is an option in Enterprise Manager and in SQL Server Management Studio. You can use this wizard to copy tables between company databases. Enterprise Manager is not available together with MSDE 2000. SQL Server Management Studio is available for SQL Server Express 2005.



To transfer setup information by using SQL Server 2000, follow these steps:

1.Click Start, point to All Programs, point to Microsoft SQL Server, and then click Enterprise Manager.

2.Double-click Microsoft SQL Servers, double-click SQL Server Group, double-click your instance of SQL Server, and then double-click Databases.

3.Right-click the source database, point to All Tasks, and then click Export Data.

4.In the Data Transformation Service Export Wizard window, click Next.

5.In the Data Source list, click Microsoft OLE DB Provider for SQL Server, and then type the server name in the Server box.

6.Click Use SQL Server Authentication, and then type the sa user name and sa password for the server in the corresponding boxes. Click the source database in the Database list, and then click Next.

7.In the Destination list, click Microsoft OLE DB Provider for SQL Server, and then type the server name in the Server box.

8.Click Use SQL Server Authentication, and then type the sa user name and sa password for the server in the corresponding boxes. Click the destination database in the Database list, and then click Next.

9.Click Copy table(s) and views(s) from the source database.

10.Click to select the check boxes for the tables that you want to copy to the destination database.



Note The table to which you are exporting must exist in the database. If the table does not exist, create the table. To do this, start Microsoft Dynamics GP, click File, point to Maintenance, and then click SQL.

11.Next to each table that you selected, click the lookup button under the Transform column.

12.Verify that Delete rows in destination table is selected and that the Enable identity insert check box is not selected. Click OK, and then click Next.



Note If Delete rows in destination table is unavailable, the table to which you are exporting does not exist. You must create the table in the databases. To do this, start Microsoft Dynamics GP, click File, point to Maintenance, and then click SQL.

13.In the When box, click to select a check box for when the DTS package should run. Or, accept the default option to immediately run the DTS package. Then, click Next.

14.In the Summary field, review the information. If the information is correct, click Finish. If the information is incorrect, click Back to go back to the screen that requires updated information.

After you click Finish, the data starts to move from the source database to the destination database. You can track the progress in the Executing Package window. When the data move is completed, you receive a message.