Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join this live chat April 6, 10AM EST - QlikView to Qlik Sense REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
ashis
Creator III
Creator III

how to call store procedure from SQL Server

Hi ,

i have following store procedure in SQL server that generate data in tabular format.

DECLARE @Instance   < Table type variable >
   DECLARE @ShowQuery  < Table type variable>
   NSERT INTO @Instance SELECT Instance_ID FROM dbo.xx_Instance
   NSERT INTO @ShowQuery Values(0)
   EXEC SP_NAME @DateFrom = '1 Jul 2017', @DateTo = '5 Jul 2017', @Instance = @Instance, @ShowQuery = @ShowQuery, @ReportName = '', @Debug = 1
  
I want to call this in QlikView i am trying the following steps however it is not doing anything ,


sp:
SQL

DECLARE @Instance   < Table type variable >
   DECLARE @ShowQuery  < Table type variable>
   NSERT INTO @Instance SELECT Instance_ID FROM dbo.xx_Instance
   NSERT INTO @ShowQuery Values(0)
   EXEC SP_NAME @DateFrom = '1 Jul 2017', @DateTo = '5 Jul 2017', @Instance = @Instance, @ShowQuery = @ShowQuery, @ReportName = '', @Debug = 1

Please help.

5 Replies
prma7799
Master III
Master III

ashis
Creator III
Creator III
Author

Hi  PM, thank you for the quick reply.

I have seen these posts but it did not work.

There is something that I am doing wrong.

MayilVahanan

Hi

May be try like this

Use OLEDB connection string in Write mode

xx_Instance:

Load *;

SELECT Instance_ID FROM dbo.xx_Instance

Let vInstance = Peek('Instance_ID' , 0, 'xx_Instance');

SQL Exec [DBNAME].[dbo].SP_NAME @DateFrom = '1 Jul 2017', @DateTo = '5 Jul 2017', @Instance = '$(vInstance)', @ShowQuery = 0, @ReportName = '', @Debug = 1

Thanks & Regards,
Mayil Vahanan R
ashis
Creator III
Creator III
Author

No, It is not working,

Even a simple store procedure like below is not returning any data, is there any other setting that I am missing.

Table:
SQL  EXEC SP_NAME  @DateFrom = '10 Jul 2017', @DateTo = '17 Jul 2017',@ReportName = '', @Debug = 1 ;

I am not getting any error though however no data return.

ashis
Creator III
Creator III
Author

It is working now , after putting the following lines before the store procedure.

SET NOCOUNT ON