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] Calling stored procedure, error on temp table?
Go
New
Search
Notify
Tools
Reply
  
[CLOSED] Calling stored procedure, error on temp table?
 Login/Join
 
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, 2018Reply With QuoteReport This Post
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.0.1M gen 240, Dev 8.2.04 gen 48, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2105 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Reply With QuoteReport This Post
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: 1506 | Location: Customer Support | Registered: April 12, 2005Reply With QuoteReport This Post
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, 2018Reply With QuoteReport This Post
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, 2018Reply With QuoteReport This Post
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: 1506 | Location: Customer Support | Registered: April 12, 2005Reply With QuoteReport This Post
  Powered by Social Strata  
 

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-2018 Information Builders, leaders in enterprise business intelligence.