How to use the Bulk Copy Process (BCP) to export Microsoft Dynamics GP data from one database and import data into a new database


INTRODUCTION


This article discusses how to use the Bulk Copy Process (BCP) to export data and to import data by using Microsoft Dynamics GP or Microsoft Business Solutions - Great Plains.

More Information


  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 clickSave 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.
    • For Microsoft SQL Server 2000 or MSDE 2000
      1. Open the BCPData folder.
      2. 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.
      3. Open the BCPData folder.
      4. Right-click the Copyout.bat file, and then click Edit to open the file in Notepad or in another text editor
      5. Select all the text. To do this, click Edit, and then click Select All. Or, press Alt+A.
      6. Copy all the text from the Copyout.bat file to the Clipboard. To do this, click Edit, and then click Copy. Or, press Ctrl+C.
      7. Open a Command Prompt window. To do this, click Start, click Run, type cmd, and then click OK.
      8. Paste the contents of the Copyout.bat file into the Command Prompt window. To do this, right-click the window, and then click Paste.
    • For Microsoft SQL Server 2005
      1. Open the BCPData folder.
      2. 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 Microsoft Dynamics GP. 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.
    • If you are using SQL Server 2000 Desktop Engine, remove the database from within Support Administrator Console by running the following script, where TWO is the name of the database.
       DROP DATABASE TWO 
  9. Re-create the company database and procedures. To do this, start Microsoft Dynamics GP 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.
      /* Script to remove all data from all user tables in the company database */
      SET QUOTED_IDENTIFIER OFF
      if exists (select * from sysobjects where name = 'RM_NationalAccounts_MSTR_FKC')
      ALTER TABLE dbo.RM00105
      DROP CONSTRAINT RM_NationalAccounts_MSTR_FKC
      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.RM00105 ADD
      CONSTRAINT RM_NationalAccounts_MSTR_FKC FOREIGN KEY
      (
      CPRCSTNM
      ) REFERENCES dbo.RM00101 (
      CUSTNMBR
      )
      GO
    • If you are using SQL Server 2000 Desktop Engine, run the following scripts separately in the Support Administrator console.
      • Script 1
         SET QUOTED_IDENTIFIER OFF
        if exists (select * from sysobjects where name = 'RM_NationalAccounts_MSTR_FKC')
        ALTER TABLE dbo.RM00105
        DROP CONSTRAINT RM_NationalAccounts_MSTR_FKC
      • Script 2
         SET QUOTED_IDENTIFIER OFF
        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
      • Script 3
         ALTER TABLE dbo.RM00105 ADD 
        CONSTRAINT RM_NationalAccounts_MSTR_FKC FOREIGN KEY
        (
        CPRCSTNM
        ) REFERENCES dbo.RM00101 (
        CUSTNMBR
        )
  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. For information about consulting services that may be available to change the Microsoft SQL Server Sort Order for you, use one of the following options, depending on whther you are a customer or a partner:

Customers:
For more information about data manipulation consulting services, contact your partner of record. If you do not have a partner of record, visit the following web site to identify a partner: Microsoft Pinpoint.

Partners:
For more information about data manipulation consulting services, contact Microsoft Advisory Services at 800-MPN-SOLVE or via email at askpts@microsoft.com


To do this, follow steps 1 through 11 in the "More Information" section. 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
  • If you are using Microsoft SQL Server 2000 Desktop Engine, run the following scripts separately in the Support Administrator console.
    • Script 1
      if exists (select * from sysobjects where name = 'orgEntity_SETP')
      ALTER TABLE dbo.ORG40100
      DROP CONSTRAINT orgEntity_SETP
    • Script 2
      if exists (select * from sysobjects where name = 'orgRelation_MSTR')
      ALTER TABLE dbo.ORG00100
      DROP CONSTRAINT orgRelation_MSTR
    • Script 3
       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
    • Script 4
      ALTER TABLE dbo.ORG40100 ADD 
      CONSTRAINT orgEntity_SETP FOREIGN KEY
      (
      ENTYLVL
      ) REFERENCES dbo.ORG40000 (
      ENTYLVL
      )
    • Script 5
      ALTER TABLE dbo.ORG00100 ADD 
      CONSTRAINT orgRelation_MSTR FOREIGN KEY
      ( ENTITYID ) REFERENCES dbo.ORG40100 ( ENTITYID )