Error 4806

Severity Level 16

Message Text

Warning, reverting to slow bulk copy on table '%.*s' because BULKCOPY option not set in database '%.*s'.

Explanation

This error occurs when the select into/bulkcopy option is not set to true when using:

• WRITETEXT
• dbwritetext
• SELECT INTO on a permanent table
• bcp into a table that has no indexes or triggers

Action

Use the sp_dboption system procedure to set the select into/bulkcopy option as follows:

sp_dboption database_name 'select into/bulkcopy', true
go

use database_name
go

checkpoint
go

For information about using sp_dboption and CHECKPOINT, see the Microsoft SQL Server Transact-SQL Reference.

Additional Information

While the select into/bulkcopy option is turned on, you cannot dump the transaction log to a device because these operations are not logged and changes therefore cannot be recovered from transaction logs. In this situation, using DUMP TRANSACTION to dump the transaction log to a device produces a 4207 error message, which instructs you to use DUMP DATABASE instead. By default, the select into/bulkcopy option is turned off in newly created databases.

For copying data in, bcp is fastest if your database table has no indexes or triggers. Fast bcp (bcp on a table with no indexes or triggers) does not log data inserts in the transaction log. When you copy into a table that has indexes or triggers, a slower version of bcp is used automatically. The slow version, which logs data inserts in the transaction log, can cause the transaction log to become very large.

After backing up your database with DUMP DATABASE, you can truncate the transaction log with DUMP TRANSACTION WITH TRUNCATE_ONLY, and dump the database again (for recoverability).

For additional information about using bcp, see the Microsoft SQL Server Transact-SQL Reference.