Bulk Copy Process (BCP) to Export Microsoft Dynamics GP Data into New Database

How to use the Bulk Copy Process (BCP) to export data and to import data by using Microsoft Dynamics GP.

1. Make a backup of your company database.
2. Copy and paste the following CreateBulkCopyOut.sql script into Microsoft SQL Query Analyzer.

/* Script to create bcp commands to export data for all tables. */

SET QUOTED_IDENTIFIER OFF

select 'bcp "TWO..' + name + '" out ' + name + '.out -e ' + name + '.err -c -b 1000 -U sa -P password -t "|" -S SERVERNAME -r "#EOR#\n"' from sysobjects where type = 'U' order by name

In the script, replace the following placeholders with the correct information:

• Replace TWO with the name of your company database.
• Replace password with your sa password.
• Replace SERVERNAME with the name of your instance of Microsoft SQL Server.

Note To open Query Analyzer, click Start, point to Programs, point to Microsoft SQL Server, and then click Query Analyzer.

Note If you are using Microsoft SQL Server 2000 Desktop Engine (also known as MSDE 2000), run the statement in Microsoft Support Administrator Console. To open Support Administrator Console, click Start, point to Programs, point to Microsoft Support Administrator Console, and then click Support Administrator Console. Support Administrator Console requires a separate installation. You can install the program by using the Great Plains installation CD number 2.

3. Run the script against the database and then save the results to a batch file. To do this, follow these steps:
• If you are using Query Analyzer, click in the results pane, and then click Save As on the File menu. Create a folder named BCPData, name this file Copyout.bat, and then click Save.
• If you are using Support Administrator Console, click File, and then click Export. Create a folder and name it BCPData. Name the file Copyout.bat. Then click Save.
4. Copy and paste the following CreateBulkCopyIn.sql script into Query Analyzer.

/* Script to create bcp commands to import data for all tables. */

SET QUOTED_IDENTIFIER OFF      

select 'bcp "TWO..' + name + '" in ' + name + '.out -e ' + name + '.err       -c -b 1000 -U sa -P password -t "|" -S SERVERNAME -r "#EOR#\n"' from sysobjects where type = 'U' order by name

In the script, replace the following placeholders with the correct information:

• Replace TWO with the name of your company database.
• Replace password with your sa password.
• Replace SERVERNAME with the name of your instance of SQL Server.

Note To open Query Analyzer, click Start, point to Programs, point to Microsoft SQL Server, and then click Query Analyzer.

Note If you are using SQL Server 2000 Desktop Engine, run the statement in Support Administrator Console. To open Support Administrator Console, click Start, point to Programs, point to Microsoft Support Administrator Console, and then click Support Administrator Console. Support Administrator Console requires a separate installation. You can install the program by using the Great Plains installation CD number 2.

5. Run the script against the database and then save the results to a batch file.
• If you are using Query Analyzer, click in the results pane, and then click Save As on the File menu. Create a folder and name it BCPData. Name the file Copyin.bat, and then click Save.
• If you are using Support Administrator Console, click File and then click Export. Create a folder and name it BCPData. Name the file Copyin.bat. Then click Save.
6. Perform a bcp command to move the data out of the company database. To do this, use the appropriate method.

• Open the BCPData folder.
• Double-click the Copyout.bat file.
  
  Note The batch file starts the BCP process to copy the data from the database to a text file.

7. Delete the company from within Great Plains. To do this, log into Great Plains as the sa user. Click Tools, point to Setup, point to System and then click Company. Click the lookup glass to display all the companies listed. Select the company and then click Delete.
   
   Note For versions of Microsoft Great Plains that are earlier than Microsoft Great Plains 8.0, delete the company. To delete the company, do the following: On the Setup menu, click System, and then click Company to delete the company.
8. Remove the database.
• If you are using Microsoft SQL Server, open Enterprise Manager, expand the server name, expand Database, right-click the company database that you deleted in step 7, and then select Delete.

DROP DATABASE TWO

9. Re-create the company database and procedures. To do this, start Great Plains Utilities, log on as the sa user, and then click Create new company in the Additional Tasks dialog box.
   
   Note Use the same company name as the one that you deleted in step 7.
10. After the company is created, the tables must be truncated.

If you are using Microsoft SQL Server, run the following Truncate_Table_Company.sql script in Query Analyzer.

o    /* Script to remove all data from all user tables in the company database */

o    SET QUOTED_IDENTIFIER OFF

o    if exists (select * from sysobjects where name = 'RM_NationalAccounts_MSTR_FKC')

o    ALTER TABLE dbo.RM00105

o    DROP CONSTRAINT RM_NationalAccounts_MSTR_FKC

o    Go

o    declare @tablename char(255)

o    DECLARE t_cursor CURSOR for

o       select "truncate table " + name

o       from sysobjects where type = 'U'

o       set NOCOUNT on

o       open t_cursor

o       FETCH NEXT FROM t_cursor INTO @tablename

o       while (@@fetch_status <> -1)

o       begin

o       if (@@fetch_status <> -2)

o       begin

o       exec (@tablename)

o       end    

o       FETCH NEXT FROM t_cursor into @tablename

o       end

o    DEALLOCATE t_cursor

o    GO

o    ALTER TABLE dbo.RM00105 ADD

o       CONSTRAINT RM_NationalAccounts_MSTR_FKC FOREIGN KEY

o       (

o       CPRCSTNM

o       ) REFERENCES dbo.RM00101 (

o       CUSTNMBR

o       )

o    GO

11. Move the data back into the company database. To do this, open the BCPData folder, and then double-click the Copyin.bat batch file. Running this batch file starts the process of moving the data back into the company database. When the data is moved back into the database, all indexes are created and verified for each table.
    
    Note When the process is completed, the BCPData folder will contain .err files. If any one of these .err files is larger than 0 KB, the data was not imported for the company database successfully.

Additional steps

If you are using the BCP process to change your Microsoft SQL Server Sort Order, you must bulk copy data out of the DYNAMICS database and all Company databases.

Note Changing the Microsoft SQL Server Sort Order for the DYNAMICS database and for the Company database is not supported by Microsoft. This is only supported if MBS Professional Services makes the change for you.

To do this, follow steps 1 through 11. In step 10, you must also truncate the DYNAMICS database. To do this, use one of the following methods:

If you are using Microsoft SQL Server, run the following Truncate_Tables_Dynamics.sql script in Query Analyzer.

·         /* ** **

·         Truncate_Tables_Dynamics.sql

·         function: Will remove all data from all user tables in the DYNAMICS database

·          ** */

·          

·         SET QUOTED_IDENTIFIER OFF

·          

·         if exists (select * from sysobjects where name = 'orgEntity_SETP')

·         ALTER TABLE dbo.ORG40100

·         DROP CONSTRAINT orgEntity_SETP

·         GO

·         if exists (select * from sysobjects where name = 'orgRelation_MSTR')

·         ALTER TABLE dbo.ORG00100

·         DROP CONSTRAINT orgRelation_MSTR

·         Go

·         declare @tablename char(255)

·          

·         DECLARE t_cursor CURSOR for

·         select "truncate table " + name

·         from sysobjects where type = 'U'

·          

·         set NOCOUNT on

·         open t_cursor

·         FETCH NEXT FROM t_cursor INTO @tablename

·         while (@@fetch_status <> -1)

·         begin

·         if (@@fetch_status <> -2)

·         begin

·         exec (@tablename)

·         end

·          

·         FETCH NEXT FROM t_cursor into @tablename

·         end

·          

·         DEALLOCATE t_cursor

·         GO

·         ALTER TABLE dbo.ORG40100 ADD

·         CONSTRAINT orgEntity_SETP FOREIGN KEY

·         (

·         ENTYLVL

·         ) REFERENCES dbo.ORG40000 (

·         ENTYLVL

·         )

·         GO

·         ALTER TABLE dbo.ORG00100 ADD

·         CONSTRAINT orgRelation_MSTR FOREIGN KEY

·         ( ENTITYID ) REFERENCES dbo.ORG40100 ( ENTITYID )

·         GO