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

Resolved Calling sys.sp_setapprole from outside transaction

  1. How-to
0
Votes
Undo
  1. Larry Pettit
    Larry Pettit
  2. PowerBuilder
  3. Thursday, 30 May 2024 18:17 PM UTC

After successfully connecting to a Microsoft SQL Server database using SQLCA in PowerBuilder 2022 R2 and the MSOLEDBSQL 19.3.3.0 driver, I am trying to call the sp_addapprole stored procedure using the following code:

string ls_sql
ls_sql = "sp_setapprole @rolename=N'rolename', @password=N'" + "rolepassword" + "'" (rolename and rolepassword have been replaced)
execute immediate :ls_sql;

sqlca.sqlcode is -1

sqlca.sqlerrortext = The procedure 'sys.sp_setapprole' cannot be executed within a transaction.

How can I execute this stored procedure outside a transaction?  Everything we are currently doing is within a transaction.

Top Secret PowerBuilder Developer (near Washington...
Webservice redirect uri
Accepted Answer
Chris Pollach @Appeon
Chris Pollach @Appeon Accepted Answer Pending Moderation
  1. Thursday, 30 May 2024 20:12 PM UTC
  2. PowerBuilder
  3. # Permalink
Please try instead ... SQLCA.AutoCommit = TRUE Connect using SQLCA; execute immediate :ls_sql;
Comment
There are no comments made yet.
Responses (3)
Larry Pettit
Larry Pettit Accepted Answer Pending Moderation
  1. Thursday, 30 May 2024 19:18 PM UTC
  2. PowerBuilder
  3. # 1

execute immediate "End Transaction"; returns the following:

SQLSTATE = 42000
Microsoft OLE DB Driver 19 for SQL Server
Incorrect syntax near the keyword 'Transaction'.

Comment
Load more comments
Larry Pettit
  1. Larry Pettit
  2. Thursday, 30 May 2024 20:04 PM UTC
By the way, thank you for your help!!
  1. Helpful
Larry Pettit
  1. Larry Pettit
  2. Thursday, 30 May 2024 20:10 PM UTC
The following appears to work:

SQLCA.AutoCommit=false

Connect;

SQLCA.AutoCommit = true

execute immediate :ls_sql;

SQLCA.AutoCommit = false



So apparently you can change the value of AutoCommit after you have connected and it takes effect from that point forward.
  1. Helpful
Chris Pollach @Appeon
  1. Chris Pollach @Appeon
  2. Thursday, 30 May 2024 20:22 PM UTC
Yes, you can now change the AutoCommit "on the fly" vs in the old PowerSoft days of PB you could not. It had to be done before the Connect.

Yes, the AC feature must be used carefully as it takes you out of a Begin/End transaction sequence.

PB Automatically issues an End/Begin transaction on a Commit when AC is false.

Glad that this worked for you .. thanks for the update! :-)
  1. Helpful
There are no comments made yet.
Chris Pollach @Appeon
Chris Pollach @Appeon Accepted Answer Pending Moderation
  1. Thursday, 30 May 2024 19:09 PM UTC
  2. PowerBuilder
  3. # 2

Hi Larry;

   Have you tried ...

  • execute immediate "End Transaction"
  • execute immediate :ls_sql;
  • execute immediate "Begin Transaction"

Regards .. Chris

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.
We use cookies which are necessary for the proper functioning of our websites. We also use cookies to analyze our traffic, improve your experience and provide social media features. If you continue to use this site, you consent to our use of cookies.