Hi,
When I run the query generated in Bo with Toad, I am getting some records. But in BO, the message is No data to fetch.
Please guide me.
ravik (BOB member since 2004-06-02)
Hi,
When I run the query generated in Bo with Toad, I am getting some records. But in BO, the message is No data to fetch.
Please guide me.
ravik (BOB member since 2004-06-02)
There could be a couple of things:
check to make sure that your connections are allright - clicking on parameters in designer to test the connection.
check to see if you may have applied a filter to the report that you are running and are unaware of doing it. Sometimes, you may have modified your report by using the slice and dice and then re-ran the report.
Not sure if this is the answer, but give these a shot.
indy (BOB member since 2004-04-08)
Do you have date condition in your query?
if Yes check your .sbo file
JaiGupta (BOB member since 2002-09-12)
Hi,
I retested the connections in Universe. It is ok.
Ya there is a filter, which is also there in Toad (copy & paste the SQL in BO to Toad)
No
ravik (BOB member since 2004-06-02)
How long does the query take to run in TOAD? What are the universe constraints? Is is possible you’ve exceeded the time limit placed by either the Universe Designer, or superseded in Supervisor?
Anita Craig (BOB member since 2002-06-17)
Ok…
Now it becomes neccessary to check the query which Bo is sending to Database.
There are 3 ways of determining it.
1- Using BOLOGAPP.EXE
2 - Using Toad SQL Monitor
3 - or ask your DBA to give you the query being sent by BO
JaiGupta (BOB member since 2002-09-12)
Hi,
How long does the query take to run in TOAD
Around 27 minutes
What are the universe constraints?
Nof rows limit : 1100000
Excution Time : 35 Minutes
Multiple SQL Statement for each Context: Yes
Any more constraints do I have to check?
Is it possible you’ve exceeded the time limit placed by either the Universe Designer, or superseded in Supervisor?
I am the universe designer and I have the access to edit the connection from universe to set the above parameters like max time /rows etc. Where else Supervisor can set the limits?
Using BOLOGAPP.EXE
I could not BOLOGAPP.exe in my desktop
Using Toad SQL Monitor
do you mean to explain plan or Auto trace etc?
ask your DBA to give you the query being sent by BO
I cut & paste the query generated from BO to Toad, it is working there.
ravik (BOB member since 2004-06-02)
BOLOGAPP.EXE will be located in C:\Program Files\Business Objects\BusinessObjects 5.0
Toad SQL Monitor is a utility which comes with TOAD if you have DBA license.
It does not refer to Explain plan or trace.
This shows you the query sent by BO to Oracle.
When the Query is running on Database ask your DBA to use Kill/Trace Seession. When he clicks on your session he will be able to see the query running on the database.
Would it be possible for you to post the query here?
JaiGupta (BOB member since 2002-09-12)
In supervisor, the universe properties set by the designer can be overwritten. By selecting the user and then the relevant universe, right click and select properties. Check the controls as you would in designer and see if any of the items are highlighted in red. This would signify that the Supervisor has changed the controls from those set by the designer.
If the number of rows had changed, you would get partial results, but if the execution time is changed and it times out before the query has returned, you would get No results to fetch. NB this would only be the case for a synchronous connection.
jmmorton (BOB member since 2004-07-05)
Ravi - is it possible that BusinessObjects is actually running a JOIN query or a SYNCHRONIZED query – which in reality resolves to multiple queries? If you’ve got a universe time-out of 35 minutes, and it’s taking TOAD 27 minutes to run – and might be running just one of what may be multiple queries – you could very well be hitting the time-out limit in BusinessObjects.
Since you’re the Designer, try increasing your time-out time to something very big – or get Supervisor to increase it just for your login.
Anita Craig (BOB member since 2002-06-17)
Ya I am using Synchronous mode. That could be one of reasons for that message to appear
What are the pro’s and con’s of synchronous connection
I increased the time to 250 minutes. now it is not displaying the ‘no data fetch’ message
ravik (BOB member since 2004-06-02)
You are supposed to use Synchronus connection in Webi as per BO recommendation.
JaiGupta (BOB member since 2002-09-12)
I read this to say that increasing the universe time-out worked.
So, I’d infer that you were hitting the universe time-out!
Anita Craig (BOB member since 2002-06-17)
One problem with synchronous connections is that the message you get back when your query overruns is not very meaning full.
Basically, with a synchronous connection, you fire the query off to say, Oracle, and then sit and wait for it to come back. If the query execution time reaches the time limit set and no data has been returned, it will appear as if it is still running, which indeed on the Oracle side it is. Say the query takes 45 minutes, Oracle says “here you go, here are the results you were after”. BusinessObjects would reject the results as they had exceeded the time limit and just state “No data to fetch”. Not a very meaningful message at all.
With an asynchronous connection, Oracle would keep BusinessObjects posted of the progress as it goes along. If the time limit is reached, BusinessObjects would terminate the query and display what results it had and show the Partial Results warning.
Thats a simplified explanation and it doesn’t take into account the performance differences in the two connection types, but it should help in understanding the error you were getting.
jmmorton (BOB member since 2004-07-05)
Hi,
Thanks for info.
However even with Synchronous connection (universe), sometimes i noticed partial results in BOBJ fullclient reports
ravik (BOB member since 2004-06-02)
The other controls can also prevent the full return of the data such as the result set size limit. If this is exceeded, it will only return the maximum permitted. Check the Data Manager in Reporter and see what the run produced. Cross check this to what controls have been set on the universe. As was mentioned earlier in this thread, the Supervisor can also over-ride these controls on a user by user basis so it maybe worth checking there.
jmmorton (BOB member since 2004-07-05)