Condividi tramite


bcp_control

Changes the default settings for various control parameters for a bulk copy between a file and SQL Server.

Sintassi

RETCODE bcp_control (
        HDBC hdbc,
        INT eOption,
        void* iValue);

Arguments

  • hdbc
    Is the bulk copy-enabled ODBC connection handle.
  • eOption
    Is one of the following:
  • BCP6xFILEFMT
    When iValue is TRUE, specifies that columns stored in a data file use the SQL Server version 6.x format instead of the SQL Server version 7.0 format.

    Version 6.x format does not support several data types when bulk copying out from an SQL Server 7.0 database. Nullable bit values are converted to 0. char, varchar, binary, and varbinary values longer than 255 bytes are trunctated. uniqueidentifier, nchar, nvarchar, and ntext columns are not supported. Zero-length data is converted to NULL.

    When bulk copying in from a character data file column, blank input values are converted to NULL when iValue is set to FALSE and as follows when iValue is TRUE.

    Target column data type Resulting value

    Any data type in the numeric category

    0

    binary or varbinary

    0x00

    datetime or smalldatetime

    NULL

    uniqueidentifier

    NULL

    When bulk copying in from a character data file column containing datetime strings, all datetime string formats supported by earlier DB-Library versions of bulk copy are supported.

    When iValue is set to TRUE, a prefix of 0x is allowed for binary values specified in character mode data files. The prefix is not allowed if iValue is FALSE.

    When iValue is set to FALSE, zero length indicates are stored as 0x00 in character mode data files and as 0x0000 in BCPUNICODE files.

  • BCPABORT
    Stops a bulk-copy operation that is already in progress. Call bcp_control with an eOption of BCPABORT from another thread to stop a running bulk copy operation. The iValue parameter is ignored.
  • BCPBATCH
    Is the number of rows per batch. The default is 0, which indicates either all rows in a table, when data is being extracted, or all rows in the user data file, when data is being copied to an SQL Server. A value less than 1 resets BCPBATCH to the default.
  • BCPFILECP
    iValue contains the number of the code page for the data file. You can specify the number of the code page, such as 1252 or 850, or one of these values:

    BCPFILE_ACP: data in the file is in the Microsoft Windows® code page of the client.

    BCPFILE_OEMCP: data in the file is in the OEM code page of the client (default).

    BCPFILE_RAW: data in the file is in the code page of the SQL Server.

  • BCPFIRST
    Is the first row of data to file or table to copy. The default is 1; a value less than 1 resets this option to its default.
  • BCPFMTXML
    Specifies that the format file generated should be in XML format. It is off by default.

    XML format files provide greater flexibility but with some added constraints. For example, you can not specify the prefix and terminator for a field simultaneously, which was possible in older format files.

    [!NOTA] XML format files are only supported when SQL Server 2005 tools are installed along with SQL Native Client.

  • BCPHINTS
    iValue contains an SQLTCHAR character string pointer. The string addressed specifies either SQL Server bulk-copy processing hints or a Transact-SQL statement that returns a result set. If a Transact-SQL statement is specified that returns more than one result set, all result sets after the first are ignored. For more information about bulk-copy processing hints, see bcp Utility.
  • BCPKEEPIDENTITY
    When iValue is TRUE, specifies that bulk copy functions insert data values supplied for SQL Server columns defined with an identity constraint. The input file must supply values for the identity columns. If this is not set, new identity values are generated for the inserted rows. Any data present in the file for the identity columns is ignored.
  • BCPKEEPNULLS
    Specifies whether empty data values in the file will be converted to NULL values in the SQL Server table. When iValue is TRUE, empty values will be converted to NULL in the SQL Server table. The default is for empty values to be converted to a default value for the column in the SQL Server table if a default exists.
  • BCPLAST
    Is the last row to copy. The default is to copy all rows; a value less than 1 resets this option to its default.
  • BCPMAXERRS
    Is the number of errors allowed before the bulk copy operation fails. The default is 10; a value less than 1 resets this option to its default. Bulk copy imposes a maximum of 65,535 errors. An attempt to set this option to a value larger than 65,535 results in the option being set to 65,535.
  • BCPODBC
    When TRUE, specifies that datetime and smalldatetime values saved in character format will use the ODBC timestamp escape sequence prefix and suffix. The BCPODBC option only applies to BCP_OUT.

    When FALSE, a datetime value representing January 1, 1997 is converted to the character string: 1997-01-01 00:00:00.000. When TRUE, the same datetime value is represented as: {ts '1997-01-01 00:00:00.000'}.

  • BCPUNICODEFILE
    When TRUE, specifies the input file is a Unicode file.
  • FIRE_TRIGGERS
    Specifies that INSERT and INSTEAD OF triggers defined on the destination table are fired once for each bulk copy batch. The inserted table passed to each trigger contains all of the rows inserted by the batch. Bulk copy operations that would otherwise be logged minimally are fully logged when FIRE_TRIGGERS is specified. No result sets generated by the triggers are returned to the client performing the bulk copy operation. Specify FIRE_TRIGGERS only when all of the INSERT and INSTEAD OF triggers on the destination table support multiple row inserts. The iValue parameter is ignored.
  • iValue
    Is the value for the specified eOption. iValue is an integer value cast to a void pointer to allow for future expansion to 64 bit values.

Returns

SUCCEED or FAIL.

Osservazioni

This function sets various control parameters for bulk-copy operations, including the number of errors allowed before canceling a bulk copy, the numbers of the first and last rows to copy from a data file, and the batch size.

This function is also used to specify the SELECT statement when bulk copying out from SQL Server the result set of a SELECT. Set eOption to BCPHINTS and set iValue to have a pointer to an SQLTCHAR string containing the SELECT statement.

These control parameters are only meaningful when copying between a user file and an SQL Server table. Control parameter settings have no effect on rows copied to SQL Server with bcp_sendrow.

Example

// Variables like henv not specified.
SQLHDBC      hdbc;
DBINT      nRowsProcessed;
 
// Application initiation, get an ODBC environment handle, allocate the
// hdbc, and so on.
... 

// Enable bulk copy prior to connecting on allocated hdbc.
SQLSetConnectAttr(hdbc, SQL_COPT_SS_BCP, (SQLPOINTER) SQL_BCP_ON,
   SQL_IS_INTEGER);

// Connect to the data source, return on error.
if (!SQL_SUCCEEDED(SQLConnect(hdbc, _T("myDSN"), SQL_NTS,
   _T("myUser"), SQL_NTS, _T("myPwd"), SQL_NTS)))
   {
   // Raise error and return.
   return;
   }

// Initialize bulk copy. 
if (bcp_init(hdbc, _T("address"), _T("address.add"), _T("addr.err"),
   DB_IN) == FAIL)
   {
   // Raise error and return.
   return;
   }

// Set the number of rows per batch. 
if (bcp_control(hdbc, BCPBATCH, (void*) 1000) == FAIL)
   {
   // Raise error and return.
   return;
   }

// Set file column count. 
if (bcp_columns(hdbc, 1) == FAIL)
   {
   // Raise error and return.
   return;
   }

// Set the file format. 
if (bcp_colfmt(hdbc, 1, 0, 0, SQL_VARLEN_DATA, '\n', 1, 1)
   == FAIL)
   {
   // Raise error and return.
   return;
   }

// Execute the bulk copy. 
if (bcp_exec(hdbc, &nRowsProcessed) == FAIL)
   {
   // Raise error and return.
   return;
   }

printf("%ld rows processed by bulk copy.", nRowsProcessed);

Vedere anche

Riferimento

Bulk Copy Functions

Guida in linea e informazioni

Assistenza su SQL Server 2005