Can not call sp_detach_db on a database that is offline

Question

I can run this command in SqlManager to detach the db

ALTER DATABASE mydb SET OFFLINE WITH ROLLBACK IMMEDIATE

GO

dbo.sp_detach_db @dbname = N'mydb',@keepfulltextindexfile = N'false'

When I use the same connection running the same commadn via ado.net fails with error:

The database 'mydb' can not be opened because it is offline 

(Error is translated from german.)

The Ado.Net code is

            SqlCommand cmdOffline = new SqlCommand(@"ALTER DATABASE mydb SET OFFLINE WITH ROLLBACK IMMEDIATE");
            cmdOffline.Connection = prepareMasterDBConnection;
            cmdOffline.ExecuteNonQuery();


            SqlCommand cmdDetach = new SqlCommand(@"dbo.sp_detach_db @dbname = N'mydb',@keepfulltextindexfile = N'false'");
            cmdDetach.Connection = prepareMasterDBConnection;
            cmdDetach.ExecuteNonQuery();

The connection is set to master - DB and open. The first commadn exceutes sucessfully.

What is the difference here when calling the code from ado and from sql-manager?

Answer 1

If your goal is to avoid conflicting connections while dropping it, rather than setting it offline before detaching, I would use the command, ALTER DATABASE mydb SET SINGLE_USER WITH ROLLBACK IMMEDIATE instead of setting it offline (and reverse it with ALTER DATABASE mydb SET MULTI_USER).

Answer 2

Detach needs to do some stuff before it detaches. Like s_detach says (my bold)

@skipchecks = 'skipchecks'

Specifies whether to skip or run UPDATE STATISTIC. skipchecks is a nvarchar(10) value, with a default value of NULL. To skip UPDATE STATISTICS, specify true. To explicitly run UPDATE STATISTICS, specify false.

By default, UPDATE STATISTICS is performed to update information about the data in the tables and indexes in the SQL Server 2005 Database Engine. Performing UPDATE STATISTICS is useful for databases that are to be moved to read-only media.

When it's offline, you can't do that...