Execute a stored procedure from Qlik Sense without loading data

kdaniels-obrien — Sat, 16 Nov 2024 18:15:36 GMT

Hi,

I would like to execute a stored procedure in Qlik before I load data from another stored proc. Right now, my load script connects to the database then uses:

[MyTable]:

SQL exec [my stored procedure] 'my parameter 1', 'my parameter 2', 'my parameter 3';

I receive the error: Unable to get column information for the fields that are used in the query: Object reference not set to an instance of an object.

I have checked the QvOdbcConnectorPackage.exe.config found in: C:\Program Files\Common Files\Qlik\Custom Data\QvOdbcConnectorPackage and can confirm that the allow non select queries is set to True.

Could someone provide me with a solution for executing this stored proc in Qlik without needing to load data?

Re: Execute a stored procedure from Qlik Sense without loading data

SeekTheLight — Fri, 02 Jun 2023 19:44:39 GMT

I wrestled with this a bit.

Whenever you call a stored procedure, Qlik expects to load data into a Qlik table from that procedure. So have each stored procedure return a table of some kind. If you don't need the returned table, just drop the data on the floor.

My solution (because I could edit a procedure that did not return a value), was to return a table. Using SQL Server, inside the procedure, 1) I added the declaration of a table, 2) I populated the table, and 3) I returned a value from the table. The text that I added looked something like this:

Declare @t table(col1 int);

...

insert into @t (col1) Select 1 -- This stored a single row into the table, with a value of "1".

Select * from @t

end

Then, inside Qlik I loaded the table that was returned by the procedure (a single row with a value of 1) into a Qlik table, and then dropped the Qlik table. The syntax was something like this:

Load1:
Load *;
SQL EXEC ...;
drop Table Load1;

topic Re: Execute a stored procedure from Qlik Sense without loading data in App Development

Execute a stored procedure from Qlik Sense without loading data

Re: Execute a stored procedure from Qlik Sense without loading data