In order to include SQL SET statements or non select queries in the script depending of the driver configurations might be needed. The following is a basic sample that shows how to include SQL SET statement or non select queries using Qlik ODBC Package and native SQL Native Driver.
Environment:
The information in this article is provided as-is and to be used at own discretion. Depending on tool(s) used, customization(s), and/or other factors ongoing support on the solution below may not be provided by Qlik Support.
Qlik ODBC Package
a SET statement is considered a non select query, therefore it needs to be enabled, setting allow-nonselect-queries to true (see instructions here)
in addition it will be necessary to include !EXECUTE_NON_SELECT_QUERY in the query
Option 1
Simple query
SQL SET ANSI_NULLS ON !EXECUTE_NON_SELECT_QUERY;
SQL SET NOCOUNT ON !EXECUTE_NON_SELECT_QUERY;
SELECT * FROM
master.sys.syslanguages;
Option 2
If you get syntax errors you can attempt to use a variable
vSQLScript = 'SET NOCOUNT ON !EXECUTE_NON_SELECT_QUERY;
SELECT * FROM
master.sys.syslanguages;';
SQL $(vSQLScript);
Option 3
If none of the above suggestions worked you might need to use a DB object as a workaround (store procedure, views, etc), this is a sample with an Store Procedure:
SQL EXEC sp_databases;
SQL Native Driver
The driver can be downloaded from Microsoft, make sure is up to date and compatible with your SQL Server DB (ODBC Driver for SQL Server)
Option 1
Simple query
SQL SET NOCOUNT ON ;
SELECT * FROM
master.sys.syslanguages;
Option 2
If you get syntax errors you can attempt to use a variable
vSQLScript = 'SET NOCOUNT ON ;
SELECT * FROM
master.sys.syslanguages;';
SQL $(vSQLScript);
Option 3
If none of the above suggestions worked you might need to use a DB object as a workaround (store procedure, views, etc), this is a sample with an Store Procedure:
SQL EXEC sp_databases;
Related Content
Connector reply error: Executing non-SELECT queries is disabled. Please contact your system administ...
