--- title: "Create a Bulk Copy Format File (ODBC) | Microsoft Docs" ms.custom: "" ms.date: "03/14/2017" ms.prod: sql ms.prod_service: "database-engine, sql-database, sql-data-warehouse, pdw" ms.reviewer: "" ms.technology: native-client ms.topic: "reference" helpviewer_keywords: - "bulk copy [ODBC], file formats" - "bulk copy [ODBC], data files" ms.assetid: 0572fef3-daf5-409e-b557-c2a632f9a06d author: markingmyname ms.author: maghan monikerRange: ">=aps-pdw-2016||=azuresqldb-current||=azure-sqldw-latest||>=sql-server-2016||=sqlallproducts-allversions||>=sql-server-linux-2017||=azuresqldb-mi-current" --- # Create a Bulk Copy Format File (ODBC) [!INCLUDE[appliesto-ss-asdb-asdw-pdw-md](../../../includes/appliesto-ss-asdb-asdw-pdw-md.md)] This sample shows how to use bulk copy functions to create both a data file and a format file. This sample was developed for ODBC version 3.0 or later. > [!IMPORTANT] > 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](https://go.microsoft.com/fwlink/?LinkId=64532). ### 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](../../../relational-databases/native-client-odbc-extensions-bulk-copy-functions/bcp-init.md) 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](../../../relational-databases/native-client-odbc-extensions-bulk-copy-functions/bcp-columns.md) to set the number of columns. 6. Call [bcp_colfmt](../../../relational-databases/native-client-odbc-extensions-bulk-copy-functions/bcp-colfmt.md) for each column to define its characteristics in the data file. 7. Call [bcp_writefmt](../../../relational-databases/native-client-odbc-extensions-bulk-copy-functions/bcp-writefmt.md) to create a format file describing the data file to be created by the bulk copy operation. 8. Call [bcp_exec](../../../relational-databases/native-client-odbc-extensions-bulk-copy-functions/bcp-exec.md) 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. ## Example You will need an ODBC data source called AdventureWorks, whose default database is the AdventureWorks sample database. (You can download the AdventureWorks sample database from the [Microsoft SQL Server Samples and Community Projects](https://go.microsoft.com/fwlink/?LinkID=85384) home page.) This data source must be based on the ODBC driver that is supplied by the operating system (the driver name is "SQL Server"). If you will build and run this sample as a 32-bit application on a 64-bit operating system, you must create the ODBC data source with the ODBC Administrator in %windir%\SysWOW64\odbcad32.exe. This sample connects to your computer's default [!INCLUDE[ssNoVersion](../../../includes/ssnoversion-md.md)] instance. To connect to a named instance, change the definition of the ODBC data source to specify the instance using the following format: server\namedinstance. By default, [!INCLUDE[ssExpress](../../../includes/ssexpress-md.md)] installs to a named instance. Execute the first ( [!INCLUDE[tsql](../../../includes/tsql-md.md)]) code listing to create the table that the sample will use. Compile the second (C++) code listing with odbc32.lib and odbcbcp.lib. Execute the third ( [!INCLUDE[tsql](../../../includes/tsql-md.md)]) code listing to delete the table that the sample used. ``` use AdventureWorks IF EXISTS (SELECT name FROM sysobjects WHERE name = 'BCPDate') DROP TABLE BCPDate GO CREATE TABLE BCPDate (cola int, colb datetime) insert BCPDate(cola) values(1) insert BCPDate(cola) values(2) insert BCPDate(cola) values(3) insert BCPDate(cola) values(4) ``` ``` // compile with: odbc32.lib odbcbcp.lib #include #include #include #include #include #include SQLHENV henv = SQL_NULL_HENV; HDBC hdbc1 = SQL_NULL_HDBC; void Cleanup() { if (hdbc1 != SQL_NULL_HDBC) { SQLDisconnect(hdbc1); SQLFreeHandle(SQL_HANDLE_DBC, hdbc1); } if (henv != SQL_NULL_HENV) SQLFreeHandle(SQL_HANDLE_ENV, henv); } int main() { RETCODE retcode; // BCP variables. SDWORD cRows; // Allocate the ODBC environment and save handle. retcode = SQLAllocHandle (SQL_HANDLE_ENV, NULL, &henv); if ( (retcode != SQL_SUCCESS_WITH_INFO) && (retcode != SQL_SUCCESS)) { printf("SQLAllocHandle(Env) Failed\n\n"); Cleanup(); return(9); } // Notify ODBC that this is an ODBC 3.0 app. retcode = SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION, (SQLPOINTER) SQL_OV_ODBC3, SQL_IS_INTEGER); if ( (retcode != SQL_SUCCESS_WITH_INFO) && (retcode != SQL_SUCCESS)) { printf("SQLSetEnvAttr(ODBC version) Failed\n\n"); Cleanup(); return(9); } // Allocate ODBC connection handle, set bulk copy mode, and connect. retcode = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc1); if ( (retcode != SQL_SUCCESS_WITH_INFO) && (retcode != SQL_SUCCESS)) { printf("SQLAllocHandle(hdbc1) Failed\n\n"); Cleanup(); return(9); } retcode = SQLSetConnectAttr(hdbc1, SQL_COPT_SS_BCP, (void *)SQL_BCP_ON, SQL_IS_INTEGER); if ( (retcode != SQL_SUCCESS_WITH_INFO) && (retcode != SQL_SUCCESS)) { printf("SQLSetEnvAttr(ODBC version) Failed\n\n"); Cleanup(); return(9); } // Sample uses Integrated Security, create SQL Server DSN using Windows NT authentication. retcode = SQLConnect(hdbc1, (UCHAR*)"AdventureWorks", SQL_NTS, (UCHAR*)"",SQL_NTS, (UCHAR*)"", SQL_NTS); if ( (retcode != SQL_SUCCESS) && (retcode != SQL_SUCCESS_WITH_INFO) ) { printf("SQLConnect() Failed\n\n"); Cleanup(); return(9); } // Initialize the bulk copy. retcode = bcp_init(hdbc1, "BCPDate", "BCPODBC.bcp", NULL, DB_OUT); if (retcode != SUCCEED) { printf("bcp_init() Failed\n\n"); Cleanup(); return(9); } // Set the number of output columns. retcode = bcp_columns(hdbc1, 2); if (retcode != SUCCEED) { printf("bcp_init() Failed\n\n"); Cleanup(); return(9); } // Describe the format of column 1 in the data file. retcode = bcp_colfmt(hdbc1, 1, SQLCHARACTER, -1, 5, NULL, 0, 1); if (retcode != SUCCEED) { printf("bcp_init() Failed\n\n"); Cleanup(); return(9); } // Describe the format of column 2 in the data file. retcode = bcp_colfmt(hdbc1, 2, SQLCHARACTER, -1, 20, NULL, 0, 2); if (retcode != SUCCEED) { printf("bcp_init() Failed\n\n"); Cleanup(); return(9); } // Create the format file. retcode = bcp_writefmt(hdbc1, "c:\\BCPFMT.fmt"); if (retcode != SUCCEED) { printf("bcp_init() Failed\n\n"); Cleanup(); return(9); } // Execute the bulk copy. retcode = bcp_exec(hdbc1, &cRows); if (retcode != SUCCEED) { printf("bcp_init() Failed\n\n"); Cleanup(); return(9); } printf("Number of rows bulk copied out = %d.\n", cRows); // Cleanup SQLDisconnect(hdbc1); SQLFreeHandle(SQL_HANDLE_DBC, hdbc1); SQLFreeHandle(SQL_HANDLE_ENV, henv); return(0); } ``` ``` use AdventureWorks IF EXISTS (SELECT name FROM sysobjects WHERE name = 'BCPDate') DROP TABLE BCPDate GO ``` ## See Also [Bulk Copying with the SQL Server ODBC Driver How-to Topics (ODBC)](../../../relational-databases/native-client-odbc-how-to/bulk-copy/bulk-copying-with-the-sql-server-odbc-driver-how-to-topics-odbc.md) [Using Data Files and Format Files](../../../relational-databases/native-client-odbc-bulk-copy-operations/using-data-files-and-format-files.md)