SqlBCPControl%

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

Syntax

SqlBCPControl% ( sqlconn%, param%, value& )

where

sqlconn%

Is a SQL Server connection. The value of sqlconn% is returned by SqlOpen%.

param%

One of the following:

BCPMAXERRS%

Specifies the number of errors allowed before terminating. The default is 10. Providing a value of less than 1 to this field resets it to its default value. If a value larger than 65,535 is specified, this field is set to 65,535.

BCPFIRST%

Specifies the first row to copy. The default is 1. Providing a value of less than 1 to this field resets it to its default value.

BCPLAST%

Specifies the last row to copy. The default is to copy all rows. Providing a value of less than 1 to this field resets it to its default value.

BCPBATCH%

Specifies the number of rows per batch. The default is 0. Providing a value of less than 1 to this field resets it to its default value.

BCPKEEPNULLS

Specifies whether empty data values in the file will be converted to NULL values in the SQL Server table. If this option is set before calling SqlBCPExec%, empty values will be converted to NULL values in the SQL Server table. The default is for empty values to be converted to the column's default value in the SQL Server table.

value&

Is the value for the specified param%.

Returns

SUCCEED (1) or FAIL (0).

Remarks

SqlBCPControl% sets various control parameters for bulk-copy operations, including the number of errors allowed before ending an operation, the numbers of the first and last rows to copy, and the batch size.

These control parameters are meaningful only when you are copying between an operating-system file and a SQL Server table.

Example

'Initialize bcp.
Result% = 
SqlBCPInit%(Sqlconn%,"condb..address", "address.add", "addr.err", DBIN%)
IF Result% = FAIL THEN
   SQLExit
   SQLWinExit
   End
END IF
'Set the number of rows per batch.
Result% = SqlBDPControl%(Sqlconn%, BCPBATCH%, 1000)
IF Result% = FAIL THEN
   PRINT "SqlBCPControl% failed to set batching behavior."
   PRINT
   SQLExit
   SQLWinExit
   End
END IF
'Set file column count.
Result% = SQLBCPColumns%(Sqlconn%, 1)
IF Result% = FAIL THEN
   PRINT "SqlBCPColumns% failed."
   PRINT
   SQLExit
   SQLWinExit
   End
END IF

'Set file format.
Result% = SqlBCPColfmt%(Sqlconn%, 1, 0, 0, -1, Chr$(13), 1, 1)
IF Result% = FAIL THEN
   PRINT "SqlBCPColfmt% failed."
   PRINT
   SQLExit
   SQLWinExit
   End
END IF

'Now, execute the bulk-copy.
Result% = SqlBCPExec%(Sqlconn%, RowsCopied&)
IF Result% = FAIL THEN
   PRINT "Incomplete bulk-copy. Only "; RowsCopied&; " rows copied."
   PRINT
   SQLExit
   SQLWinExit
   End
END IF

See Also

SqlBCPColfmt%, SqlBCPColumns%, SqlBCPExec%, SqlBCPInit%