U00003754 Database-error in SQLI-variable, REST Migration Assistant
Article ID: 129173
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'.
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
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.
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:
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.
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>
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.
Feedback
Yes
No
Powered by
