how to call store procedure from SQL Server

[ashis]

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.

[prma7799]

Please check this

How to Call stored procedure in Qlikview

Execute a Stored Procedure in Qlikview | Qlik Community

[ashis]

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

[ashis]

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]

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

SET NOCOUNT ON