Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


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

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[CLOSED]SQL Stored Procedure with Temp Tables
 Login/Join
 
Scott Matson
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, 2014Report This Post
Vitoto
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, 2013Report This Post
JRLewis
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, 2009Report This Post
Scott Matson
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, 2014Report This Post
Ricardo Augusto
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, 2006Report This Post
Vitoto
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, 2013Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

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

Copyright © 1996-2020 Information Builders