I have an interesting situation where I use temporary tables in my stored procedure. The names begin with # which work nornally outside of BO. But when I try to use the stored procedure in BO, I get an invalid table name error. However, if I place a Select statement (to show the parameter passed to the stored procedure) before any real code, no error is returned. I get my results fine. But I get the parameter by itself too.
So, I am missing something that I didnāt catch in the documentation? BTW, I have BO version 6.5.1 and SQL 2000.
This is a known issue in BO 651 (Using temporary tables in stored procedures) and I believe there is a fix for this issue. You can contact tech support to get the fix and I think we need to modify one or 2 parameters in the .prm file too.
Dean, I am wondering did you install the HotFix?
Or perhaps someone else has installed the HotFix?
I found on another posting on BOB that the HotFix is #626. Is this correct? Did it cause any other problems after installation?
I am using BO6.5 and SQL Server 2000 and have the same problem. Unfortunately I have been working with BO tech support for over a week now but have not yet received the hotfix and am wondering what to expect when i do.
With my ever multi-directional priorities, I didnāt get to load the Hotfix. This is still a problem and I have been using the old version of BO (5.1.6) just to get these one-off requests done.
Well, I was able to download and install SP2 and Cuumulative Hotfix 8. That didnāt solve the problem.
Someone mention parameters in the .prm file. I compared the version from 5.1.6 to 6.5 SP2 and the only thing that looks differeent is the Table Delimiter value. 5.1.6 has " and 6.5 SP2 has ". Has anyone changed this?
I received from Business Objects support Hotfix 487 which i installed. This took a while as i didnāt realize i needed to stop the services. I then did the following:
Added the following to the odbc.sbo file after the last sql
Microsoft
rdbms_mssqlserverodbc.txt
sqlsrv
sqlsrv
sqlsrv
True
dbd_mssql
31
datadirect
2)Registry modification - You have to create a new String Value Ā« MS SQL Server 2000 Query Ā» located in HKEY_LOCAL_MACHINE\Software\Business Objects\Suite 6.0\default\Shared\ConnectionServer\NetworkLayer\ODBC\SQL List
So now I can run stored procedures with temp tables but they are way slower. If a query took 1 minute in version 5.1.6 it takes 6-10 mins in version 6.5.1
Let me know if you find your sps are much slower now too.
Currently, we are not running WebIntelligence so there is not running on the server. So I am still trying to fugure out how to fix this issues. BTW, I am trying to run this report in the Report Builder, so it is running on my PC.
I have a full client install on my machine and I followed the instructions as per the server instructions - running the hotfix, making the registry change and changing the odbc.sbo file. Iām not sure if it was all necessary but it worked. Iām sorry I donāt have any other suggestions. Have you completed all the steps?
I added the registry setting as indicated and placed the following after āMS SQL Server 7.xā in OLEDB.SBOā¦
Microsoft
rdbms_mssqlserverodbc.txt
sqlsrv
sqlsrv
sqlsrv
True
dbd_mssql
31
datadirect
The hotfix should have been in either SP2 or the Monthly Cummulative HF8. Those are straight forward, just execute and go, with all BO programs closed.
No fault at all. I added it and still get the same error. The temporary table is still an invalid object. I donāt knowā¦ Maybe I should un-install and re-install the client-side.
No fault at all. I added it and still get the same error. The temporary table is still an invalid object. I donāt knowā¦ Maybe I should un-install and re-install the client-side.
After much searching of Tech Support, I have identified Bug No. 1086058 which has yet to be resolved by a Hot Fix. It relates to SQL Server OLEDB (from versions BO 5.1.8 to 6.5.1).
Resolution 16075 recommends either removing the INSERT after the SELECT or using ODBC connectivity for the Stored Procedure. Unfortunately, both of these solutions cannot be applied here so I have escalated the case and requested a fix, as BO themselves have agreed it is definitely a bug.
In the meantime, I am re-writing the Stored Procedure into Freehand SQL and trying to use Derived Tables at Universe level to implement the solution delivered by the Stored Procedure.
Hope this helps anyone trying to resolve OLEDB SQL Server Stored Procedure problems.
i have tried to create a procedure with a PL/SQL table and ref cursor , which is called by a package.
When i tried to add this procedure in Supervisor it says āThere is no available stored procedure to addā , we tried it by creating synonym of the procedure also.Syntax of synonym is
Iām not sure about hotfix #626 but 487 was included in 6.5.2 but you still needed to make the appropriate changes in in odbc.sbo and registry settings and it stored procedures with temp tables still run slower than in 5.1.
Has BO released any fixes to solve this issue? Weāve tried the fix and had no luck. I noticed a LOT of traffic on this issue and would assume a fix for 6.5x
I get my results fine. 
But I get the parameter by itself too.
(BOB member since 2005-05-17)