Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

Loading from SQL Stored procedure not working

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';
1 Solution

Accepted Solutions
Not applicable

Re: Loading from SQL Stored procedure not working

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.

8 Replies

Re: Loading from SQL Stored procedure not working

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';

Not applicable

Re: Loading from SQL Stored procedure not working

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.

Re: Loading from SQL Stored procedure not working

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.

Not applicable

Re: Loading from SQL Stored procedure not working

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.

Re: Loading from SQL Stored procedure not working

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.

Not applicable

Re: Loading from SQL Stored procedure not working

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.

Re: Loading from SQL Stored procedure not working

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.

Not applicable

Re: Loading from SQL Stored procedure not working

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