How to know,some one in SQL Server database

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

Other Development Tools

How to know,some one in SQL Server database

Author

Topic

Mamatha
Posting Yak Master

102 Posts

Posted - 2005-01-23 : 06:38:14

Hi

I have a ASP application that uses SQL Server as a database.If some one has access the same database then how can i know some one uses that?If some one uses the same database which i want to access then my application displays a message like "someone uses the database please wait some time ".How can i know this please give me the solution,thanks in advance.

Mamatha

nr
SQLTeam MVY

12543 Posts

Posted - 2005-01-23 : 10:15:34

select * from master..sysprocesses where dbid = db_id('mydbname')

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Mamatha
Posting Yak Master

102 Posts

Posted - 2005-01-24 : 02:46:56

I have one restore database application in ASP,at the same i also contains application to retrieve table names from database.If i want to retrieve table names from database like employee then at the same time i want to restore employee database backup file,then page displayed error like

Microsoft SQL-DMO (ODBC SQLState: 42000) (0x800A0C1D)
[Microsoft][ODBC SQL Server Driver][SQL Server]Exclusive access could not be obtained because the database is in use. [Microsoft][ODBC SQL Server Driver][SQL Server]RESTORE DATABASE is terminating abnormally.

So how can i know wether some one has access the same database.I want to display a message like "some one in database".
If you know the solution please let me know.

Mamatha

sreemace
Starting Member

9 Posts

Posted - 2005-01-24 : 03:12:59

sp_who or sp_who2(undocumented) will help u out from this....

sp_who2 will give only active access to DB giva a try...

Thanks & Regards,
Sreejith G

nr
SQLTeam MVY

12543 Posts

Posted - 2005-01-24 : 06:17:28

You need
select * from master..sysprocesses where dbid = db_id('mydbname')
It doesn't matter if the connections are active or not - they will still prevent a restore.
You can then kill the spids (making sure they are not system processes) or whatever.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Mamatha
Posting Yak Master

102 Posts

Posted - 2005-01-24 : 23:38:20

I checked in restore file like this:

set rs_exist=conn.execute("select * from master..sysprocesses where dbid = db_id('mydbname')")
if rs_exist(0) <> "" then
'do the operations for restore
else
response.write "some one exist"
end if

but it gives an Exception occured error at line if condition .
What is the reason?Is there any problem in syntax.
Please give me the reply.

Mamatha

nr
SQLTeam MVY

12543 Posts

Posted - 2005-01-25 : 05:58:29

try if rs_exist.eof
or something like that

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Mamatha
Posting Yak Master

102 Posts

Posted - 2005-01-29 : 02:34:03

I want to know how many members access that same database,my requirement is,i have to files in ASP,those two files uses the same database.
If one file access the database,then at the same time with another file i want to restore the same database.So for restoreing,it may overwrite the existing database,at the time of restoring it will check wethere database is accessed any one,If any one access before restoring it will gives an error like...Some one in database.
How can we check before restoring?
Is there any method for this?
Please give me the solution?
I am restoring the file using SQLDMO.dll
I tried with that objects also,but i was unable to find any method related to this.

Mamatha

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources