Geneos - Gateway - What does the error message "ERROR: SQL Error in Stage: 'Extract xxx' mean in Gateway SQL

If you encounter this issue, first, you need to ensure that the dataview sources are available and that the license daemon is running with available token(s) for Gateway SQL. If these checks are true and still encounter the issue, then it is possible that you are using UNION statements and there are duplicate rownames in the dataview sources.

For example, you have source T1:

And source T2:

And you want to select all the rows from both sources using the UNION statement. But since there are duplicate rownames (ID=2), you will get the error:

select * from T1 union all select * from T2

To solve this issue, you can tick the Show row id option.

To troubleshoot this:

1. Enable the Gateway-SQL plugin's statusDetails and showQueries debug settings as seen below:

2. Save the configuration and open the Gateway logs.

3. Search for the keyword Gateway-sql::sql Details to extract the following log entries:

<Mon Jan 20 10:10:10> DEBUG: Gateway-sql::sql [Extract Status]: 'SELECT FileNo, Group, FileName FROM Custom_Table' <Mon Jan 20 10:10:10> DEBUG: Gateway-sql::sql Details of Sampler ERROR Message::     Sampler: '/geneos/gateway[(@name="my_gateway")]/directory/probe[(@name="my_probe")]/managedEntity[(@name="my_entity")]/sampler[(@name="my_sampler")][(@type="not_my_type")]'     View: 'Status'     Stage: 'Extract Status'     Message: 'near "Group": syntax error' 

Using the above, the Message parameter highlights that the error was caused by a syntax error. The syntax error was caused by not escaping the reserve keyword Group

To solve this, the said reserve keyword needs to be enclosed in double quotes ( " ).