How to execute sql command just like "Drop DATABASE " and "Restore DATABASE "?

  • Thread starter Thread starter Risen
  • Start date Start date
R

Risen

Hi,all,

I want to execute SQL command " DROP DATABASE mydb" and "Restore DATABASE
....." in vb.net 2003. But it always shows error. If any body can tell me how
to execute sql command as above? Thanks a lot.


Best regard.
Risen

----
see my code below:

Dim conn As New SqlClient.SqlConnection
conn.ConnectionString = ConnStr
Try
Dim selectCMD As SqlCommand = New SqlCommand
selectCMD.Connection = conn
selectCMD.CommandType = CommandType.StoredProcedure
'change CommandType to CommandType.Text show error too.

selectCMD.CommandText = "DROP DATABASE RMS" 'how to execute
current sql command?
conn.Open()
selectCMD.ExecuteNonQuery()
MsgBox("successful")
Catch ex As Exception
MessageBox.Show("error!")
Finally
conn.Close()
End Try
 
You need to run it from a stored procedure. Create one that has the drop
procedure command and call it from your CommandText.
 
Risen,

Beside that crazy thing that you tell that it is a stored procedure I (if I
not oversee something) do it the same as you,

The simplest you can change to see what is going wrong is.
Catch ex As Exception
MessageBox.Show("error!")
Finally
Click to expand...

MessageBox.Show(ex.ToString())

I hope this helps,

Cor
 
SQL doesn't like dropping databases when you have open connections to it.
Are you trying to drop a database you are currently connected to ? If so try
connecting to another database on the same SQL instance and (assuming you
have the permissions) drop the database with the sql script.

Gerry
 
Gerry,

I use exactly the same, only in my connection string I don't of course not
tell to use a database.

Cor
 
Thanks,Gerry,
Thanks,all,

The problem has been resolved. It must change database ,and the best is
"master" database. And the database being droped or restored is not used.

Risen


My code is below:

Dim conn As New SqlClient.SqlConnection
Dim tmpstr As String
tmpstr = "workstation id=" & ReadStrfromReg("Wkst_Id") + ";" & _
"packet size=" & ReadStrfromReg("pkt_size") + ";" & _
"user id=" & ReadStrfromReg("SQL_User") + ";" & _
"data source=" & ReadStrfromReg("SQLServerName") + ";" & _
"persist security info=" & ReadStrfromReg("ps_info") + ";"
& _
"initial catalog=master;" & _
"password=" & ReadStrfromReg("SQL_PSW")
conn.ConnectionString = tmpstr
Try
Dim selectCMD As SqlCommand = New SqlCommand
selectCMD.Connection = conn
selectCMD.CommandType = CommandType.Text
selectCMD.CommandText = "DROP DATABASE MyDB"
conn.Open()
selectCMD.ExecuteNonQuery()
MsgBox("Drop Successful!")
Catch ex As Exception
MessageBox.Show(ex.ToString)
Finally
conn.Close()
End Try
 
Thanks,Gerry,
Thanks,all,

The problem has been resolved. It must change database ,and the best is
"master" database. And the database being droped or restored is not used.

Risen


My code is below:

Dim conn As New SqlClient.SqlConnection
Dim tmpstr As String
tmpstr = "workstation id=" & ReadStrfromReg("Wkst_Id") + ";" & _
"packet size=" & ReadStrfromReg("pkt_size") + ";" & _
"user id=" & ReadStrfromReg("SQL_User") + ";" & _
"data source=" & ReadStrfromReg("SQLServerName") + ";" & _
"persist security info=" & ReadStrfromReg("ps_info") + ";"
& _
"initial catalog=master;" & _
"password=" & ReadStrfromReg("SQL_PSW")
conn.ConnectionString = tmpstr
Try
Dim selectCMD As SqlCommand = New SqlCommand
selectCMD.Connection = conn
selectCMD.CommandType = CommandType.Text
selectCMD.CommandText = "DROP DATABASE MyDB"
conn.Open()
selectCMD.ExecuteNonQuery()
MsgBox("Drop Successful!")
Catch ex As Exception
MessageBox.Show(ex.ToString)
Finally
conn.Close()
End Try
 
Risen,

I told you already 2 days ago that you should *not* use a database for this
kind of operations. In other words the master database.

Strange that you did not see that

Cor
 
Back
Top