nowinski
commented
3 years ago My thoughts are the QA Status is actually a system field that is assigned a numeric value which only prints as a text string once it has been related to the QAStatus table in CDMS. The "Ready for QA" value is actually applied to new imports automatically, but receives the numeric value of 6.
Yes! QAStatusId is a system field in the detail tables and only accepts integer values. CDMS services is not expecting a string value for QAStatus and does not wrap the incoming value in quotes while building the insert statement. This explains why SQL Server returns ...
Msg 156, Level 15, State 1, Line 2 Incorrect syntax near the keyword 'for'.
Wrapping that string value in quotes and running the insert statement manually in SQL Server produces a new error:
Msg 245, Level 16, State 1, Line 1 Conversion failed when converting the varchar value 'Ready for QA' to data type int
Possible workaround (untested)--use integers in the import file QAStatus column
Possible long term fix--map QAStatus strings to appropriate integer values during import
We recently had an employee importing data he had reconstructed to meet the output of data from CDMS. Specifically for this issue, it meant he had included both the "QA Status" and "QA Comments" fields. He had included the values of "Ready for QA" in the "QA Status" field.
I reproduced this error by importing a single file into an an existing dataset, including the two QA fields: QA Status = 'Ready for QA', QA Comments = null. After passing through the column mapper, location mapper, and the import preview (0 errors), the import still fails:
In devtools (F12), I found this:
The CDMS log file shows an SQL INSERT statement: 2020-11-10 10:08:58.3658|DEBUG|services.Controllers.CDMSController|INSERT INTO SGS_Redd_Detail (ActivityId,ByUserId,EffDt,RowStatusId,RowId,QAStatusId,Count,Latitude,Longitude,ReddSpecies,WPTComments,WPTName,WPTType) VALUES ( 60738,3207,'11/10/2020 10:08:58 AM',0,1,Ready for QA,0,null,null,'S_CHN',null,'BLANK','New Redd')
I went back into the import file and removed the "Ready for QA" value (the 'QA Status' field itself still present), and the import will work. Removing the QA fields entirely also works. My thoughts are the QA Status is actually a system field that is assigned a numeric value which only prints as a text string once it has been related to the QAStatus table in CDMS. The "Ready for QA" value is actually applied to new imports automatically, but receives the numeric value of 6.
I went back into the file and in the QA Status column inserted the value of "for". This broke the import. I believe that the importer is bringing in this column with it's literal value which is causing the INSERT statement in SQL to fail due to the inclusion of the "for" word, which is an SQL keyword/verb. I included the word "for" in a non-system field in the import, and this didn't affect the import.
This behavior seems to be specifically associated with including an SQL keyword/verb within the values of a system field.