Exclusive DB access

Hi,
I'm searching a way or a method to gain exclusive access for a short time
period (minutes) to a SQL 2000 database from a VB application using ADO and
SQLDMO, in order to do some data historization and computation, and
preventing that others users may connect to the database during this time.
After searching in the SQLDMO object model, I couldn't find anything adequate
for this purpose. Is the only way to lock/unlock all the tables ?
Has anyone an idea ?
Roberto
Answered in .connectivity
Please do not multi-post.
Regards
Mike
"GunSmoke_62" wrote:

> Hi,
> I'm searching a way or a method to gain exclusive access for a short time
> period (minutes) to a SQL 2000 database from a VB application using ADO and
> SQLDMO, in order to do some data historization and computation, and
> preventing that others users may connect to the database during this time.
> After searching in the SQLDMO object model, I couldn't find anything adequate
> for this purpose. Is the only way to lock/unlock all the tables ?
> Has anyone an idea ?
> Roberto

Exclusive DB access

Hi,
I'm searching a way or a method to gain exclusive access for a short time
period (minutes) to a SQL 2000 database from a VB application using ADO and
SQLDMO, in order to do some data historization and computation, and
preventing that others users may connect to the database during this time.
After searching in the SQLDMO object model, I couldn't find anything adequat
e
for this purpose. Is the only way to lock/unlock all the tables ?
Has anyone an idea ?
RobertoAnswered in .connectivity
Please do not multi-post.
Regards
Mike
"GunSmoke_62" wrote:

> Hi,
> I'm searching a way or a method to gain exclusive access for a short time
> period (minutes) to a SQL 2000 database from a VB application using ADO an
d
> SQLDMO, in order to do some data historization and computation, and
> preventing that others users may connect to the database during this time.
> After searching in the SQLDMO object model, I couldn't find anything adequ
ate
> for this purpose. Is the only way to lock/unlock all the tables ?
> Has anyone an idea ?
> Robertosql

Exclusive DB access

Hi,
I'm searching a way or a method to gain exclusive access for a short time
period (minutes) to a SQL 2000 database from a VB application using ADO and
SQLDMO, in order to do some data historization and computation, and
preventing that others users may connect to the database during this time.
After searching in the SQLDMO object model, I couldn't find anything adequate
for this purpose. Is the only way to lock/unlock all the tables ?
Has anyone an idea ?
RobertoAnswered in .connectivity
Please do not multi-post.
Regards
Mike
"GunSmoke_62" wrote:
> Hi,
> I'm searching a way or a method to gain exclusive access for a short time
> period (minutes) to a SQL 2000 database from a VB application using ADO and
> SQLDMO, in order to do some data historization and computation, and
> preventing that others users may connect to the database during this time.
> After searching in the SQLDMO object model, I couldn't find anything adequate
> for this purpose. Is the only way to lock/unlock all the tables ?
> Has anyone an idea ?
> Roberto

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

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.
RobertoHi
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 tha
t
> 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 tha
t
> 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]