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/bulk copy' 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