• You are here:  
  • Home
  • Q&A
  • Q&A
  • PowerBuilder
  • Unable to update a table via linked server (PB2022 / MS SQL Server)

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

Resolved Unable to update a table via linked server (PB2022 / MS SQL Server)

  1. Advice
0
Votes
Undo
  1. Hugo Moors
    Hugo Moors
  2. PowerBuilder
  3. Tuesday, 23 May 2023 07:19 AM UTC

I'm pulling my hair out again.

I'm connecting to a server A which is connected to a linked server B for security reasons, to insert and update 1 table.

In SQL Manager I can do what needs to be done (read, update, insert, delete).

In PB2022 (build 1892) database painter I can do what needs to be done (read, update, insert, delete).

I take these connection parameters letter per letter (and the update, insert or delete statement) and connect to the server, OK, read the table OK

update, insert, delete NOT OK, I get this error (that is the way the linked server is connected not my app connection to the DB):

SQLSTATE = 01000
Microsoft OLE DB Driver 19 for SQL Server
OLE DB provider "SQLNCLI11" for linked server "LinkedServer" returned message "No transaction is active.".

What can possibly be different from the PB painter vs. the application?

I've tried all ways to connect (SNC Native, MSOLEDB, ODBC, ADO) all have the same behavior.

I've tried via datawindow, via SQL in code, via stored procedure, via OPENQUERY nothing works.

For the linked server RPC, RPC Out are True, Enable Promotion of Distributed Transaction is True (also tried with them off)

Is PB setting some extra parameters that are not displayed in the connection syntax?

 

tool called CDMATCH for powerbuilder
Localized runtime
Accepted Answer
Peter Pang @Appeon
Peter Pang @Appeon Accepted Answer Pending Moderation
  1. Wednesday, 24 May 2023 05:35 AM UTC
  2. PowerBuilder
  3. # Permalink

Hi Hugo,

 

The application tries to set SQLCA.AutoCommit = True when connecting to the database.

 

Best Regards,
Peter

Comment
Hugo Moors
  1. Hugo Moors
  2. Wednesday, 24 May 2023 09:08 AM UTC
I was setting AutoCommit = FALSE (it is also in the connection parameter preview in the PowerBuilder Database painter!)

I tried setting AutoCommit = TRUE and ... it works !!!

Thank you!
  1. Helpful 1
There are no comments made yet.
Responses (3)
Peter Pang @Appeon
Peter Pang @Appeon Accepted Answer Pending Moderation
  1. Wednesday, 24 May 2023 05:13 AM UTC
  2. PowerBuilder
  3. # 1

Hi Hugo,

I did not reproduce the issue. Please open a support ticket in the support portal and please provide a simple PB case & the detailed steps to reproduce the issue, Many thanks in advance.
https://www.appeon.com/standardsupport/

Best Regards,
Peter

Comment
Hugo Moors
  1. Hugo Moors
  2. Wednesday, 24 May 2023 05:37 AM UTC
I'm not sure it's something with Powerbuilder or the specific setup at my client. For other linked servers I do not have a problem.

They have gone a bit far in locking everything that could be remotely accessed from outside so maybe there's a firewall or something on the network blocking something but like I said it's strange that Powerbuilder database painter has no issues but the application does.

I'll look further into the connection parameters as Arnd suggested.

As for opening a ticket, I don't know the setup of the linked server and even all the settings of the main DB server, I'm not a DB but ultimately I think the problem is there.







  1. Helpful
There are no comments made yet.
Hugo Moors
Hugo Moors Accepted Answer Pending Moderation
  1. Tuesday, 23 May 2023 14:49 PM UTC
  2. PowerBuilder
  3. # 2

I've done a trace with SQL Server Profiler and can't see any difference in the connection parameters.

The first is with the application (insert and update don't work, select does), the second login is with PowerBuilder (everything works) both connecting via MSOLEDBSQL with the same user from the same laptop

 

It is really strange I copy/paste the insert or update into the ISQL window of the Database painter in Powerbuilder and it works 100%

In the application it doesn't.

 

Comment
Arnd Schmidt
  1. Arnd Schmidt
  2. Tuesday, 23 May 2023 16:59 PM UTC
Hi Hugo,

check if you are really using the identical(!) connection parameters (user, password, providerstrings etc. ) and if your powerbuilder application does not set an application role in the sql server.

Best way to debug... search for the CONNECT in your PowerBuilder application and inspect all transaction properties.

Then check for sp_setapprole.



https://learn.microsoft.com/en-us/sql/relational-databases/security/authentication-access/application-roles?view=sql-server-ver16

hth

Arnd
  1. Helpful 2
Hugo Moors
  1. Hugo Moors
  2. Wednesday, 24 May 2023 08:57 AM UTC
Good suggestion but we don't use application roles.

And like I said the connection from the Powerbuilder DB Painter is identical to the connection in the application.

  1. Helpful
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.