topic Re: Using multiple result sets served by SQL Server stored procedures in QlikView

Using multiple result sets served by SQL Server stored procedures

Mon, 03 Aug 2015 13:16:28 GMT

Hi,

I'm fairly new to Qlikview (v11), and this issue has me stumped. I've searched previous answers, but none addresses my specific problem it seems.

I need to load two seperate result sets from an MS SQL Server, both which has to be fetched by a stored procedure (due some internal security issues). Both SPs are stored in the same database, so if possible, they might share a connection.

I believe I've tried all possible combinations of statement sequences in the load script, unfortunately without much success, the best result being having the latter result set being loaded, but not the former.

This is my basic approach, which doesn't work:

Sdata:

LOAD *;

ODBC CONNECT32 TO [NEC/GIS];

SQL EXEC spQlikviewData_S;

Pdata:

LOAD *;

ODBC CONNECT32 TO [NEC/GIS];

SQL EXEC spQlikviewData_P;

I've also tried building and calling a single SP, which returns both (two) result sets, but that only leaves me with one data set loaded, apparently.

I know that one can load virtually an unlimited number of datasets, so what's stumping me here ? Is there a limit on result sets fetched by stored procedures ?

Cheers

Lars

Re: Using multiple result sets served by SQL Server stored procedures

maxgro — Mon, 03 Aug 2015 15:42:47 GMT

// connect once, the db is the same

ODBC CONNECT32 TO [NEC/GIS];

Sdata:

LOAD *;

SQL EXEC spQlikviewData_S;

Pdata:

noconcatenate

LOAD *;

SQL EXEC spQlikviewData_P;

after the reload

open the table viewer ctrl+t

and verify if you have 2 tables with the correct number of records

Re: Using multiple result sets served by SQL Server stored procedures

Wed, 05 Aug 2015 10:44:04 GMT

Thanks Massimo, it got me part of the way.

I do see two tables in the "Ctrl-T" browser, but they're still connected to each other and a third table named "$Syn 1 Table".

My two datasets (SData and PData) have a number of identical field/column names, but I only want to connect them via one field column ("Journalnummer"), if possible.

How can I accomplish that ?

And is there a reason why my reloading still freezes/hangs QV for many minutes ?

Cheers

Lars

Re: Using multiple result sets served by SQL Server stored procedures

maxgro — Wed, 05 Aug 2015 12:20:32 GMT

Qlik associates by name of the field

So don't rename the Journalnummer field (Qlik will associate, you'll see a link in table viewer ctrl+t)

and rename (fieldname as newfieldname) the other fields with different names in the 2 tables (Qlik won't associate)

I suggest to start with few fields, below I used the field Journalnummer and 2 other fields (change it)

After reload I suppose you'll have only 2 tables with an association by Journalnummer

// connect once, the db is the same

ODBC CONNECT32 TO [NEC/GIS];

Sdata:

LOAD

Journalnummer, // join field, no rename

field1 as field1S, // no join, rename with different names in S and P

field2 as field2S

;

SQL EXEC spQlikviewData_S;

Pdata:

noconcatenate

LOAD

Journalnummer,

field1 as field1P

field2 as field2P

;

SQL EXEC spQlikviewData_P;

Regarding "$Syn 1 Table", when you have more than 1 common field between 2 tables, Qlik makes a syn table.

Synthetic Keys

And is there a reason why my reloading still freezes/hangs QV for many minutes ?

maybe because you have a lot of common fields and/or records in the 2 tables

Re: Using multiple result sets served by SQL Server stored procedures

Wed, 05 Aug 2015 15:04:26 GMT

Thanks again Massimo,

As I wrote, I'm fairly new to this, so your tips did help me very much.

The data loads fast now, and the Ctrl-T now shows two tables with a single connection.

So, off to find other problems to hazzle over 🙂

Cheers

Lars