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

Important Notes for Microsoft Dynamics GP

• All un posted transactions mostly goes to 10000 series table in every module
• All un posted transactions mostly detail/line times goes to 10001 tables
• All posted transactions mostly goes to 20000 series table in every module
• All posted transactions mostly detail/line times goes to 20001 tables
• All history transactions mostly goes to 30000 tables
• 40000 series tables are mostly related to setups
• Two types of posting setups for every module as follows:

• Post to General Ledger (Transactions posted from module reside in GL as un posted Batch)
• Post Through General Ledger (Transactions posted from module also posted in GL)

• If using transaction posting then every transaction goes to GL as un posted regardless of post TO and Through
• Cash Receipts Updated AR Module and GL Module but not updated Bank/Checkbook
• Bank Deposit then update checkbook/bank and have no effect on GL
• One of the method to call Crystal report in GP is through Dexterity/VBA Code
• Through Business Portal we can generate and process Purchase Requisition and then converted into GP as Purchase Order, But in GP 10 we can utilize Work Flows to directly create purchase order and to manage hierarchy and approvals without using Business Portal
• VBA, Modifier, Dexterity and .Net are used for GP Customization
• FRx is Reporting tool that can be utilized for financial reporting
• Financial Reporting are namely (Balance Sheet, P&L, Income Statement, Cash Flow, etc)
• From GP we can use, Smart List, Inquiry and Report for viewing data
• Two types of posting mostly follows in GP:
• Transaction Posting (User who enter transaction can immediately post it and it will reside in GL as un posted regardless of posting setup POST TO or POST THROUGH
• Batch Posting (one users make X No. of transactions in one batch, another user review that batch and then post that batch and in batch posting system take care of POST TO and POST THROUGH
• Debit memo will increase customer balance in Account receivable (mean we have to take more from customer)
• Credit Memo will decrease customer balance in Accounts Receivable (mean we have to take less from customer)
• Credit Memo will decrease supplier/vendor balance in Accounts Payable (Mean we have to pay less to Vendor)
• No Debit memo available in Accounts Payable
• National Accounts are utilized in sale/Accounts receivable as Distributor/Retailer or Parent/child relationship