Business Objects

BusObj + MySQL – SQLBindParameter State 07001

I recently encountered a strange problem in Business Objects whilst trying to create a derived table against a MySQL database and whilst the query was a complex one it executed without fault in the MySQL Query Browser so I knew that wasn’t the probem.  The error message I encountered was “SQLBindParameter not used for all parameters”…

I tried the same query as a Freehand SQL query in DeskI and received the same error, after banging my head against the problem for about 15 minutes I decided to take my favourite solution – I went for lunch!  On return the solution seemed blindingly obvious, at the very top of my query I’d included a comment but in the comment I’d included a question mark – as soon as I removed the “?” the query ran without a hitch.  I’m not quite sure of the reason behind this but I suspect that one of the Business Objects, ODBC or MySQL layers treats the “?” as a reserved character to indicate a parameterised query – if anyone has the answer i’d be interested to know.

Business Objects DeskI: DA0005 “No column or data to fetch”

This is just a quick tip to help people fix an odd error I encountered whilst runing a Freehand SQL query against a MySQL 5 database, I’d used prompted queries against MySQL previously so that wasn’t an issue  – I’m speculating a little but in my case the data provider used subqueries and both the inner and outer query were prompted which is a little unusual. 

The error I received was DA0005 “No column or data to fetch”…

The solution came from the brilliant BOB Forums (original post here), the solution was to edit the odbc.sbo file which on my default installation found in:  

C:\Program Files\Business Objects\BusinessObjects Enterprise 11.5\win32_x86\dataAccess\connectionServer\odbc

You’ll need to add the following in the relevant section, in my case I added it under Generic, Generic ODBC and MySQL 5. 

<Parameter Name="ForceSQLExecute">Always</Parameter>

After closing and restarting DeskI everything was fine, please bear in mind that if you’re running a client/server installation and your Inforview users need to run the report you’ll need to change the settings on the server too.

Quick Fix: DeskI Query Panel Appears Invisible / Off Screen

For the last couple of weeks I’ve had an extremely unusual problem in Business Objects Desktop Intelligence (DeskI), whenever I went to edit a Data Provider nothing would happen.  Initially it looked like DeskI had hung but hitting escape returned me back into the report, I figured that because the Query Panel is modal it must be launching but for some reason I couldn’t see it. 

After a little bit of searching I managed to find the solution on the excellent BOB Forum (original post here) and I just had to reproduce it since I found it quite tough to find but hats off to Marek Chladny who came up with the solution – if you’re ever in the UK let me know and I’ll buy you a beer! 

1. Go to Edit Data Provider. 
2. Press Alt + Space
3. Press M
4. Use the arrows to move the Query Panel back onto the screen. 

Marek mentions that this only works if the Query Panel isn’t maximised, also I assume that since this uses standard Windows keyboard shortcuts it might work for any other application where you have a similar issue.  As for how it came about, I have no idea – it may be something to do with occasionally using a single-screen PC to remote-desktop into my dual-screen desktop.

Set Instance Limits in Business Objects XI R2

In a previous post I mentioned previously How to Manage Instances in Business Objects XI R2, well – that’s only half of the story – rather than having to go and delete old instances yourself it’s far easier to have Business Objects tidy up after itself.  This is one of the few scheduler-related features that can be found in the CMC under “Settings” and “Limits” allows you to specify…

• The maximum number of instances to be maintained (anything above will be deleted). 
• How many instances should be kept for each user/group (I choose Everyone). 
• How many days these instance should be kept for each user/group (again, I choose Everyone). 

Please note that the excess instances do not get deleted immediately after the change, in my case it seemed to happen by the next day so do be patient!  This will really help to cut down the size of your FRS and may disaster recovery much easier. 

View Failed Scheduled Jobs in Business Objects XI R2

When I moved from old-school Business Objects (6.x) to XI Release 2 I was happy that the scheduler would now be available for all users directly in WebI, unfortunately along with the improvement came the down-side – no Broadcast Agent Console (BCA Console) to monitor jobs. The lack of an admin tool for the scheduler becomes a pain every now & again since without very careful control you’ll quickly lose track of which documents have been scheduled by whom and whether they’re still running, if they’re failing, etc. Additionally if you experience some sort of system failure (e.g. FRS goes down) you may need to see what failed in order to make sure it is re-run manually.

Well, despite the fact that the CMC doesn’t include a great deal of schedule management functionality, there is a tool bundled with the standard BO installation which can at least help with the issue. The tool is called the Instance Manager and it’s part of the SDK ‘use case’ examples provided and can be accessed via the Administration Launchpad URL as so…

http://bo_server_name:8080/businessobjects/enterprise115/adminlaunch/launchpad.html

… effectively this redirects you to here…

http://bo_server_name:8080/businessobjects/enterprise115/adminlaunch/instancesByStatus/main.jsp

After selecting the Instance Manager on the left-hand menu (see image above), log in with the relevant credentials…

Select the status you want to look for and a username for which you want to search (username optional for everything but ‘All Statuses’) and hit Go!

You then see a list of instances (note, this is Instances not Jobs)…

Please be careful since this screen will allow you to delete instances (up to 100 at a time) if required and whilst there is a message box asking “are you sure you want to delete…” I don’t know of any way to roll back the changes.

How to Kill Sessions in Business Objects XI (R2 + R3)

There are some areas of functionality that the Business Objects admin tools really lack, one is the ability to view who is currently using the system and then kill a particular session.  These tools can be useful if you’re diagnosing problems and whilst BO don’t provide them the SDK does allow for them to be created and thankfully Johnny Ye has created the Business Objects XI Session Removal Tool, it’s fairly rudimentary in design terms but it does the job perfectly and I’m sure if you know Java and HTML you could customise it easily.

Out of the box it should work with XI R3 but I’ve not tried it since I’m currently running XI R2, in order to get it running on Release 2 you’ll need to modify Johnny’s instructions as follows (instructions taken from this thread on BOB)…

1. In the new_utilities.jsp file change the
line: result += "Delta " +conn.getDelta()+"<br>";
to: result += "Delta " + "na"/*(conn.getDelta()*/+"<br>";

2. Copy the library files from:
~\Tomcat\webapps\businessobjects\enterprise115\adminlaunch\WEB-INF\lib
to:
~\Tomcat\webapps\KillSession\WEB-INF\lib

NOTE: ~ = “C:\Program Files\Business Objects”

3. Restart Apache/Tomcat (I’m using IIS so this didn’t really impact my users).