topic Re: how to call store procedure from SQL Server in QlikView

how to call store procedure from SQL Server

ashis — Wed, 25 Nov 2020 16:16:04 GMT

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

Please help.

Re: how to call store procedure from SQL Server

prma7799 — Wed, 09 Aug 2017 07:51:17 GMT

Please check this

How to Call stored procedure in Qlikview

Execute a Stored Procedure in Qlikview | Qlik Community

Re: how to call store procedure from SQL Server

ashis — Wed, 09 Aug 2017 08:33:34 GMT

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.

Re: how to call store procedure from SQL Server

MayilVahanan — Wed, 09 Aug 2017 09:54:16 GMT

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

Re: how to call store procedure from SQL Server

ashis — Wed, 09 Aug 2017 12:23:22 GMT

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.

Re: how to call store procedure from SQL Server

ashis — Thu, 10 Aug 2017 05:30:11 GMT

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

SET NOCOUNT ON