Qlik Sense Enterprise on Windows: Execute SQL Set statements or Non Select Queries

[NadiaB]

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 statements or non-select queries using the Qlik ODBC Package and the native SQL Native Driver.

Environment

• Qlik Sense all versions.

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;