I am writing a procedure that calls a stored procedure on a MSSQL 2008 server. The stored procedure create a handful of temporary tables (#tb_Temp0, etc). However, when I execute the stored procedure I receive the following error:

(FOC1400) SQLCODE IS 208 (HEX: 000000D0) XOPEN: 42S02
: Microsoft OLE DB Provider for SQL Server: [42S02] Invalid object name '#
: tb_Temp0'.
L (FOC1405) SQL PREPARE ERROR.
0 ERROR AT OR NEAR LINE 11 IN PROCEDURE ADHOCRQ FOCEXEC *
(FOC205) THE DESCRIPTION CANNOT BE FOUND FOR FILE NAMED: SQLOUT
BYPASSING TO END OF COMMAND

I have no problems running other stored procs from this same server that do not create or drop temp tables.

here is the code I am trying to run:

ENGINE SQLMSS SET CURSORS CLIENT
ENGINE SQLMSS SET DEFAULT_CONNECTION APMESHIST

ENGINE SQLMSS SET ANSI_WARNINGS ON
ENGINE SQLMSS SET NOCOUNT ON
SQL SQLMSS PREPARE SQLOUT FOR
EXEC dbo.pr_Rpt_R5556001_Prod_WC_Sum_PrfmYld 'CM19', 'All-CM', '11/02/2015 00:00', '11/04/2015 00:00';

END


Any ideas on why I can't run this stored procedure?

This message has been edited. Last edited by: <Emily McAllister>,

Hi!

I think the issue must be related with the user you assigned to the adapter, and it must have assigned no permissions to créate or drop temp tables at that database.

I dont think there are any error with the call you are doing to the procedure, nor with webfocus, nor sqlserver. There must be a security issue with that user.

Could you change the user at wf adapter at yours to see that it is the problema?

Specifying PREPARE SQLOUT FOR on your SQL initialize statement tells WebFOCUS that a hold file is expected to be created with the statement. What if you took removed that from the statement and coded like below.

ENGINE SQLMSS SET CURSORS CLIENT
ENGINE SQLMSS SET DEFAULT_CONNECTION APMESHIST

ENGINE SQLMSS SET ANSI_WARNINGS ON
ENGINE SQLMSS SET NOCOUNT ON
SQL SQLMSS
EXEC dbo.pr_Rpt_R5556001_Prod_WC_Sum_PrfmYld 'CM19', 'All-CM', '11/02/2015 00:00', '11/04/2015 00:00';

END

That eliminates the error message, but how do I then use the dataset produced by that stored proc to write a report? Eventually I have to save it to a hold file, don't I?

-****************************************
-* SET SQLMSS ENGINE TO RUN SQL QUERIES
-****************************************
ENGINE SQLMSS SET DEFAULT_CONNECTION CONNECTION_NAME
SQL SQLMSS PREPARE SQLOUT FOR


EXECUTE OWNER.SP 'PAR1', 'PAR2', 'PARN'
;
END



TABLE FILE SQLOUT
PRINT *
END

This message has been edited. Last edited by: Ricardo Augusto,

Why doesnt check if my answer is right or wrong?

Seeing your first post, it seems you doesnt have any problema to launch the procedure at your SqlServer. It is itself (the stored procedure) that ends with error, and it is that it is tryying to use a table that doesnt exist, so it may not have been created (by the sored procedure itself I asume) so, the problem is excatlly this. The stored procedure dont créate the temp table, so it must be the user its running this stored procedure (it must be the user at webfocus adaptor to your sqlserver)

Your call to the stored procedure, seems right, I'd check writing the logs of the stored procedure to see exactly where is the error... and maybe, just maybe..., discovering that that was the problem. But maybe not of course...