• You are here:  
  • Home
  • Q&A
  • Q&A
  • PowerServer
  • Failed to parse the statement sp_set_session_context

Subscribe via RSS
Toggle Submenu
  1. Categories
  2. Recent
  3. Tags
View Replies (5)

Resolved Failed to parse the statement sp_set_session_context

  1. How-to
0
Votes
Undo
  1. Alex Peters
    Alex Peters
  2. PowerServer
  3. Tuesday, 6 February 2024 06:21 AM UTC

Hello!
I have the problem that I have to call the MS SQL Server stored procedure sp_set_session_context to set a context variable. Unfortunately this does not work with the PowerServer.

The PowerServer always gives me the message

'PowerServer.Api.ServerApiController.ImmediateExecute (PowerServer.Api)' get result (
{
"IsSuccess": false,
"RequestId": null,
"AppName": "OV",
"NameSpace": "OV",
"ErrorCode": 0,
"ErrorMessage": "Failed to parse the statement 'EXEC sp_set_session_context N'username', N'apeters'': The function or stored procedure might not exist."
}).

 

The call is no problem with Powerbuilder. I have also tried various variations, unfortunately all without success.

ls_sql = "EXEC sys.sp_set_session_context N'" + as_key + "', N'" + as_value + "';"
EXECUTE IMMEDIATE :ls_sql USING THIS;

alternative

DECLARE usp_createproc PROCEDURE FOR sys.sp_set_session_context
key=:as_key, value=:as_value USING THIS;
EXECUTE usp_createproc;

How can I actually solve my problem?

I am using PowerBuilder / PowerServer version 22.0.0.1892

 

https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-set-session-context-transact-sql?view=sql-server-ver16

 

Thanks for the help

Alex

 

  1. PowerBuilder
  2. PowerServer
  3. SQL Server
  4. Stored Procedure
  5. .NET 6.0
Publish Web APIs to Docker
Getting "Error: Session creation failed. Cause: (H...
Responses (5)
Angie Liu@Appeon
Angie Liu@Appeon Accepted Answer Pending Moderation
  1. Tuesday, 9 April 2024 00:59 AM UTC
  2. PowerServer
  3. # 1

Hi Alex;

Regarding this issue, we have made some enhancements in the latest version. When users use Dynamic SQL Format 1, if parsing stored procedure parameters fails, we will directly hand over the syntax to the database for execution. This method is suitable for system stored procedures without return parameters, solving the simple invocation of system stored procedures for some users.

We invite you to download the version and experience the functionality. If you encounter any problems, please provide feedback to us, and we will continue to enhance it.

You can obtain the installer from our Downloads portal at https://account.appeon.com/download (login required).

We appreciate all feedback from you!

Regards,

Angie

Comment
There are no comments made yet.
Alex Peters
Alex Peters Accepted Answer Pending Moderation
  1. Wednesday, 7 February 2024 10:27 AM UTC
  2. PowerServer
  3. # 2

Thanks to all for the response, I will try the trick with the wrapper stored procedure.

 

Comment
There are no comments made yet.
Angie Liu@Appeon
Angie Liu@Appeon Accepted Answer Pending Moderation
  1. Wednesday, 7 February 2024 05:37 AM UTC
  2. PowerServer
  3. # 3

Hi Alex;

 

We have locally reproduced your problem and submitted it to the developer.

To better track and handle this problem, we suggest you open a ticket for it at our support portal https://www.appeon.com/standardsupport/newbug.

 

For now, it is recommended that you refer to Jim Nesbitt's advice to work around this problem.

 

Regards

Angie

Comment
There are no comments made yet.
Jim Nesbitt
Jim Nesbitt Accepted Answer Pending Moderation
  1. Tuesday, 6 February 2024 12:17 PM UTC
  2. PowerServer
  3. # 4

Additional info - I have a proc in a PB app that calls sp_set_session_context, but it's inside a stored procedure. It works fine for PowerBuilder and Powerserver and worked in  22.0.0.1892. 

Comment
Jim Nesbitt
  1. Jim Nesbitt
  2. Tuesday, 6 February 2024 16:12 PM UTC
For Powerserver, I also had to set longconnection to 1 in DBPARM to persist the session context. Typically APIs connect to DB and then disconnect, so session context is gone once API call completes. Setting long connection worked fine - we would have needed major rework otherwise.
  1. Helpful 1
There are no comments made yet.
Jim Nesbitt
Jim Nesbitt Accepted Answer Pending Moderation
  1. Tuesday, 6 February 2024 12:11 PM UTC
  2. PowerServer
  3. # 5

Hi - This is more of a workaround than a solution - but if you already have any working PowerServer code to call a stored procedure with parameters and no errors, you could try setting up a new 'wrapper' stored procedure to call sp_set_session_context and call the wrapper procedure instead to see if it works. 

Comment
There are no comments made yet.
  • Page :
  • 1


There are no replies made for this question yet.
However, you are not allowed to reply to this question.