We are using BODI 6.5.1 on a SQL server 2000 platform in combination with BO 6.5.1.
From time to time we are having ETL jobs being stopped due to a lock on the database. The lock is always generated by the BO reporting server. The query that causes the lock seems to be a ad hoc report generated by an end user (all ZABO users).
We have set the BO settings to uncommitted read (via the *.sbo files located on the server).
Any one an idea how to make sure ZABO queries do not lock database tables and thus don’t block the BODI job.
I’m not really understanding this one. Aren’t there two seperate issues here (apologies if I’ve misunderstood)
The BODI jobs are locking.
Ad hoc queries using the reporting tools are also causing locks.
As far as I know there is no way that you could set the ReadUncommitted option for DI (not BO reporting tools) in version 6.5. The option to to do this for DI jobs was only introduced in v11.5. We have set that option and it has prevented locks from occurring, although it’s not regarded as a very clean fix.
So, your DI jobs will not be set to read Uncommitted no matter where else you have set this option. I’m more than happy to be proven wrong on this so feel free to post if I’m missing something.
Do you mean that the ad hoc queries are accessing the tables, locking them down then DI tries to access the same locked table?
Read Uncomitted is set for a distinct session. The DI job will create numerous sessions for each job none of which (in V6.5) will be set to Read Uncommitted. None of these sessions will be in any way related to queries from the reporting tool, where there may be an option to Read Uncommitted data.
SQL server locking (particularly with older verions like 2000 and 7.0) is so simplistic, that this will likely be a problem with anyone trying to report on the database while the ETL is occurring. We had the same problem.
Our solution was to keep two copies of our reporting database. A “runtime” version and a “build” version. You always populate BODI into the “build” database. Then, through some sort of magic, back up and restore build into Runtime. You’d have to kick out your reporting users while the restore occurs, though.
Or use SQL Server replication from Build to Runtime. Or some other (expensive) replication tool.
The problem I’m having is indeed that Business Objects queries obtain locks on tables. Each time ZABO sessions are terminated in a dirty way, the sessions remain open, BODI can’t update tables and the BODI job waits till the lock is released.
We have set up a work-around for the problem:
each night we restart the webintelligence service, to make sure the connection server is restarted and all remaining ZABO session are terminated. All processes on our database are then also terminated.
We have the same problem. The sessions are open and the queries themselves aren’t active, but they have some sort of strange lock on the database.
Our solution was to write a stored procedure which looks through the database for any connection that is known to be coming from a universe. (We use only a small number of specific lognis for the universes.) It then does a “kill” against each of those sessions.
I try to execute the command below in a script in a DI job, which is supposed to kill all the users that are connected to a specific database:
sql(‘RM_Billing_DW’, ‘exec KillUsers_Tom’);
but when I run it, I get the following error message … any idea what the issue could be??
552 2400 DBS-070401 11/10/2010 8:40:56 AM |Session job_Test_Kill
552 2400 DBS-070401 11/10/2010 8:40:56 AM ODBC data source error message for operation : <[Microsoft][ODBC SQL Server Driver][SQL
552 2400 DBS-070401 11/10/2010 8:40:56 AM Server]KILL command cannot be used inside user transactions.>.
552 2400 RUN-050304 11/10/2010 8:40:56 AM |Session job_Test_Kill
552 2400 RUN-050304 11/10/2010 8:40:56 AM Function call <sql ( RM_Billing_DW, exec KillUsers_Tom ) > failed, due to error <70401>: <ODBC data source
552 2400 RUN-050304 11/10/2010 8:40:56 AM error message for operation : <[Microsoft][ODBC SQL Server Driver][SQL Server]KILL command cannot be used inside
552 2400 RUN-050304 11/10/2010 8:40:56 AM user transactions.>.>.
552 2400 RUN-053008 11/10/2010 8:40:56 AM |Session job_Test_Kill
552 2400 RUN-053008 11/10/2010 8:40:56 AM INFO: The above error occurs in the context <|Session job_Test_Kill|sql(…) Function Body|>.
Followed your first suggestion to add the if … then commit … but still getting the same error message below in DI:
and by the way, that does work in sql server managament studio.
2724 6096 DBS-070401 11/10/2010 10:31:08 AM |Session job_Test_Kill
2724 6096 DBS-070401 11/10/2010 10:31:08 AM ODBC data source error message for operation : <[Microsoft][ODBC SQL Server Driver][SQL
2724 6096 DBS-070401 11/10/2010 10:31:08 AM Server]KILL command cannot be used inside user transactions.>.
2724 6096 RUN-050304 11/10/2010 10:31:08 AM |Session job_Test_Kill
2724 6096 RUN-050304 11/10/2010 10:31:08 AM Function call <sql ( RM_Billing_DW, exec KillUsers_Tom ) > failed, due to error <70401>: <ODBC data source
2724 6096 RUN-050304 11/10/2010 10:31:08 AM error message for operation : <[Microsoft][ODBC SQL Server Driver][SQL Server]KILL command cannot be used inside
2724 6096 RUN-050304 11/10/2010 10:31:08 AM user transactions.>.>.
2724 6096 RUN-053008 11/10/2010 10:31:08 AM |Session job_Test_Kill
2724 6096 RUN-053008 11/10/2010 10:31:08 AM INFO: The above error occurs in the context <|Session job_Test_Kill|sql(…) Function Body|>.
Going to test it now with using xp_cmdshell in a stored procedure to execute another stored procedure (which does the kill) … and that is working correct !!
(BOB member since 2005-05-03)
(BOB member since 2004-01-30)
(BOB member since 2004-01-30)
(BOB member since 2008-10-16)