Error 7130

Error 7130

Severity Level 16

Message Text

%ls WITH NO LOG is not valid at this time. Use sp_dboption to set the 'select into/bulkcopy' option on for database '%.*ls'.

Explanation

This error occurs upon executing either the UPDATETEXT or WRITETEXT statements, or either the dbupdatetext or dbwritetext routines in a database that does not have the sp_dboption system stored procedure's select into/bulkcopy option enabled. Because these are nonlogged operations, the option must be enabled.

Often, UPDATETEXT, WRITETEXT, dbupdatetext, and dbwritetext are run against a temporary table. The select into/bulkcopy option must be enabled in tempdb, or these will not run successfully.

Action

Enable the sp_dboption system stored procedure's select into/bulkcopy option for all databases affected by the query that contains a nonlogged operation. To do this, the system administrator and database owners should follow these steps:

1. Enable the option:

   USE master
   GO
   sp_dboption database_name, 'select into/bulkcopy',true
   GO
   USE database_name
   GO
   CHECKPOINT
   GO
   

2. Verify that the change is active:

   sp_helpdb database_name
   GO
   

3. Execute the nonlogged operation.
   
   
4. When the nonlogged operation is complete, backup the database.

   Caution  After executing a nonlogged operation, do not back up the transaction log. Although the procedure may appear to succeed, you may have been backing up only empty text pages. Whenever you make nonlogged changes to your database, you must use the BACKUP DATABASE statement, because changes made by the nonlogged operation cannot be recovered from transaction logs.

   5. Return the database to its original condition by disabling the select into/bulkcopy option:

   USE master
   GO
   sp_dboption database_name, 'select into/bulkcopy',false
   GO
   USE database_name
   GO
   CHECKPOINT
   GO
   

6. Verify that the change is active:

   sp_helpdb database_name
   GO
   

See Also

CHECKPOINT

dbwritetext

dbupdatetext

Setting Database Options

sp_dboption

sp_helpdb

UPDATETEXT

WRITETEXT