Can not execute SQL SEVER Stored Procedure in Qv

[#1]

Hi Everyone,

Anybody knows how to load data from stored procedure?

I need to load data from sql server Stored Precedure in Qlikview. But each time no data or table loaded without error message.

Connect to sql server OK. The script work fine in sql server client.

I wrote the script like this:

Below is the ending snapshot after finished reloading.

[#2]

Mine is like this :

7/31/2015 5:28:27 PM: 0021  OLEDB CONNECT*Provider*

7/31/2015 5:28:29 PM: 0023  sql exec  [dbo].[proc_Name] '120','A'

7/31/2015 5:28:34 PM:      Execution finished.

Curious.

[markodonovan]

Hi Duo,

If possible can you give some details of the stored procedure you are using?

Does it just use a simple select ?

Mark

techstuffybooks.com

[markodonovan · Accepted solution]

Hi Duo,

Can you check that NOCOUNT is set to ON.

Here is my stored procedure :

USE [testdb]

GO

/****** Object:  StoredProcedure [dbo].[get_test_data]    Script Date: 07/31/2015 10:55:19 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- =============================================

-- Author:        <Author,,Name>

-- Create date: <Create Date,,>

-- Description:    <Description,,>

-- =============================================

ALTER PROCEDURE [dbo].[get_test_data]

AS

BEGIN

    -- SET NOCOUNT ON added to prevent extra result sets from

    -- interfering with SELECT statements.

    SET NOCOUNT ON;

    -- Insert statements for procedure here

    SELECT branch from dim_branch

    where branch is not null

END

Thanks

Mark

techstuffybooks.com

[#5]

  It is not written by myself and i can not see the script. I'm sorry. But I don't think it is simple select.

[#6]

No such script as i have check with the creator of the stored procedure.

Do we have to set "NOCOUNT " ON ? What is it used for? Will is influence the outcomes?

[markodonovan]

Just read that it caused someone a problem with their stored procedure.

It prevents the number of rows affected being returned.

Mark

[sasiparupudi1]

may be

load * sql exec  ('[dbo].[proc_Name] "120" ,"A" ' )

[#9]

Thank you. But it did not work.

[sasiparupudi1]

have a look at the post

Execute a Stored Procedure in Qlikview

[markodonovan]

Hi Duo,

Can you check that the connection string is using the right database.

It is seems like the stored procedure exists but a select statement is being executed because even if not data was returned from the select you would see that '0 lines fetched' in the log file.

Let me know how you get on.

Mark

techstuffybooks.com