Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED]SQL Stored Procedure with Temp Tables
Go
New
Search
Notify
Tools
Reply
  
[CLOSED]SQL Stored Procedure with Temp Tables
 Login/Join
 
Gold member
posted
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>,


WebFOCUS 8007
 
Posts: 51 | Registered: August 15, 2014Reply With QuoteReport This Post
Member
posted Hide Post
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?


WF 8.1
Windows Server 2008
SQL Server 2012
 
Posts: 24 | Registered: September 04, 2013Reply With QuoteReport This Post
Gold member
posted Hide Post
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


WebFOCUS 8
 
Posts: 74 | Location: Gahanna, OH | Registered: September 22, 2009Reply With QuoteReport This Post
Gold member
posted Hide Post
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?


WebFOCUS 8007
 
Posts: 51 | Registered: August 15, 2014Reply With QuoteReport This Post
Guru
posted Hide Post
-****************************************
-* 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,


WebFOCUS 8.1.05 / APP Studio
 
Posts: 272 | Location: Brazil | Registered: October 31, 2006Reply With QuoteReport This Post
Member
posted Hide Post
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...


WF 8.1
Windows Server 2008
SQL Server 2012
 
Posts: 24 | Registered: September 04, 2013Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED]SQL Stored Procedure with Temp Tables

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.