Unable to create data in data pool

book

Article ID: 141306

calendar_today

Updated On:

Products

CA Test Data Manager (Data Finder / Grid Tools)

Issue/Introduction

We are trying to create data for the Data Pool (generator) based on the subset rules that we created in GT SUBSET.

We see that one table has a column name as "KEY" (which is a keyword)

Below is the error that we are getting and these tables which has this error are not being USED in the Data Pool.

Failed to insert data for TABLE_NAME

SQLSTATE = 42000
Microsoft SQL Server Native Client 11.0
Incorrect syntax near the keyword 'KEY'.

No changes made to database.

INSERT INTO MV_196_98626_3100 ( column_1, KEY, VALUE, DELETED, DELETED_DT, DELETEDBY, CREATED_DT, CREATEDBY, MODIFIED_DT, MODIFIEDBY, VERSION ) VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )

Please advise

Cause

The error you are seeing is getting thrown by SQL Server. The SQL State 42000 is thrown because of a Syntax error or because of access violation. See https://docs.microsoft.com/en-us/sql/odbc/reference/appendixes/appendix-a-odbc-error-codes?redirectedfrom=MSDN&view=sql-server-ver15 for SQL Server Error codes.

Doing a Google search on "SQLSTATE = 42000 Incorrect syntax near the keyword 'KEY'" and I see this is a very common error when there's a column name with a reserved keyword, such as "Function", "References", and "Key".

For a list of SQL Server Reserved Keywords, see https://docs.microsoft.com/en-us/sql/t-sql/language-elements/reserved-keywords-transact-sql?redirectedfrom=MSDN&view=sql-server-ver15

Environment

Release : 4.8

Component : CA Test Data Manager

Resolution

If you have another SQL utility, such as Microsoft SQL Server Management Studio (MSSMS) installed on the SQL Server machine, you can use this utility to test the SQL statement to see if you are getting the same syntax error. If you are getting the same error, then it's not an issue with the TDM component, but rather with the SQL statement you are running. However, if the SQL statement returns the expected results, and no errors, but you are seeing errors thrown from the TDM Component, then the issue is clearly with TDM, and we need to have Engineering look closer at the issue.


Your SQL Server DBA should be able to help you determine the syntax issue and how to address it in your SQL statement. In my Google search I ran across references, which state that anytime you run a SQL query against a SQL Server database that contains fields, which use reserved keywords, these fields need to be escaped by placing the keyword in double quotes or in square brackets, such as "Key" or [Key]. I suggest verifying this information with your SQL DBA, to verify this is true for your release of SQL Server. 

Additional Information

If you are seeing this problem while attempting to update the GTREP database, you will need to add the keyword to "gtrep_reserved_names" table under the "rn_word" column, which will force Datamaker to use square brackets when querying the specified keyword.