August 18, 2003 at 1:23 am
I have created an multiuser-multicompany vb with sql server application, and database is created by the name of company code. I am allowing the user to deleting database and I just want take the precaution, if someone delete the shared database so an eror message should display.
It works fine when a single user is working on it, but it gives problem at same time multiple user working on same database and one of them want to drop the databse. It gives following error
"-2147217900 (80040e14) can not drop the database, it is currently in use....."
How do I prevent this?
Is there any way to know someone is using the database? or I can lock the database ? So I can give the error message accordingly?
I know I can use sp_who /sp_who2 system procedures. Or can query the master..sysprocesses table to see the current connections.
To remove them, I can either kill all the spids in a loop or I can do the popular method :
ALTER DATABASE db SET SINGLE_USER WITH ROLLBACK IMMEDIATE
But I dont want to kill all the spides, I want to gove error message to that person who is trying to delete database, if someone is sharing the database.
So please suggest me the best way or any sample VB program to do all this activities.
August 18, 2003 at 4:49 am
Firstly I am sure you are connecting to a database other than the database you need to drop while executing the drop command from the front end.
If this is true then what I feel is that you can create a store procedure with an output paramter and execute the store procedure from the front end. The store procedure will contain the drop database command. Immediately after the drop database command pass the value of @@error value to the output parameter. It will be 3702 in case the database cannot be dropped else it will be 0.
Trap this value in the front end and display an error message.
Tell me if this is of any help to you.
Relationships are like Banks.You Invest the Principal and then get the Interest.
He who knows others is learned but the wise one is one who knows himself.
August 18, 2003 at 9:17 am
If it not agianst your ethics, could you send me the code?
Sonashish
August 18, 2003 at 11:09 pm
Visual Basic Part
=================
on error goto error
Dim oCommand As New Command
oCommand.ActiveConnection = <connectionobject>
oCommand.CommandTimeout = 100
oCommand.CommandType = adCmdStoredProc
oCommand.CommandText = <storedprocedurename>
oCommand.Parameters.Append oCommand.CreateParameter("@status", adInteger, adParamOutput)
oCommand.Execute
Exit Sub
error:
i = oCommand("@status").Value
MsgBox i
Stored Procedure
=================
create procedure updropdatabase @status int output
as
drop database <databasename>
set @status=@@error
Relationships are like Banks.You Invest the Principal and then get the Interest.
He who knows others is learned but the wise one is one who knows himself.
August 19, 2003 at 3:24 am
Could please tell me how do I the pass the databasename in store procedure?
August 19, 2003 at 3:41 am
Since you cannot drop a database when some one is connected to the same, you need to keep the store procedure in some other database and connect to that database.
you can specify this in the connection string.
(Anyone have some better suggestions)
Pay Respect to People on your way up. For you will meeet the same People on your way down.
He who knows others is learned but the wise one is one who knows himself.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply