I am trying to enable the select into/bulkcopy database option on a SQL Server 2005 database using the command:
EXEC sp_dboption 'mydbname', 'select into/bulkcopy', 'true'
After executing the above, running
EXEC sp_dboption 'mydbname', 'select into/bulkcopy' tells me that the option is still set to OFF.
I've confirmed that my Windows login is a user in the database and that it belongs to the db_owner role. Having read the MSDN Documentation for sp_dboption, this appears to be the only prerequisite for using the procedure to change options on a database.
Are there any other steps or settings I am missing that could prevent me from being able to enable this option?
That procedure is deprecated. You can use
ALTER DATABASE [mydbname] SET RECOVERY BULK_LOGGED WITH NO_WAIT
This seems to happen if your DB is currently in
SIMPLE recovery model.
Looking at the
sp_dboption procedure definition the relevant bit of code is
if @alt_optvalue = 'ON' begin if databaseproperty(@dbname, 'IsTrunclog') = 1 select @alt_optvalue = 'RECMODEL_70BACKCOMP' else select @alt_optvalue = 'BULK_LOGGED' end
The effect of running
ALTER DATABASE [mydbname] SET RECOVERY RECMODEL_70BACKCOMP WITH NO_WAIT seems to be to set the recovery model to
SIMPLE so basically it has no effect in this instance