When writing SQL statements inside Synergy or CallScripter, issues can arise when using reserved keywords as column or table names, or when the column or table name has a space in it. In either instance, the normal process would be to wrap the column or table name is square brackets within SQL; however, this can then cause problems if the name is shared with a Workflow field.
This might produce a variety of different error messages depending on the column or table name that is being used, and requires a specific process to reference these columns or table from within Synergy or CallScripter.
Example
If you had a table with information about trade unions that you do business with, you might write a query like this:
SELECT ID, Union, Main Address
FROM Trade_Unions
The intent for this statement is to return all the information about the trade unions, however since the word Union is a reserved keyword for an SQL UNION operator, and the Main Address column name has a space in it, the query will be misinterpreted and it will fail with an error such as the following:
Incorrect syntax near the keyword 'Union'.
Incorrect column name 'Main'.
The normal way of addressing the issue in SQL would be to wrap the column names in square brackets, for example:
SELECT ID, [Union], [Main Address]
FROM Trade_Unions
However in Synergy and CallScripter, this cannot be used because square brackets may be interpreted as a Field or Variable in the Workflow. To avoid this, double-quotes can be used to identify the column instead:
SELECT ID, "Union", "Main Address" -- All values will need to have double quotes
FROM Trade_Unions
Notes
Depending on the version of SQL server and the settings that have been defined for the database, you may need to set QUOTED_IDENTIFIER to ON at the top of your query:
SET QUOTED_IDENTIFIER ON;
SELECT ID, "Union", "Main Address"
FROM Trade_Unions
Article ID: 3265, Created: July 16, 2018 at 10:13 AM, Modified: July 18, 2018 at 1:44 PM