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] Calling stored procedure, error on temp table?

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[CLOSED] Calling stored procedure, error on temp table?
 Login/Join
 
Sean Sweeney
Silver Member
posted
Good Afternoon,

I'm trying to call a stored procedure I have in my MSSQL DB so that I don't have to reproduce a large amount of logic in AppStudio. I have it calling properly with the following:

quote:

SQL SQLMSS PREPARE SQLOUT FOR
exec dbo.getUserPerms '00000000-0000-0000-0000-000000000000', ''
END


I know it's "working" as I get the following result:

quote:

(FOC1400) SQLCODE IS 208 (HEX: 000000D0) XOPEN: 42S02

(FOC1500) : Microsoft OLE DB Provider for SQL Server: [42S02] Invalid object name
(FOC1500) : '#tempPermIDs'.
L (FOC1405) SQL PREPARE ERROR.


The object it is barking about is a temp table created in the stored procedure like so:

quote:

CREATE TABLE #tempPermIDs (PermissionID UNIQUEIDENTIFIER)


Running the exec straight in management studio, (as the user I'm using to connect through AppStudio), works fine, returns the proper result set. So curious, what might I be missing?

Thanks!

This message has been edited. Last edited by: FP Mod Chuck,


Windows 8203 All output formats
 
Posts: 47 | Registered: November 30, 2018Report This Post
MartinY
Virtuoso
posted Hide Post
Are you sure that the users who runs from AS is the same as if you run it from SQLMS ?

Even if your AS user is the same as MSQMS, your IB server may have been configured to run with a generic user that is not "you" and that generic user may not have the right to create temp table in SQL.


WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2409 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Report This Post
FP Mod Chuck
Virtuoso
posted Hide Post
Sean

FYI... Another way to call a SQL Stored procedure is to create a synonym for it and use that instead of the SQL passthru. When you create the synonym there is a drop down that says Restrict object type and you choose Stored Procedure.


Thank you for using Focal Point!

Chuck Wolff - Focal Point Moderator
WebFOCUS 7x and 8x, Windows, Linux All output Formats
 
Posts: 2127 | Location: Customer Support | Registered: April 12, 2005Report This Post
Sean Sweeney
Silver Member
posted Hide Post
Good Morning

Martin - so I would be looking under "Manage Adapters"... select appropriate adapter, properties, the user listed there? If so yep I'm testing that user in my management studio.

Chuck - Interesting. Gave that a whirl and I get a different error:

L (FOC1487) SQL SYNTAX ERROR dbo.getUserPerms

I'll see if I can get another, more basic, stored procedure working with this route and go from there.

Any more advice / thoughts are greatly appreciated.

Thanks!


Windows 8203 All output formats
 
Posts: 47 | Registered: November 30, 2018Report This Post
Sean Sweeney
Silver Member
posted Hide Post
Woohoo, well that was quick haha.

Just created a simple stored procedure that does a select on a basic table and both routes, (SQL passthru and synonym), worked perfectly.

So, are there stored procedure mechanics, (I don't know... case statements?), that are not going to jive with AppStudio?

Thanks!


Windows 8203 All output formats
 
Posts: 47 | Registered: November 30, 2018Report This Post
FP Mod Chuck
Virtuoso
posted Hide Post
Sean

I think you should open a case with techsupport on this one. They can help you get traces to detect the underlying problem.


Thank you for using Focal Point!

Chuck Wolff - Focal Point Moderator
WebFOCUS 7x and 8x, Windows, Linux All output Formats
 
Posts: 2127 | Location: Customer Support | Registered: April 12, 2005Report 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] Calling stored procedure, error on temp table?

Copyright © 1996-2020 Information Builders