Uncommitted Reads against SQL Server

system · March 2, 2006, 7:09pm

For reporting, a universe has been developed against a SQL Server 2000 database that will be updated at a 10-15 minute interval.

We have been executing a few of our reports against this database, but we are running into table locks from the 10-15 minute update process. Is there any way we can force the queries to do uncommitted, or dirty, reads against the database? We would prefer to do this at the universe level.

Thx,
Adam

akolbe (BOB member since 2004-11-19)

system · March 2, 2006, 7:26pm

Did you try a Search using keywords dirty reads before posting? This post talks about enabling it in the database.

The Designer Database Specific FAQ (a Sticky at the top of the Semantic Layer forum) also mentions how to set a parameter for DB2 – maybe there’s such a way for SQL Server – you’d have to consult the database-specific user manual to see what your options are. (For me, it’s in the under C:\Program Files\Business Objects\Online Guides\EN\SybaseEN.pdf for version 5.1.9).

Anita Craig (BOB member since 2002-06-17)

system · March 2, 2006, 8:07pm

Thank you for the response. I tried searching before posting, but all of the results dealt with dirty reads for a universe built on a DB2 database.

I did some research with SQL Server’s Online books, and there are two ways to get dirty reads. One way is to set the Transaction Isolation Level and the other is to use With (Nolock). The isolation level would need to be set before the query. The nolock would need to be added after each table in the from and joins inside the query. I am not sure where I could add either in my universe.

akolbe (BOB member since 2004-11-19)

system · March 2, 2006, 10:54pm

If you can do so, you wouldn’t be adding that in your universe. It would probably be in your *.prm file.

Look at the database-specific BusinessObjects documentation for your database – that’s what I was talking about. See if they tell you how you can identify that you want dirty reads.

Anita Craig (BOB member since 2002-06-17)

system · January 26, 2007, 4:16am

I am interested in this issue as well. Where can I find the specific documentation about SQL Server and Business Objects? I tried looking at BOs website but didn’t find anything.

Thanks!

Juan

jgorricho (BOB member since 2005-10-21)

system · January 26, 2007, 7:12am

There are many way’s to influence the SQL generated and many are not documented. Ask your DBA or a Database expert about what you should do. Then try to apply it in BO.

If you are using BO 6.5 you have to change the PRM file and add a line if it does not exist END_SQL = “The SQL specific elements you want to add”. This will apply to all universes and has to be “deployed” to every client

In XIR2 these properties are for each universe and are in the Parameter --> Parameter tab.

Historically (V3 and early V4) on oracle you would also change these files to send an alter session prior to the SQL Sent by BO. I have not done it recently but it is probably still possible.

ClaireB (BOB member since 2002-08-09)

system · May 18, 2007, 4:00pm

All…

To force uncommited reads against the SQL Server DB, you will have to update the odbc.sbo file in your BusinessObjects installation directory (will probably be…C:\Program Files\BusinessObjects Enterprise 11.5
\win32_x86\dataAccess\connectionServer\odbc)

Find the parameters listed under your specific DB/Server (MS SQL Server 7.x or MS SQL Server 2000 etc) and add the following parameter to the block…

READ UNCOMMITTED

That should do the trick.

Ofcourse, this change will have to be made on the server where BO runs. And if you would want the same setting to be applied when you run DeskI reports, you will have to make the change to the .sbo file on your local machine too.

Hope this helps.

R.

nonemvijar (BOB member since 2005-12-13)

system · May 21, 2007, 1:34pm

Are you sure we can add parameters to this file such as
READ UNCOMMITTED ?
I tried doing it but it did not change the isolation level for my queries.

Zulfiqar_Taj (BOB member since 2002-09-16)

JohnBClark · November 17, 2009, 6:10pm

I will add some additional information to this post instead of starting a new one.

First, a few keywords: SQL Server, SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED, Dirty Reads, Crystal Reports.

Out scenario: We primarily use Crystal Reports written against universes over SQL Server databases. In order to set up Dirty Reads at the server level for this scenario we must implement the following registry change depending on your version of Business Objects.

For BOEXIR2 sp x.x navigate to the following key in the registry:

HKEY_LOCAL_MACHINE\SOFTWARE\Business Objects\Suite 11.5\Crystal Reports\Database

Create a new key called ODBC. Within this new ODBC key add a String Value - Name = IsolationLevel, Value = 1.

For our BOE XI3.1 sp2 testing the registry change is a little different. This is running on Windows Server 2008 which is most likely the main issue for the change. This has been tested with a SQL Trace.

Navigate to the following key in the registry:

HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Business Objects\Suite 12.0\Crystal Reports\Database

Create a new key called ODBC. Within this new ODBC key add a REG_DWORD Value - Name = IsolationLevel, Value = 1.

Using the Registry Editor incorrectly can cause serious problems that may require you to reinstall the Microsoft Windows operating system. Use the Registry Editor at your own risk.
It is strongly recommended that you make a backup copy of the registry files (System.dat and User.dat on Win9x computers) before you edit the registry.

system · July 20, 2010, 6:27am

I’m only replying to this post for the purpose of confirming that I have just added the change to my ODBC.SBO file and it has worked perfectly. No changes needed to any Registry files but then I am only using Webi reports in XIR3 so I can’t speak for Crystal users.

Thanks

plessiusa (BOB member since 2004-03-22)

system · May 18, 2018, 1:48pm

Hi,

I am trying to do the same at the moment using BO 4.2 against a SQLserver db.

The team we use that manages BO for us created an ODBC connection and added SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED to the connection, however when I get DBA’s to run a SQL trace we do not see the SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED being run before the SQL, does the ODBC.sbo file have to be changed ? Unfortunately I cant access the server to make the change and quickly test, I have raise a ticket wait a few days …

What we have done so far is,

Created ODBC source
Created SQL Server connection in universe using ODBC data source.
Select Connection Pool Mode as Disconnection after each transaction
Enter SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED in the connectInit Parameters
Assign connection to universe.

I got the DBA’s to run a trace but I don’t see the SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED in there.

thanks

Rich (BOB member since 2002-10-04)

system · May 23, 2018, 7:25am

Hey,

ODBC.SBO does indeed need changing, there`s a tag in there called which needs changing or adding. Something like;

READ_UNCOMMITTED

ABILtd (BOB member since 2006-02-08)