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

[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.SqlCo nnection
conn.Connection String = ConnStr
Try
Dim selectCMD As SqlCommand = New SqlCommand
selectCMD.Conne ction = conn
selectCMD.Comma ndType = CommandType.Sto redProcedure
'change CommandType to CommandType.Tex t show error too.

selectCMD.Comma ndText = "DROP DATABASE RMS" 'how to execute
current sql command?
conn.Open()
selectCMD.Execu teNonQuery()
MsgBox("success ful")
Catch ex As Exception
MessageBox.Show ("error!")
Finally
conn.Close()
End Try

[Shawn]

Re: How to execute sql command just like "Drop DATABASE " and "Restor e DATABASE "?

You need to run it from a stored procedure. Create one that has the drop
procedure command and call it from your CommandText.

"Risen" <risenat@21cn.c om> wrote in message
news:OmmBW4DaFH A.1044@TK2MSFTN GP10.phx.gbl...[color=blue]
> 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[/color]
how[color=blue]
> to execute sql command as above? Thanks a lot.
>
>
> Best regard.
> Risen
>
> ----
> see my code below:
>
> Dim conn As New SqlClient.SqlCo nnection
> conn.Connection String = ConnStr
> Try
> Dim selectCMD As SqlCommand = New SqlCommand
> selectCMD.Conne ction = conn
> selectCMD.Comma ndType = CommandType.Sto redProcedure
> 'change CommandType to CommandType.Tex t show error too.
>
> selectCMD.Comma ndText = "DROP DATABASE RMS" 'how to execute
> current sql command?
> conn.Open()
> selectCMD.Execu teNonQuery()
> MsgBox("success ful")
> Catch ex As Exception
> MessageBox.Show ("error!")
> Finally
> conn.Close()
> End Try
>
>[/color]

[Cor Ligthert]

Re: How to execute sql command just like &quot;Drop DATABASE &quot; and &quot;Restor e DATABASE &quot;?

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.[color=blue]
> Catch ex As Exception
> MessageBox.Show ("error!")
> Finally[/color]

MessageBox.Show (ex.ToString())

I hope this helps,

Cor

[dotnetnewbie]

Re: How to execute sql command just like &quot;Drop DATABASE &quot; and &quot;Res

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


"Cor Ligthert" wrote:
[color=blue]
> 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.[color=green]
> > Catch ex As Exception
> > MessageBox.Show ("error!")
> > Finally[/color]
>
> MessageBox.Show (ex.ToString())
>
> I hope this helps,
>
> Cor
>
>
>[/color]

[Cor Ligthert]

Re: How to execute sql command just like &quot;Drop DATABASE &quot; and &quot;Res

Gerry,

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

Cor

[Risen]

problem has been resolved

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.SqlCo nnection
Dim tmpstr As String
tmpstr = "workstatio n 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.Connection String = tmpstr
Try
Dim selectCMD As SqlCommand = New SqlCommand
selectCMD.Conne ction = conn
selectCMD.Comma ndType = CommandType.Tex t
selectCMD.Comma ndText = "DROP DATABASE MyDB"
conn.Open()
selectCMD.Execu teNonQuery()
MsgBox("Drop Successful!")
Catch ex As Exception
MessageBox.Show (ex.ToString)
Finally
conn.Close()
End Try



"Shawn" <shawn.camner@c cci.org> дÈëÏûÏ¢ÐÂÎÅ:%2 3Pd$o7DaFHA.464 @TK2MSFTNGP15.p hx.gbl...[color=blue]
> You need to run it from a stored procedure. Create one that has the drop
> procedure command and call it from your CommandText.
>
> "Risen" <risenat@21cn.c om> wrote in message
> news:OmmBW4DaFH A.1044@TK2MSFTN GP10.phx.gbl...[color=green]
>> 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[/color]
> how[color=green]
>> to execute sql command as above? Thanks a lot.
>>
>>
>> Best regard.
>> Risen
>>
>> ----
>> see my code below:
>>
>> Dim conn As New SqlClient.SqlCo nnection
>> conn.Connection String = ConnStr
>> Try
>> Dim selectCMD As SqlCommand = New SqlCommand
>> selectCMD.Conne ction = conn
>> selectCMD.Comma ndType = CommandType.Sto redProcedure
>> 'change CommandType to CommandType.Tex t show error too.
>>
>> selectCMD.Comma ndText = "DROP DATABASE RMS" 'how to execute
>> current sql command?
>> conn.Open()
>> selectCMD.Execu teNonQuery()
>> MsgBox("success ful")
>> Catch ex As Exception
>> MessageBox.Show ("error!")
>> Finally
>> conn.Close()
>> End Try
>>
>>[/color]
>
>[/color]

[Risen]

problem has been resolved

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.SqlCo nnection
Dim tmpstr As String
tmpstr = "workstatio n 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.Connection String = tmpstr
Try
Dim selectCMD As SqlCommand = New SqlCommand
selectCMD.Conne ction = conn
selectCMD.Comma ndType = CommandType.Tex t
selectCMD.Comma ndText = "DROP DATABASE MyDB"
conn.Open()
selectCMD.Execu teNonQuery()
MsgBox("Drop Successful!")
Catch ex As Exception
MessageBox.Show (ex.ToString)
Finally
conn.Close()
End Try



"Shawn" <shawn.camner@c cci.org> дÈëÏûÏ¢ÐÂÎÅ:%2 3Pd$o7DaFHA.464 @TK2MSFTNGP15.p hx.gbl...[color=blue]
> You need to run it from a stored procedure. Create one that has the drop
> procedure command and call it from your CommandText.
>
> "Risen" <risenat@21cn.c om> wrote in message
> news:OmmBW4DaFH A.1044@TK2MSFTN GP10.phx.gbl...[color=green]
>> 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[/color]
> how[color=green]
>> to execute sql command as above? Thanks a lot.
>>
>>
>> Best regard.
>> Risen
>>
>> ----
>> see my code below:
>>
>> Dim conn As New SqlClient.SqlCo nnection
>> conn.Connection String = ConnStr
>> Try
>> Dim selectCMD As SqlCommand = New SqlCommand
>> selectCMD.Conne ction = conn
>> selectCMD.Comma ndType = CommandType.Sto redProcedure
>> 'change CommandType to CommandType.Tex t show error too.
>>
>> selectCMD.Comma ndText = "DROP DATABASE RMS" 'how to execute
>> current sql command?
>> conn.Open()
>> selectCMD.Execu teNonQuery()
>> MsgBox("success ful")
>> Catch ex As Exception
>> MessageBox.Show ("error!")
>> Finally
>> conn.Close()
>> End Try
>>
>>[/color]
>
>[/color]

[Cor Ligthert]

Re: problem has been resolved

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