?
Solved

Database locking

Posted on 2006-03-30
4
Medium Priority
?
206 Views
Last Modified: 2010-04-23
Hi Guys

I have a big string which contains a whole lot of batch queries and I was wondering how to lock the database or even better specific tables then perform the batch queries and then unlock either the tables or the database. I am running on sql server 2006. Basic code is below not exact but just to show how I am currently running the queries.

Dim DatabaseConn As SqlConnection = New SqlConnection("Server=sdf;UID=sdg;PWD=dfb;DATABASE=dfb")
Dim SqlQuery As SqlCommand
DatabaseConn.Open()
SqlQuery = New SqlCommand(QueryString, DatabaseConn)
SqlQuery.ExecuteNonQuery()
DatabaseConn.Close()
0
Comment
Question by:peter58
  • 2
4 Comments
 
LVL 8

Expert Comment

by:doobdave
ID: 16333337
Hi,

I assume you meant SQL Server 2005, and not 2006!

Not too sure if there's any new features for this, but I believe what you're looking for is called a "locking hint" (refer to SQL Server Books Online for more information).

So your INSERT/UPDATE/DELTE, would look something like this:

INSERT INTO tblname WITH (TABLOCKX)
 (col1, col2, colN)
VALUES
 (val1, val2, valN)

Hope this helps.

Best Regards,

David
0
 
LVL 4

Author Comment

by:peter58
ID: 16334352
Is there no way to lock it before and after all the batch queries as I want to update or insert them all before anyone can access them.
0
 
LVL 6

Accepted Solution

by:
JRossi1 earned 1000 total points
ID: 16336594
I think utilizing a transaction would give you what you are looking for.  

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnadonet/html/transact.asp 
0
 
LVL 6

Expert Comment

by:JRossi1
ID: 16336652
Follow-up.  

I believe the default isolation level in a transaction is 'serializable' which means: "A range lock is placed on the DataSet, preventing other users from updating or inserting rows into the dataset until the transaction is complete."  

0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Creating an analog clock UserControl seems fairly straight forward.  It is, after all, essentially just a circle with several lines in it!  Two common approaches for rendering an analog clock typically involve either manually calculating points with…
The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…

750 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question