U00003754 Database-error in SQLI-variable, REST Migration Assistant

book

Article ID: 129173

calendar_today

Updated On:

Products

CA Automic Workload Automation - Automation Engine

Issue/Introduction

When executing/resolving a SQLI variable that calls the stored procedure of the REST Migration Assistant, the following error is displayed:

Error: U00020274 Wrong SQL statement in 'WS.REST.MIGRATION.EXECUTE.MIGRATION.FUNCTION'. DB error: 'U00003754 Database-error in SQLI-variable: 'U00003590 UCUDB - DB error: 'SQLExecDirect', 'ERROR ', '42000', 'SELECT failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'.

Cause

Incorrect settings for Quoted Identifiers

Environment

MSSQL Database
Automation Engine v12.2 / v12.3

Resolution

Investigate where QUOTED_IDENTIFIERS may be set differently. Possible locations:

1) Within the SQL statement itself, in the SQLI variable. 

2) Within the stored procedures on each affected database. 
  • Open the stored procedure in the Studio under <AUTOMIC_DB> / Programmability / Stored Procedures / ws_rest_*
  • Right click, Script Stored Procedure as > DROP and ALTER to > New  Query Editor Window
  • If in the first lines it says: SET QUOTED_IDENTIFIER OFF change it to SET QUOTED_IDENTIFIER ON
  • Run the procedure 

This needs to be done for all ws_rest_* procedures. Alternatively, drop the stored procedures by running WEBSERVICE_REST_MIGRATION_CLEANUP.TXT with the DB Load Utility.
Then follow point 3 below to make sure that the procedure are loaded with SET QUOTED_IDENTIFIER ON from the onset and use the DB Load Utility to reload WEBSERVICE_REST_MIGRATION.TXT

3) Within the ODBC connection settings.

Reference: https://communities.ca.com/thread/241804277

Open your ODBC Administator tool and check the following set ups: 

  • Change to the <System DSN> Tab
  • Choose your database and click  <Configure>
  • Proceed further on until you reach <Change the default database to> 
  • This tab shows the <ANSI> settings 
  • Check if the checkmarks are checked on <Use ANSI quoted identifiers> and <Use ANSI null, paddings and warnings>
These check marks are also recommended from our side. For more information, please see our documentation:

https://docs.automic.com/documentation/webhelp/english/AWA/12.2/DOCU/12.2/AWA%20Guides/help.htm#AWA/Admin/admin_CreatingODBCDataSource.htm

After modifications, restarting the WPs is necessary, this may be done in a cyclic fashion.

If this does not resolve the error message, provide screenshots of the above settings for all affected environments to Support via a ticket and reference this knowledge article to help expedite resolution.