Exclusive Mode

Question

Exclusive Mode

umesh_j99

Old Hand

Points: 355
More actions
April 7, 2008 at 3:44 am

#205235

Hi!
I am opening connection to sql server 2005, using VB 6.
I am setting connection object's mode property to adModeShareExclusive. Still program is updating the records while database is open. Please help.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Ian Yates SSCoach Points: 19738 More actions · Answer 1

I assume you are using ADO. Could you use SQL Profiler to see what the ADO is doing with the SQL Server SET options when it opens the connection?

If you want absolute exclusive one at a time access to the DB you should set the database property for SINGLE_USER mode rather than MULTI_USER mode.

umesh_j99 Old Hand Points: 355 More actions · Answer 2

You are right. I am using ADO.

Regarding your suggestions,

(1) What is SQL Profiler and how to use it?

(2) How to set the database property for SINGLE_USER mode? Does connectionstring have any parameter for it?

Thanks a lot.

Ian Yates SSCoach Points: 19738 More actions · Answer 3

SINGLE_USER is a database setting. You can set it by running SQL code as in

alter database [myDatabase] set SINGLE_USER

This means that only one connection can be accessing that database at a time. Change SINGLE_USER to MULTI_USER to reverse the changes.

SQL Profiler is a utility that comes with SQL 2005 Workgroup (?), Standard, Enterprise and Developer - not Express. If you are running SQL Express then you can obtain Profiler by obtaining the 120 evaluation of SQL Enterprise and running Profiler from that to look at what's happening with your SQL Express.

Essentially SQL Profiler lets you see all of the commands being sent to your server. You can also see the connection settings in effect.

Is it a problem that another program is updating records? Which records are these - any in the database or just a select few? If it's "any in the database" then single_user ought to do the trick. If it's "just a few" then you should look up "locking hints" in SQL Server Books Online (the manual for SQL Server). Also look up "transaction isolation levels". These should map over to ADO connection string settings.

umesh_j99 Old Hand Points: 355 More actions · Answer 4

It is "selected records in a particular table".

Thanks