2005 can do it as:
1. Query hint [with (nolock)]
2. Transaction level [set transaction isolation level <level>]
3. Database level [this allows for "snapshot" isolation levels where
data is
still preserved and row versioning is done over row locking]
If you really, really want to change to a read-uncommitted state, verify
that doing so will not create an adverse impact and set your application
to connect with the appropriate settings. Tread carefully as I believe
it is still possible to even change uncommitted data.
Reviewing your statements below, the snapshot row version might work
well for you in your reporting server. Data can be changed and users
will see data as it existed at the beginning of their query (avoid the
dirty reads). A lot like Oracle and its snapshot files.
Hth,
Jon
-----Original Message-----
From: Richard Wadsworth
[mailto:mssqldba-ezmlmshield-x41729652.[Email address protected]
Sent: Friday, June 29, 2007 5:16 AM
To: LazyDBA Discussion
Subject: Transaction Isolation Level
Hi,
Is there a way of setting the transaction isolation level for a DB
(possibly even at server level). I'd like our reporting DB to be set to
read uncommitted, but I can't see how to do this without changing this
for all procs.
Any ideas?
Thanks
Rick
Richard Wadsworth
Database Administrator
CARCRAFT
E: richard.[Email address protected]
T: 07811 341277
Nixon Street, Rochdale OL11 3JW
www.carcraft.co.uk
This email and any files transmitted with it are strictly confidential.
It is for the intended recipient only. If you have received the email in
error please notify the author by replying to this email. If you are not
the intended recipient you must not disclose, distribute, copy, print or
rely on this email in anyway. Any views which are expressed in this
e-mail are solely the personal opinion of the author and do not
necessarily represent the views of CC Automotive Group Limited.
Registered in England and Wales No: 819414. Registered office: Nixon
Street, Rochdale, Lancashire OL11 3JW. This footnote also confirms this
email message has been swept for the presence of known computer viruses
before transmission.
---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
MS Sql Server LazyDBA home page