Execute a stored procedure from Qlik Sense without loading data

[kdaniels-obrien]

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?

[SeekTheLight]

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;