Connector reply error: Executing non-SELECT queries is disabled. Please contact your system administrator to enable it.
Qlik ODBC connector package (database connector built-in Qlik Sense) fails to reload with error Connector reply error:
Executing non-SELECT queries is disabled. Please contact your system administrator to enable it.
The issue is observed when the query following SQL keyword is not SELECT, but another statement like INSERT, UPDATE, WITH .. AS or stored procedure call.
Qlik Sense Enterprise on Windows
Qlik Sense Enterprise SaaS
Qlik Sense Desktop
See the Qlik Sense February 2019 Release Notes for details on item QVXODBC-1406.
By default, non-SELECT queries are disabled in the Qlik ODBC Connector Package and users will get an error message indicating this if the query is present in the load script. In order to enable non-SELECT queries, allow-nonselect-queries setting should be set to True by the Qlik administrator.
To enable non-SELECT queries:
Modify the QvOdbcConnectorPackage.exe.config found in the locations mentioned below. Set the parameter allow-nonselect-queries to True
This is case-sensitive. true will not work.
In a multi node environment, the changes need to be applied to all nodes. Configuration file QvOdbcConnectorPackage.exe.config locations:
Qlik Sense Enterprise: C:\Program Files\Common Files\Qlik\Custom Data\QvOdbcConnectorPackage
Qlik Sense Desktop: C:\Users\user-name\AppData\Local\Programs\Common Files\Qlik\Custom Data\QvOdbcConnectorPackage
Note: As we are modifying the configuration files, these files will be overwritten during an upgrade and will need to be made again.
Now non-select statements are already enabled. If you need to run SQL statements without any data table returned (INSERT/ UPDATE/ DELETE/ DROP), add keyword !EXECUTE_NON_SELECT_QUERYat the end of the query. Other queries that return data (such as WITH ... AS in PostgreSQL) does not need this keyword.