Loading from SQL Stored procedure not working

[#1]

Trying to load data from a SQL Server stored procedure, this works fine for other procedures but does not want to work in this instance.

Load *; Sql Exec [Report].[UspResults_SalesOrderKPI] @Company = '10' , @RedTagType = 'Q' , @RedTagUse = '' ;

I'm sure I'm missing something simple but I cannot see it.

The final code I will be using will be something like the below.

RawSysOrders: Load StockDescription As Product ,CustomerPoNumber As PO ,SalesOrder As PBL_Ref ,AcceptedDate ,ActualDeliveryDate As ActualShipDate ,Null() As Confirmation ,PlannedDeliverDate As ProposedShipDate ,Null() As RevisedShipDate1 ,Null() As RevisedShipDate2 ,Null() As RevisedShipDate3 ,OrderQty As Qty ,StockCode As Code ,Null() As Volume ,Job As Batch ,CustomerName As DeliveredTo ,CustomerName As SoldTo ,Null() As PaymentTerms ,ApplyMap('Map_SOPrices',SalesOrder&'_'&SalesOrderLine,0) As GBP ,0 As USD ,0 As Euro ,1 As USDRate ,1 As EuroRate ,0 As ShippingCost ,If(ActualDeliveryDate<=PlannedDeliverDate,'Yes','No') As ShipDateMet ,DaysDiff As DaysOVer ,DispatchComments As NotesProblems ,Year(EntrySystemDate) as File; where Year(EntrySystemDate)>=2016; Sql Exec [BlackBox].[Report].[UspResults_SalesOrderKPI] @Company = '10' , @RedTagType = 'Q' , @RedTagUse = 'Qlik Sense > Product KPIs';

[Gysbert_Wassenaar]

Line 30:

RawSysOrders: 
Load 
       StockDescription As Product 
       ,CustomerPoNumber As PO 
       ,SalesOrder As PBL_Ref 
       ,AcceptedDate 
       ,ActualDeliveryDate As ActualShipDate 
       ,Null() As Confirmation 
       ,PlannedDeliverDate As ProposedShipDate 
       ,Null() As RevisedShipDate1 
       ,Null() As RevisedShipDate2 
       ,Null() As RevisedShipDate3 
       ,OrderQty As Qty 
       ,StockCode As Code 
       ,Null() As Volume 
       ,Job As Batch 
       ,CustomerName As DeliveredTo 
       ,CustomerName As SoldTo 
       ,Null() As PaymentTerms 
       ,ApplyMap('Map_SOPrices',SalesOrder&'_'&SalesOrderLine,0) As GBP 
       ,0 As USD 
       ,0 As Euro 
       ,1 As USDRate 
       ,1 As EuroRate 
       ,0 As ShippingCost 
       ,If(ActualDeliveryDate<=PlannedDeliverDate,'Yes','No') As ShipDateMet 
       ,DaysDiff As DaysOVer 
       ,DispatchComments As NotesProblems 
       ,Year(EntrySystemDate) as File
       ;  // <== There's a ; that shouldn't be there
where 
       Year(EntrySystemDate)>=2016
       ; 
Sql Exec [BlackBox].[Report].[UspResults_SalesOrderKPI] @Company = '10' , @RedTagType = 'Q' , @RedTagUse = 'Qlik Sense > Product KPIs';

[#3]

Hi Gysbert,

There seems to be something more indepth with the code, I have tried this and even removed the preceding load - Qlik is not recognising that a dataset is being returned.

[Gysbert_Wassenaar]

Then you first need to make sure that the stored procedure executes correctly and does return results when called like that by a user with the credentials you specify in the connection you use in Qlikview. Perhaps it's a credentials problem, perhaps it's a driver problem and the oledb or odbc driver can't handle the records set that's returned.

[#5 · Accepted solution]

Reviewing the SQL code, I now believe I have resolved the issue.

Qlik does not handle SQL row counts well, at the beginning of my sql query I had an insert causing a row count of 1 row being affected.

In order to address this, I set "nocount on" at the beginning of the query, in addition I had to set "nocount off" before returning the data.

[Gysbert_Wassenaar]

Qlik does not handle SQL row counts well

Qlik doesn't handle the SQL at all. It is passed as-is to the driver and executed by the database server. Qlik merely loads the records that are returned by the driver. Blame your database or the oledb/odbc driver. Qlik has nothing to do with it.

[#7]

When looking at comparable tools, crystal reports via OLEDB and ODBC, this proc ran absolutely fine and results were shown. This was a Qlik specific response not recognising that data was being returned.

I have included the answer to help anyone else that writes SQL procs to feed Qlik Sense, I understand that the proscribed Qlik route would be to have a QVD layer instead but that doesn't work for everyone.

[Gysbert_Wassenaar]

Ok, if you're 100% sure you used the exact same drivers and credentials. But Qlikview and Qlik Sense really only communicate with the drivers. They don't do anything with the sql and expect one and only one result table.

[#9]

Hi,

Thank you for the discussion!!.

Could you please help in integrating oracle stored procedure data with QlikView.

I have tried all the above changes.

But I am not able to fetch Oracle stored procedure data into QlikView as well as insert records into Oracle table through stored procedure.

I have posted the issue in forum atQlikView integration with Oracle Stored procedure FAILS in fetching data and inserting data to oracl...

Could you please take a look at and advise for corrections.

Thanks a lot in advance.

Regards,

Laxmaiah Chowdary