locking an SQL Server DB with .NET

  • Thread starter Thread starter DraguVaso
  • Start date Start date
D

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
 
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.


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
 
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
 
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)?
 
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.
--
 
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...
 
Back
Top