SQL Stored Procedure

[#1]

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

[#2]

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.

[#3]

Hi

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

[#4]

Here is the SP content

CREATE PROCEDURE [dbo].[CostQlikView]

AS

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

[Colin-Albert]

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.

[#6]

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? 

[#7]

Any thoughts/help on this please?

[#8]

any help on this please?

[Colin-Albert]

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.

[daveamz]

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

[#11]

Thats seems to have done something.

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