• Article

How to: Create a Bulk Copy Format File (ODBC)

A complete sample shows how to use bulk copy functions to create both a data file and a format file. The complete sample code is in the file BulkCopyWithFormat.cpp, which you can download from the SQL Server Downloads page on MSDN. This sample was developed using Microsoft Visual C++ 2005 and was developed for ODBC version 3.0 or later.

Security noteSecurity Note

When possible, use Windows Authentication. If Windows Authentication is not available, prompt users to enter their credentials at run time. Avoid storing credentials in a file. If you must persist credentials, you should encrypt them with the Win32 crypto API.

To create a bulk copy format file

  1. Allocate an environment handle and a connection handle.

  2. Set SQL_COPT_SS_BCP and SQL_BCP_ON to enable bulk copy operations.

  3. Connect to SQL Server.

  4. Call bcp_init to set the following information:

    • The name of the table or view to bulk copy from or to.

    • The name of the data file that contains the data to copy into the database or that receives data when copying from the database.

    • The name of a data file to receive any bulk copy error messages (specify NULL if you do not want a message file).

    • The direction of the copy: DB_OUT to the file from the table or view.

  5. Call bcp_columns to set the number of columns.

  6. Call bcp_colfmt for each column to define its characteristics in the data file.

  7. Call bcp_writefmt to create a format file describing the data file to be created by the bulk copy operation.

  8. Call bcp_exec to execute the bulk copy operation.

A bulk copy operation run in this way creates both a data file containing the bulk copied data and a format file describing the layout of the data file.