I need to build Universe that will look at a Transactional database. As such, the reporting cannot lock the tables so I need to be able to put (NOLOCK) in the FROM statement.
Now, I know that the SQL could be edited when the objects have been added to the query, but users will be using the Universe to build reports and they are bound to forget or they just not going to want to do so - and quite frankly I wouldn’t blame them either.
I have included the ConnectInit=“SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED” in the Custom Parameters of the Connection
So my question is:
How can I ensure that NOLOCK will be included in the FROM statement?
Putting With (NoLock) in the END SQL parameter will not work in this case. The With (NoLock) option has to be listed after each table name. Unless you are using free hand SQL, I haven’t found anywhere to enter this. The END SQL parameter is usefull for this if you are reporting against DB2.
Because of this you will need to set up your connection to send SET TRANSACTION ISOLOLATION LEVEL READ UNCOMMITTED to the database before the query. I’m not sure if setting this in ConnectInit works or not.
We modified our oledb.sbo file to add SET TRANSACTION ISOLOLATION LEVEL READ UNCOMMITTED. We ran a trace in SQL Server and were able to verify that this was being passed when running a WebI report.
This file can be found in Install Directory\win32_x86\dataAccess\connectionServer\oledb. Add the following line to all SQL Server section: READ UNCOMMITTED.
So far, we have been unable to get this to work for Crystal Reports. Business Objects had previously supplied us with a registry hack to get this to work with ODBC connections. We think this may be what we need to get it working with Crystal Reports. We have opened a case with BO Support but have not received any reply yet.
If this is the case, I don’t understand why they are recommending to use OLEDB connections instead of ODBC. It seems to me to be a step backwards for Crystal Reports.
My case with Support is now closed. The result of the case is still that it is not possible to set the Transaction Isolation level with Crystal Reports using OLEDB connections because the Crystal dlls are not set up to recognize any registry settings for OLEDB. I didn’t get any explanation as to why this was not done when it was done for ODBC connections.
I contacted our account manager on the issue also and I could get from them was that they couldn’t find anything in their internal forums so they would go with support.
I’m still not satisfied with the answers. This seems like a step backwards. I will probably do some testing on my own to verify this when I have time.
OLEDB connections are supposed to be faster connections than ODBC. This is the main benefit. ODBC has been around longer and does have a more wide spread usage than OLEDB. Some applications will not work with OLEDB, only ODBC. This may not be much of an issue when considering only Business Objects connection.
From an administrative stand point, if you use OLEDB connections for your universes and are using Crystal Reports, there is no need to have a local ODBC connection set up to design reports. This is beneficial because you don’t have to maintain the ODBC DSNs on the computers or servers (less administration).
There are some potential security benefits to using OLEDB connections also. All reporting using the connection information set up in Business Objects regardless of what work is happening. Creating Crystal Reports, creating Web Intelligence reports or viewing any reports all use this same connection with the same login information. This helps simply administration also because then you won’t necessarily have to either provide the report developers with a login and password in addition to the one used by Business Objects Enterprise. (This one will depend on how you are setup to use Business Objects. If your report writers use their own login credentials when using ODBC connections, this could be eliminated with OLEDB connections.)
These are the benefits that I see. Im sure others may have more.
Enhancement Request ADAPT00990551: “Read uncommitted to be available with OLE DB connection” has been submitted. The only notice there will be that this has been addressed is if it is listed in the Read Me for future service packs.
Any update to this? I am having this same issue in Crystal except I am using an ODBC connection to SQL Server. The registry change did not correct it. We are using BOE3.1 SP2 FP5. Support says I have to change the isolation level on the database for the table and there is no way that is going to happen.
You need to modify your odbc.sbo file. Do the same modifications that I have listed earlier in this thread for the oledb.sbo file. The changes are the same.
It should be added to the section for each version of SQL Server that you may be using. We add it to MS SQL Server 2000, MS SQL Server 2005 and MS SQL Server 2008. This is just to make sure we are covered for everything.
I’m not sure on this one. In our process we always reboot our server after making the change but that has more to do with other configuration changes that we make at the same time. I would say to restart BOE just to be safe.
To update one of my earlier posts on getting this to work with OLEDB connections, it has still not been corrected as of SP5. I just tested this.
I was wondering if the functionality changes between BOE XI 3.1 and BOE 4.0 and if it is still supported in 4.0.
I tried to add the configuration you specified :
READ UNCOMMITTED
In both files, but that didn’t seem to work. The queries generated by the universe in webi, didn’t have the NoLock specified.
Is there a better way to test it?
Not that I know of. We haven’t started working with 4.0 any further than getting it installed on a test server. It wouldn’t surprise me if there were some changes in the new version. I’m afraid I can’t be of much more help until I get time to work with 4.0.
I’m looking to make the change to the ODBC.SBO file on my 3.1 environment. There is some mention on this thread about a registry change. If I make the change to the SBO, is the registry change also needed? …and if so, please let me know what the registry change is.
(BOB member since 2005-12-20)
(BOB member since 2005-11-24)
but have not received any reply yet.
(BOB member since 2007-07-20)
(BOB member since 2010-11-23)