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

[dirk_konings]

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.

D.

[#3]

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

     a,

     b,

     c;

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.

[#4]

Ive got the following:

Costs:

Load A, B, C

sql exec ABC_CostQlikView

[#5]

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

[#6]

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

[#7]

Thats what i have but still no joy. 

Buckets:

Load[Financial Period] AS FinancialPeriod  ;

sql execute QLik_Buckets

[#8]

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.

[#9]

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

[#10]

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

AS

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