We are going to be using the OpenText (Hummingbird) BI Server tool to report against SQL Server databases. With this reporting tool, you specify the ODBC data source to use or connection string for user-defined connections. We want the connection for reporting to be with a READ-UNCOMMITTED transaction isolation level. However I understand you cannot set this within the connection string and the default ISOLATION LEVEL for SQL Server 2008 R2 is READ COMMITTED. Is there any way we can get around this with the ODBC connection or can we set ISOLATION LEVELS by user and handle at the database end
You are correct that the default transaction isolation level is READ COMMITTED. This can be changed to READ UNCOMMITTED at the session level by executing SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED.
I would be wary of using READ UNCOMMITTED on a production system. Not only might this result in reading uncommitted data, rows may skipped or duplicated. Instead, consider turning on the READ_COMMITTED_SNAPSHOT database option so that row versioning instead of locking is used to provide read consistency in the READ_COMMITTED isolation level while improving concurrency. READ_COMMITTED_SNAPSHOT increases tempdb usage and database space requirements but, in my experience, the benefits of typically benefits typically outweigh the costs. I suggest you test in your environment for viability.
Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/
- Proposed as answer by Papy NormandModerator Wednesday, February 22, 2012 11:03 PM
thanks for the prompt reply. So setting READ_COMMITTED_SNAPSHOT should have the effect of not locking records when connecting with the SQL Native client driver without implicitly setting the ISO level after connection (something that the reporting app does not do).
Will pass this onto my colleages to look into.
It seems that the Dan's reply is the answer to your problem. If it is the case, please, could you mark the Dan's reply as the answer ? The simplest and best way to thank him . Moreover, , an unanswered thread is too often considered as less attractive than an answered one even if there is no possible solution or workaround to propose.
Thanks for him beforehand
Have a nice day
Mark Post as helpful if it provides any help.Otherwise,leave it as it is.