• You are here:  
  • Home
  • Q&A
  • Q&A
  • PowerBuilder
  • Insert/Update/Delete failed after the migration from SQL Anywhere to MS SQL Server

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

Resolved Insert/Update/Delete failed after the migration from SQL Anywhere to MS SQL Server

  1. Issue
0
Votes
Undo
  1. Sathishkumar S
    Sathishkumar S
  2. PowerBuilder
  3. Wednesday, 4 May 2022 12:36 PM UTC

Hi Appeon,

I am doing the migration from SQL Anywhere to MS SQL Server Database 

PB2019R3

Microsoft SQL Server 2019 (RTM) - 15.0.2000.5 (X64)

when I run the application and try to save the changes from the screen I am getting the below error 

 

Microsoft OLE DB Driver for SQL Server
UPDATE failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or xml data type methods and/or spatial index operations.

To rectify the above error I have added DelimitIdentifier=1 in the INI file on DBParm part then error is not coming but update is not happening.

when I check the profiler update statement is generating but it is not affecting in the database.

 

Same I am trying in the powerbuilder DW itself

When I directly check with the datawindow I am getting the error when Enclose Table and Column Names in Quotes is not checked

SQLSTATE = 42000
Microsoft OLE DB Driver for SQL Server
UPDATE failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations.

No changes made to database.

UPDATE table SET column = 'Scan iTY' WHERE column = 1001

when I checked it Enclose Table and Column Names in Quotes and tried then the update is done and reflecting in the database.

 

 But when I run the application from the power builder IDE and try to do from the user screen it is not happening.

when I check from the SQL Profiler update statement is generated but not affected in the database 

 

 

I am unable to find the reason for it. 

Please advice how to rectify the issue, and how to configure the ini file for Microsoft OLE DB Driver for SQL Server.

Thank you

Sathishkumar S

  1. OLEDB
  2. SQL Server
Install error while installing PB 2019 R3 Runtime ...
Toolbar item icon not appearing
Responses (4)
mike S
mike S Accepted Answer Pending Moderation
  1. Wednesday, 4 May 2022 12:46 PM UTC
  2. PowerBuilder
  3. # 1

those screen shots of the database profile setup shows the settings you use to connect a datawindow within the IDE only.  it has nothing to do with the settings used when the application actually runs.

 

you need to update the settings on the transaction object.

 

You should also review your indexed views and/or compute columns in relation to the error the sql server provides

Comment
Load more comments
Sathishkumar S
  1. Sathishkumar S
  2. Wednesday, 4 May 2022 13:52 PM UTC
Hi Mike,



Autocommit is False while initiating the connection

Commit is based on the transaction object so not doing anything additionally

There is no error coming up from the application it seems saved but actually not happened and if I took the same update statement from SQL Profiler and execute it from the SSMS then it is executing.



Thank you.

  1. Helpful
mike S
  1. mike S
  2. Saturday, 7 May 2022 22:05 PM UTC
if autocommit is false, then you need to commit after the update/insert/delete
  1. Helpful
Sathishkumar S
  1. Sathishkumar S
  2. Tuesday, 10 May 2022 11:17 AM UTC
Hi Mike,



I am using PFC where commit already in place once the update is done successfully then it will go for commit else rollback in the transaction object.



as of now I made autocommit as True to continue working an find is there further difficulties during the migration from SQL Anywhere to MS SQL DB.



Thank you

Sathishkumar

  1. Helpful
There are no comments made yet.
John Fauss
John Fauss Accepted Answer Pending Moderation
  1. Wednesday, 4 May 2022 13:41 PM UTC
  2. PowerBuilder
  3. # 2

 

What we do in our commercial application is issue several SQL "Set" commands (such as SET QUOTED_IDENTIFIER ON", for example) immediately after successfully connecting to the database as part of the login/startup process to ensure critical settings are configured properly for every user session. Maybe something to consider?

If the connection properties in the IDE are working correctly, you can edit the DB profile in use, select the "Preview" tab and see the equivalent SQLCA settings in PowerScript statements; there is even a "Copy" button to copy the content into the Windows Clipboard to help you set the same values in your code.

Comment
There are no comments made yet.
Sivaprakash BKR
Sivaprakash BKR Accepted Answer Pending Moderation
  1. Thursday, 5 May 2022 07:37 AM UTC
  2. PowerBuilder
  3. # 3

Can you try changing this setting in SQL Server?

Select the database, then right click, choose properties.  In the options pane, try changing this settings and see whether the issue gets resolved. 

Happiness Always
BKR Sivaprakash

 

Comment
Sathishkumar S
  1. Sathishkumar S
  2. Thursday, 5 May 2022 18:11 PM UTC
Hi BKR Sivaprakash,



I have tried it but it seems not working, when I connect from Ini file the SET QUOTED_IDENTIFIER is going Off and if I add DelimitIdentifier=1 in DBParm the error is not coming but the update/insert/delete not affecting the database.

If I set the DelimitIdentifier=1, autocommit = True then no error and update is affecting in the tables.



Kindly suggest the best solution for this.

Thank you
  1. Helpful
There are no comments made yet.
Sivaprakash BKR
Sivaprakash BKR Accepted Answer Pending Moderation
  1. Saturday, 7 May 2022 05:17 AM UTC
  2. PowerBuilder
  3. # 4

Hello,

Have you gone through this page?

https://www.appeon.com/developers/get-help/knowledgebase/powerbuilder-update-failed-because-following-set-options-have-incorrect-settings-quotedidentifier-arithabort.html

Can you try calling following two statements before calling datawindow update?

Execute the following statementsbeforethe UPDATE call:

EXECUTE IMMEDIATE "SET QUOTED_IDENTIFIER ON" USING SQLCA;

EXECUTE IMMEDIATE "SET ARITHABORT ON" USING SQLCA;

HTH

Happiness Always
BKR Sivaprakash

 

Comment
Sathishkumar S
  1. Sathishkumar S
  2. Monday, 9 May 2022 10:19 AM UTC
Hi BKR Sivaprakash,



Thank you, I will try as you suggest and update how it goes.



Thank you

Sathishkumar
  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.