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,

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.

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.

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!

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!

Sean

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