TEMP table error:42502 from Stored Procedure based Universe

[Former Member]

Hi ,

I was trying to insert a stored procedure in my stored procedure based universe, but I am receiving an error while inserting the SP in my universe. Seems like the error  is occurring from the TEMP table from my Stored procedure. I did little research on the error code and seems like BO Universe has support issue on stored procedure that has temp table inside.  Do you think there is a work around to get rid of this error or any alternative path to follow? I am using BO Xi 3.1.

Any response will be appreciated.

Thanks

-Rana

_________Code Snip______________

CREATE Procedure [dbo].[usr_rma_conference_comparision_report_11202012sp] 

( 

   @ip_cyear_parent [DBO].[PRODUCT_CODE],

   @ip_cyear_product [DBO].[PRODUCT_CODE],

   @ip_pyear_parent [DBO].[PRODUCT_CODE],

   @ip_pyear_product [DBO].[PRODUCT_CODE]

) 

AS 

begin

Declare @current_year_meeting_days_out int

/************************************************************************************************/ 

CREATE TABLE #RMA_CONF_COMPARE_RECORDS 

(

    [PRODUCT_CODE] [DBO].[PRODUCT_CODE] NULL,

    [PARENT_PRODUCT_CODE] [DBO].[PRODUCT_CODE] NULL,

    [PRODUCT_NAME] [dbo].[PRODUCT_LONG_DESCRIPTION] NULL,

    [PRODUCT YEAR] int NULL,

    [DAYS_OUT] int NULL,

    [TOTAL_REGISTRANTS] int NULL,

    [TOTAL_AMOUNT] numeric(38, 2) NULL

)

insert into #RMA_CONF_COMPARE_RECORDS

  (   

    [PRODUCT_CODE],

    [PARENT_PRODUCT_CODE],

    [PRODUCT_NAME],

    [PRODUCT YEAR],

    [DAYS_OUT],

    [TOTAL_REGISTRANTS],

    [TOTAL_AMOUNT]

  )

SELECT

    P.PARENT_PRODUCT,

    P.PRODUCT_CODE,

    P.LONG_NAME,

    year (MP.START_DATE),

    DATEDIFF(day,GETDATE(),MP.START_DATE),

    COUNT(distinct ORDER_NO +  cast (ORDER_LINE_NO as VARCHAR)),

    (select SUM(ACTUAL_TOTAL_AMOUNT) from ORDER_DETAIL ODS (NOLOCK)

     where  P.PARENT_PRODUCT = ODS.PARENT_PRODUCT AND ODS.LINE_STATUS_CODE = 'A' and ODS.LINE_STATUS_DATE    <= getdate())   

_________________________________

[Former Member]

If you create and use a table variable before you create the first temp table in the stored procedure, this seems to prevent the invalid object error, i.e.:

<CODE>

CREATE PROCEDURE myproc

AS

DECLARE @dummy TABLE (nothing bit);

INSERT INTO @dummy VALUES (0);

CREATE TABLE #mytemptable

...

</CODE>

[Former Member]

Based on my research I guess this is what I should do on the server. But too much stuff going on the server. I am afraid to try this in PRODUCTION . I do not that have a test environment at this time. I will try once I get a test environment. As a temporary solution I am creating a static table each time I run the SP and dropping it before the create statement instead of temp table.

Thank you so much Madhumitha for your help.

[Former Member]

Hello Rana,

For any new problems/queries I suggest you to post a new thread.

Thanks for your understanding!

Madhumitha

[former_member193452]

You should always make changes in a test environment.  The sbo edit can be tested on a local pc, by replacing editing the file on the client.

then use a webi rich client to access the universe locally.

once confirmed you will need to edit the same file on the server.

also, SP2 is years past.  Update to a current patch (6) is recommended.

Thanks,

J.

[Former Member]

Thanks Madhumita...We are on Sp2. Unfortunately I cant open the URL. it asking for credential and I dont think I have user account for the site its trying to taking me.

[Former Member]

Hello Rana,

I am pasting the resolution part of the KBA alone here:

For KBA: 1428264

> Close Universe Designer

> Edit odbc.sbo file

> Add the following under all libraries needed:   <Parameter Name="Force Execute">Always</Parameter

> Restart the BO Servers

> Relaunch Universe Designer

For KBA: 1272593

1.) Open the odbc.sbo file located in the following path:

  <install dir>\ Business ObjectsXIR3\Business Objects Enterprise12.0\win32_x86\dataAccess\connectionServer\odbc

2.) Add the following parameter in MS SQL Server 2000 session:

  <Parameter Name="Force Execute">Procedures</Parameter>

3.) Save the odbc.sbo file.

Please take a back up before making any changes. Also, I suggest you to restart your connection server.

Thanks,

Madhumitha

[Former Member]

All on a sudden I encountered a new issue on my Info view window. I am receiving an error (

(FWM 01002) . Error occoured only when I click Document  list from the Info view screen. People talking about load balancer URL and sticky cookies or sessions. but I dont know what are those stuffs and where to check for those. Anybody has any idea ?

)

https://plus.google.com/102235710095977736496/posts/amho7Hj7Lmn

[Former Member]

Hello Rana,

Another similar KBA for your reference:

1272593 - Error: "Exception: DBD, [Microsoft] [ODBCSQLServerDriver] Invalid object name' #TempApplic...

Thanks,

Madhumitha

[Former Member]

Hello Rana,

What is the version of BO you are using? Apply SP2 and follow the resolution mentioned in the following KBA:

1428264 - Universe Designer Inserting Stored Procedure With Temp Table errors

Thanks,

Madhumitha