CODEWITHSUNDEEP
sql-servervb.net
Furqan Sehgal
Furqan Sehgal

Reputation: 4997

Is my detaching code right?

I am using following code to detach my DB from SQL Server express. Please advise if there is something wrong with it. I am getting an error on it. 

"Could not locate entry in sysdatabases for database 'EXEC master'. No entry found with that name. Make sure that the name is entered correctly."

The code I am using is

 Dim conn2 As New SqlConnection("Server=MyHomeServer\SQLExpress;Database=master;Trusted_Connection=False;User ID=sa;Password=abc123;")
    Dim cmd2 As New SqlCommand("", conn2)
    cmd2.CommandType = CommandType.StoredProcedure
    cmd2.CommandText = "EXEC master.dbo.sp_detach_db @dbname = N'MyHomeDBTestPWD'"
    conn2.Open()
    cmd2.Connection = conn2
    cmd2.ExecuteNonQuery()

Upvotes: 2

Views: 532

Answers (3)

Massimiliano Peluso
Massimiliano Peluso

Reputation: 26737

You better use Microsoft.SqlServer.Smo namespace which contains all you need for working with the database. There is a specific method to Attach/Detach database

Server mServer = new Server(".");
mServer.KillAllProcesses("attach1");
mServer.DetachDatabase("attach1", true);

http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.server.detachdatabase.aspx

Upvotes: 0

dknaack
dknaack

Reputation: 60556

  1. Your ConnectionString points already to the Master Database so you don't need to specify it.

    EXEC [dbo].[sp_detach_db] @dbname = N'MyHomeDBTestPWD'

  2. If you want to specify the Database you should do this

    EXEC [master].[dbo].[sp_detach_db] @dbname = N'MyHomeDBTestPWD'

  3. And you have the wrong CommandType. If you want to execute the code as it is change to CommandType.Text.

CommandType.Text solution

Dim cmd2 As New SqlCommand("", conn2) 
cmd2.CommandType = CommandType.Text
cmd2.CommandText = "EXEC [dbo].[sp_detach_db] @dbname = 'MyHomeDBTestPWD'"
cmd2.ExecuteNonQuery()

CommandType.StoredProcedure solution

Dim cmd2 As New SqlCommand("", conn2) 
cmd2.CommandType = CommandType.StoredProcedure
cmd2.CommandText = "[dbo].[sp_detach_db]"
cmd2.Parameters.AddWithValue("@dbname", "MyHomeDBTestPWD")
cmd2.ExecuteNonQuery()

Upvotes: 1

Neil Knight
Neil Knight

Reputation: 48597

You are assiging this the wrong way. You are allocating a proper SQL statement in the CommandText when you only need to add the Stored Procedure name. You will then need to add a Parameter to your SqlCommand.

Dim cmd2 As New SqlCommand("", conn2) 
cmd2.CommandType = CommandType.StoredProcedure
cmd2.CommandText = "sp_detach_db"
cmd2.Parameters.AddWithValue("@dbname", "MyHomeDBTestPWD")
cmd2.ExecuteNonQuery()

If you are alternatively wanting to execute your SQL statement, then you just need to change your CommandType to CommandType.Text

Upvotes: 1

Related Questions