locking an SQL Server DB with .NET

[DraguVaso]

Hi,

My VB.NET-application has to do some actions on a SQL Server Database, but
during these actions the DataBase may not be used by any other application.
the total time of the lock could be up to 15 minutes. How can I perform
something like a lock, who denies other users/applications to access the
Database, and after a while grant themagain permission.

Thanks,

Pieter

[David Lloyd]

Pieter:

Transact SQL has a number of locking hints that can be used in queries to
provide the appropriate level of database locking. I would recommend
browsing SQL Server Books On Line for more information on locking hints, as
well as other ways to lock a database.

--
David Lloyd
MCSD .NET
http://LemingtonConsulting.com

This response is supplied "as is" without any representations or warranties.
"DraguVaso" <pi**********@hotmail.com> wrote in message
news:#Q**************@TK2MSFTNGP15.phx.gbl...
Hi,

My VB.NET-application has to do some actions on a SQL Server Database, but
during these actions the DataBase may not be used by any other application.
the total time of the lock could be up to 15 minutes. How can I perform
something like a lock, who denies other users/applications to access the
Database, and after a while grant themagain permission.

Thanks,

Pieter

[Alejandro Mesa]

See "ALTER DATABASE" in BOL. You can use this statement to set the database
status in SINGLE_USER, do your stuff and set it back to MULTI_USER.

Example:

use northwind
go

alter database northwind
set SINGLE_USER with ROLLBACK IMMEDIATE
go

-- do your stuff here
....
go

alter database northwind
set MULTI_USER
go
AMB

"DraguVaso" wrote:

Hi,

My VB.NET-application has to do some actions on a SQL Server Database, but
during these actions the DataBase may not be used by any other application.
the total time of the lock could be up to 15 minutes. How can I perform
something like a lock, who denies other users/applications to access the
Database, and after a while grant themagain permission.

Thanks,

Pieter

[DraguVaso]

Thanks both.
Isn't it a bit dangerous to do things like this? Otherwise: what if i workd
with a different kind of login/username, and put the rights of the other
users to read-only (db_datareader)?

"Alejandro Mesa" <Al***********@discussions.microsoft.com> wrote in message
news:94**********************************@microsof t.com...

See "ALTER DATABASE" in BOL. You can use this statement to set the database status in SINGLE_USER, do your stuff and set it back to MULTI_USER.

Example:

use northwind
go

alter database northwind
set SINGLE_USER with ROLLBACK IMMEDIATE
go

-- do your stuff here
...
go

alter database northwind
set MULTI_USER
go
AMB

"DraguVaso" wrote:

Hi,

My VB.NET-application has to do some actions on a SQL Server Database, but during these actions the DataBase may not be used by any other application. the total time of the lock could be up to 15 minutes. How can I perform
something like a lock, who denies other users/applications to access the
Database, and after a while grant themagain permission.

Thanks,

Pieter

[Nick Malik [Microsoft]]

what is it that you are doing that you want to write to the db but you are
willing to allow others to read from it?

And why on earth does it take 15 minutes?

Are you pulling data out for a data warehouse?

--
--- Nick Malik [Microsoft]
MCSD, CFPS, Certified Scrummaster
http://blogs.msdn.com/nickmalik

Disclaimer: Opinions expressed in this forum are my own, and not
representative of my employer.
I do not answer questions on behalf of my employer. I'm just a
programmer helping programmers.
--
"DraguVaso" <pi**********@hotmail.com> wrote in message
news:Oo**************@TK2MSFTNGP14.phx.gbl...

Thanks both.
Isn't it a bit dangerous to do things like this? Otherwise: what if i
workd
with a different kind of login/username, and put the rights of the other
users to read-only (db_datareader)?

"Alejandro Mesa" <Al***********@discussions.microsoft.com> wrote in
message
news:94**********************************@microsof t.com...

See "ALTER DATABASE" in BOL. You can use this statement to set the

database

status in SINGLE_USER, do your stuff and set it back to MULTI_USER.

Example:

use northwind
go

alter database northwind
set SINGLE_USER with ROLLBACK IMMEDIATE
go

-- do your stuff here
...
go

alter database northwind
set MULTI_USER
go
AMB

"DraguVaso" wrote:

> Hi,
>
> My VB.NET-application has to do some actions on a SQL Server Database, but > during these actions the DataBase may not be used by any other application. > the total time of the lock could be up to 15 minutes. How can I perform
> something like a lock, who denies other users/applications to access
> the
> Database, and after a while grant themagain permission.
>
> Thanks,
>
> Pieter
>
>
>

[DraguVaso]

Hehe good question :-)
Well: I need to write some sort of replication between different SQL
Servers, and the replication has some very specific property's:
- The SQL Servers doesn't have a direct connection: it's between different
palmtree-plantations in africa, and the only communication they have is once
a week to send as-small-ass-possible files with some kind of radio :-)
- so during the replication/synchronization people shouldn't be allowed to
make any other changes in the databases
- the 15 minutes: they are just slow over there to follow the whole
procedure...
"Nick Malik [Microsoft]" <ni*******@hotmail.nospam.com> wrote in message
news:Cb********************@comcast.com...

what is it that you are doing that you want to write to the db but you are
willing to allow others to read from it?

And why on earth does it take 15 minutes?

Are you pulling data out for a data warehouse?

--
--- Nick Malik [Microsoft]
MCSD, CFPS, Certified Scrummaster
http://blogs.msdn.com/nickmalik

Disclaimer: Opinions expressed in this forum are my own, and not
representative of my employer.
I do not answer questions on behalf of my employer. I'm just a
programmer helping programmers.
--
"DraguVaso" <pi**********@hotmail.com> wrote in message
news:Oo**************@TK2MSFTNGP14.phx.gbl...

Thanks both.
Isn't it a bit dangerous to do things like this? Otherwise: what if i
workd
with a different kind of login/username, and put the rights of the other
users to read-only (db_datareader)?

"Alejandro Mesa" <Al***********@discussions.microsoft.com> wrote in
message
news:94**********************************@microsof t.com...

See "ALTER DATABASE" in BOL. You can use this statement to set the

database

status in SINGLE_USER, do your stuff and set it back to MULTI_USER.

Example:

use northwind
go

alter database northwind
set SINGLE_USER with ROLLBACK IMMEDIATE
go

-- do your stuff here
...
go

alter database northwind
set MULTI_USER
go
AMB

"DraguVaso" wrote:

> Hi,
>
> My VB.NET-application has to do some actions on a SQL Server Database,

but

> during these actions the DataBase may not be used by any other

application.

> the total time of the lock could be up to 15 minutes. How can I

perform > something like a lock, who denies other users/applications to access
> the
> Database, and after a while grant themagain permission.
>
> Thanks,
>
> Pieter
>
>
>