Error 7130

Severity Level 16

Message Text

%s with no log is not valid at this time. Use sp_dboption to set the 'select into/bulkcopy' option on for database %s.

Explanation

This error occurs upon executing the writetext command or dbwritetext routine in a database that does not have the sp_dboption system stored procedure's select into/bulkcopy option enabled. Because these are non-logged operations, the option must be enabled.

Often, writetext 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 non-logged 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


    

  1. 
Verify that the change is active:
    sp_helpdb database_name
go
    

    2. 

  2. 
Execute the non-logged operation.
    3. 

  3. 
When the non-logged operation is complete, dump the database.
    
Caution  After executing a non-logged operation, do not dump the transaction log. Although the procedure may appear to succeed, you may have been dumping only empty text pages. Whenever you make non-logged changes to your database, you must use the dump database statement, because changes made by the non-logged operation cannot be recovered from transaction logs.

    4. 

  4. 
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
    

    5. 

  5. 
Verify that the change is active:
    sp_helpdb database_name
go
    

    6. 




For more information about writetext and dbwritetext(), see the Microsoft SQL Server Transact-SQL Reference and Microsoft SQL Server Programming DB-Library for C. For more information about database dumps and recoverability, see Part 5, "Data Management."