Exclusive access to MS SQL Database

Hi,
I have to gain exclusive access to a MS SQL Server 2000 Database from a VB
application through ADO for a very short period of time in order to do
special processing (data historization and computation) and preventing that
others users can connect at the same time. Which is the best method ?
Best regards.
Roberto
Hi
Kill all the other connections where SPID > 50, except for yours. Put the DB
in Single Use mode, do your processing and then remove Single Use Mode.
Regards
Mike
"GunSmoke_62" wrote:

> Hi,
> I have to gain exclusive access to a MS SQL Server 2000 Database from a VB
> application through ADO for a very short period of time in order to do
> special processing (data historization and computation) and preventing that
> others users can connect at the same time. Which is the best method ?
> Best regards.
> Roberto
|||use ALTER DATABASE,
Example:
use master
go
alter database northwind
set SINGLE_USER with ROLLBACK IMMEDIATE
go
--do your stuff
go
alter database northwind
set MULTI_USER with NO_WAIT
go
AMB
"GunSmoke_62" wrote:

> Hi,
> I have to gain exclusive access to a MS SQL Server 2000 Database from a VB
> application through ADO for a very short period of time in order to do
> special processing (data historization and computation) and preventing that
> others users can connect at the same time. Which is the best method ?
> Best regards.
> Roberto
|||Your application will need to login with system admin rights in order to do
the following:
Kill all connections automatically (no need to kill each indicidually),
rollback any unresolved transactions, and place the database in single user
mode.
ALTER DATABASE API SET SINGLE_USER WITH ROLLBACK IMMEDIATE
Leave the database in read-only mode. This could help optimize performace of
reporting and data transfer processing.
ALTER DATABASE API SET READ_ONLY
Restrict login to the database to only those users belonging to the database
owner (DBO) role.
ALTER DATABASE API SET RESTRICTED_USER
After doing your thing, the following will restore the database back to
normal.
ALTER DATABASE API SET READ_WRITE
ALTER DATABASE API SET MULTI_USER
Also, for periodic reporting, you may want to restore the latest backup to a
dedicated reporting server or database. That way, the production database
would not need to be made unavailable.
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:5E794404-2B42-4EDB-A8AC-E65B39D234B3@.microsoft.com...
> Hi
> Kill all the other connections where SPID > 50, except for yours. Put the
DB[vbcol=seagreen]
> in Single Use mode, do your processing and then remove Single Use Mode.
> Regards
> Mike
> "GunSmoke_62" wrote:
VB[vbcol=seagreen]
that[vbcol=seagreen]
sql