Let us take a closer look at the SQL server allow BCP by our MSSQL support services at Bobcares.

Microsoft SQL Server Bulk Copy (BCP)

Use BCP utility to process and load initial-state data into a SQL Server database. The BCP utility can only be used with the Event Replicator Target Adapter to load initial-state data as it cannot be used for transactional data.

Prerequisites for SQL server allow BCP

The below requirements must be met before the BCP utility, so that can load initial-state data to a Microsoft SQL Server database via the Event Replicator Target Adapter:

• The Microsoft SQL Server utilities support the BCP utility that is installed on the same system as the Event Replicator Target Adapter. The BCP utility version 9 or greater must be present.

• Make sure that there is enough space to accommodate the *.BCPDAT files that create while processing.

Enabling BCP Utility Initial-State Processing

To enable BCP utility initial-state processing, follow the below steps:

• Using the Administration tool, configure a target definition for the Microsoft SQL Server database.

• Create a specific target processing option definition to Microsoft SQL Server database.

• In the Loader options area, you need to check the box Use Loader and provide a value for the Path to Loader Executable field, identifying the full path of the Microsoft SQL Server BCP utility executable. For example: C:\Program Files\Microsoft SQL Server\90\Tools\Binn\bcp.exe

•  All tables created by MS SQL Server uses the user login name as the default schema name. In SQL Server 2005 and later versions uses “dbo” as the default schema name, This is different from the user login name.

• Need to specify the following options, as required for installation in the Options field. By default, all the rows processed in the data file will import as one batch. If any transaction for batch fails,then the insertions from the current batch are rolled back.

• When all processing options are specified then start initial-state processing that directs the output to the Event Replicator Target Adapter via a GFFT you created for the Microsoft SQL Server database. The Event Replicator Target Adapter will collect the initial-state records and send them to the BCP utility to be loaded into your database.

Processing Output

The output from this processing includes:

• Various BCP utility processing files (*.FMT files)

• BCP utility log files from the processing (*.BCPERR and *.BCPOUT files)

• Binary data files (*.BCPDAT files) contains the initial-state data received by the Event Replicator Target Adapter/ Prior to being processed by the BCP utility and possibly *.DBGDAT files.

• An output file (*.out file) containing results from the final loader process run.

All output files will store in the \logs subdirectory of the Event Replicator Target Adapter installation.

• Processing Files

• Log Files

Processing Files

The Event Replicator Target Adapter’s BCP utility generates three processing files. The following processing files will store in the “\logs” subdirectory for Event Replicator Target Adapter installation:

• A single xxxx_yyyymmdd_hhmmss.BAT file adapts for all BCP utility processing. Here XXXX is the base table name, yyyymmdd is the date, hhmmss is the time, and .BAT file is used to run the BCP utility. This usually generates a format file (*.FMT) for each table and loads the binary *.BCPDAT file into a database.

• One xxxx_yyyymmdd_hhmmss.FMT file for each table: This is the format file for the BCP utility processing.

• One or more xxxx_yyyymmdd_hhmmss.BCPDAT file for each table: These files contain the data of database tables of the BCP utility. These files will be in compressed binary format.

• If the file debugs logging option is turned on, then the resulting debugging information will store in a debug file with a name in the format xxxx_yyyymmdd_hhmmss.DBGDAT, Multiple debug files produce a single table.

Log Files

Several kinds of log files will produce by Event Replicator. The Target Adapter BCP utility will process a primary log file and multiple processing log files. All log files will store in the \logs subdirectory of your Event Replicator Target Adapter installation.

• The BCP utility primary log file makes recommendations for the processing log files. The primary log file contains the format “xxxx_yyyymmdd_hhmmss_nnn.out”.  XXXX is the base table name. yyyymmdd referred to the date of the log file and hhmmss_nnn is the time. A single primary log file created for all BCP utilities.

• Multiple processing log files create, one for each BCP process executed by the Event Replicator Target Adapter. These processing log files will have the format “xxxx_yyyymmdd_hhmmss.BCPOUT”.

• Any errors encountered during BCP utility processing, will noted to error files with the format xxxx_yyyymmdd_hhmmss.BCPERR.

[Looking for a solution to another query? We are just a click away.]

Conclusion

To sum up, If for some reason BCP utility processing fails, we recommend reviewing primary *.out log files first while resolving the problem. Then proceed to the processing log files, as necessary.