topic Re: SQL Stored Procedure in QlikView

SQL Stored Procedure

Mon, 17 Mar 2014 13:43:16 GMT

I have a sp called SP_Qlikview that returns columns A, B and C so in my script I have the following:

Load

A, B, C

Sql exec SP_Qlikview.

Question is what table does this load into in the script as when i run the reload the table viewer doesn't show anything. In addition, in my script preceding this statement as a number of concatendate load statements for three fact tables.

Thanks

Re: SQL Stored Procedure

dirk_konings — Mon, 17 Mar 2014 13:48:54 GMT

if you call this SP in SQL Server, do you get any return (table) ?

Maybe you don't get any data. Look in the generated log file of your reload and find the number of fetched records after this SQL Exec.

Re: SQL Stored Procedure

Mon, 17 Mar 2014 13:55:48 GMT

a few things to consider.

1) if using SQL Server. don't use Stored Procedure names starting with SP_ use something else like QVSP_ cause the engine begins to search through all of the system stored procedures for your procedure as they are also named SP_ and are designed for System Functions. This can speed up response time.

2) Before your proceeding load statement do this.

TableNameX:
Load

SQL EXECUTE QVSP_MyStoredProcedure @Param1='Value1',@Param2='Value2';

If you name your table, you can normally find the table faster.

When Concatenating tables, Specify the table you want to concatinate with.

Concatenate (TableNameX);
This will ensure that the data gets loaded into the correct tables. Otherwise it will join to the previous load statement.

Re: SQL Stored Procedure

Mon, 17 Mar 2014 15:54:40 GMT

Ive got the following:

Costs:

Load A, B, C

sql exec ABC_CostQlikView

Re: SQL Stored Procedure

Mon, 17 Mar 2014 16:11:50 GMT

when i run that and look at the table viewer but also the select fields there is no table included?

Re: SQL Stored Procedure

Mon, 17 Mar 2014 16:26:35 GMT

The Below should provide an exact replica of how to do this. I just did in as a test example and tested it then copied the code and pasted it into the window here. Mine uses two parameters. But those are not required.

FYI the "Attacker" is the name of my local machine. (I am also a CEH as well as a BI Architect)

OLEDB CONNECT TO [Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=CHOLTP_DEV;Data Source=ATTACKER;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=ATTACKER;Use Encryption for Data=False;Tag with column collation when possible=False];

NewTable:

Load *;

SQL EXECUTE [dbo].[spReport_GetYearlyFunerals] 1, 2008

Re: SQL Stored Procedure

Mon, 17 Mar 2014 16:42:02 GMT

Thats what i have but still no joy.

Buckets:

Load[Financial Period] AS FinancialPeriod ;

sql execute QLik_Buckets

Re: SQL Stored Procedure

Mon, 17 Mar 2014 16:47:57 GMT

Test your stored procedure and make sure it is returning.

If you are using Dynamic SQL in your SP you will want to change that to use a defined temp table or table variable.

The problem with Dynamic sql is that when initialized the SQL engine can not send the execution plan with the defined return fields to the reporting engine before executing the stored procedure. Thus you get no fields returned. This is common. It also happens in Cognos, Tableau, and SQL Reporting Services.

Re: SQL Stored Procedure

Mon, 17 Mar 2014 21:44:54 GMT

the Sp writes to a temp table to return the results. Any thoughts?

Re: SQL Stored Procedure

Tue, 18 Mar 2014 05:28:53 GMT

at the top of your procedure put a statement representing the fields of the stored procedure to give them names. this will cause SQL to send back the initial response from the Query Optimizer to QlikView reporting the fields names before the execution of the stored procedure. This will ensure that the app sees the table structure returned by the stored procedure.

Create Procedure dbo.spWebCall_MySPName

BEGIN

IF 1=0

BEGIN

SELECT

CONVERT(int, '0') as Record_ID,

Convert(Varchar(50), '') as Field1,

Convert(Varchar(50), '') as Field2,

Convert(Varchar(50), '') as Field3,

Convert(Varchar(50), '') as Field4

WHERE 1 = 0

END

/*Your Logic Goes Here

Be Sure to return the same data types and field names

To the repoting Engine that are defined at the top of the procedure*/

--SELECT * FROM #TEMPTABLE

END

Re: SQL Stored Procedure

Tue, 18 Mar 2014 05:31:06 GMT

there is rhyme and reason to the If 1=0 and WHere 1=0. It ensures that this code is never executed. But it holds a direct result out of the procedure. so make that structure the same as the temp table.

Re: SQL Stored Procedure

Tue, 18 Mar 2014 08:51:14 GMT

Could you include an example in the above. Are you replacing the '' with the actual field name from the temptable?

Re: SQL Stored Procedure

Tue, 18 Mar 2014 09:23:25 GMT

Here is the SP content

CREATE PROCEDURE [dbo].[CostQlikView]

BEGIN

DECLARE curFP CURSOR FAST_FORWARD LOCAL FOR

SELECT TOP 6 FinancialPeriod

FROM PLC_CostBuckets

GROUP BY FinancialPeriod

ORDER BY FinancialPeriod DESC

DECLARE @fp datetime

IF OBJECT_ID('tempdb..#CostA') IS NOT NULL

DROP TABLE #CostA;

CREATE TABLE #CostA ([Class] varchar(200)

,[Financial Period] datetime

)

OPEN curFP

FETCH NEXT FROM curFP INTO @fp

WHILE @@FETCH_STATUS = 0

BEGIN

INSERT INTO #CostA ([Class],[Financial Period])

EXEC dbo.Report_Cost @FinancialPeriodParam = @fp, @GroupGroup = 'All', @Service = 'All'

UPDATE #CostA SET [Financial Period] = @fp WHERE [Financial Period] IS NULL

FETCH NEXT FROM curFP INTO @fp

END

CLOSE curFP

DEALLOCATE curFP

Re: SQL Stored Procedure

Colin-Albert — Tue, 18 Mar 2014 09:30:22 GMT

You need a semicolon at the end of the A,B,C line, and SQL line

Costs:

Load A, B, C ;

sql exec ABC_CostQlikView ;

You also need to enable "Open database in read write mode" in the connection settings if using stored procedures.

Re: SQL Stored Procedure

Tue, 18 Mar 2014 10:19:04 GMT

Ive done that but after reload the Costs do not appear still in the list of tables to select from in the select fields. I dont understand why that is?

Re: SQL Stored Procedure

Tue, 18 Mar 2014 13:46:59 GMT

Any thoughts/help on this please?

Re: SQL Stored Procedure

Tue, 18 Mar 2014 13:47:25 GMT

any help on this please?

Re: SQL Stored Procedure

Colin-Albert — Tue, 18 Mar 2014 13:54:24 GMT

Have you looked at the log file QlikView creates when reloading the document. This will show the actual sql query that is executed and how many rows are returned.

Re: Re: SQL Stored Procedure

daveamz — Tue, 18 Mar 2014 14:06:30 GMT

You have to set nocount on in your SP

CREATE PROCEDURE [dbo].[CostQlikView]

AS

SET NOCOUNT ON

BEGIN

DECLARE curFP CURSOR FAST_FORWARD LOCAL FOR

   SELECT TOP 6 FinancialPeriod

   FROM PLC_CostBuckets

   GROUP BY FinancialPeriod

   ORDER BY FinancialPeriod DESC

DECLARE @fp datetime

IF OBJECT_ID('tempdb..#CostA') IS NOT NULL

   DROP TABLE #CostA;

CREATE TABLE #CostA ([Class]      varchar(200)

                    ,[Financial Period]        datetime

                              )

OPEN curFP

FETCH NEXT FROM curFP INTO @fp

WHILE @@FETCH_STATUS = 0

BEGIN

   INSERT INTO #CostA ([Class],[Financial Period])

   EXEC dbo.Report_Cost @FinancialPeriodParam = @fp, @GroupGroup = 'All', @Service = 'All'



   UPDATE #CostA SET [Financial Period] = @fp WHERE [Financial Period] IS NULL

   FETCH NEXT FROM curFP INTO @fp

END

CLOSE curFP

DEALLOCATE curFP

David

Re: Re: SQL Stored Procedure

Tue, 18 Mar 2014 14:25:41 GMT

Thats seems to have done something.

However, when i do reload the script I get a OLEDB read failed error . Please advise?