topic Re: How to Call stored procedure in Qlikview in QlikView

How to Call stored procedure in Qlikview

Wed, 13 Mar 2013 12:15:13 GMT

Hi,

I have a stored procedure which contains 3 input parameters with multiple select and Inner joins.

I want to Call the SP in Qlikview. I followed lots of Blogs but I did't got sucess to Execute the SP in Qlikview. Help me.

I am Using OLEDB and my calling method is:

SQL CALL [DBNAME].[dbo].[ABC] @_EndTime ='2012-12-31 00:21:06.550', @_StartTime = '2012-12-31 00:21:06.550',

@_UserName = 'XYZ';

Re: How to Call stored procedure in Qlikview

vikasmahajan — Wed, 13 Mar 2013 12:43:37 GMT

sql execute < Sql Proc Name>

Re: How to Call stored procedure in Qlikview

vikasmahajan — Wed, 13 Mar 2013 12:44:04 GMT

sql execute < Sql Proc Name> < parameters>

Re: How to Call stored procedure in Qlikview

Wed, 13 Mar 2013 14:12:41 GMT

can you please answer me in details with full syntex.

Re: How to Call stored procedure in Qlikview

Wed, 13 Mar 2013 14:35:43 GMT

See this discution.

http://community.qlik.com/message/235958#235958

Re: How to Call stored procedure in Qlikview

Thu, 14 Mar 2013 07:27:50 GMT

Ricardo,

I already gone through the link you gave me but unfortunately it is not working for me.

any other solutions ??

Re: How to Call stored procedure in Qlikview

Thu, 14 Mar 2013 07:45:16 GMT

Hope this helps

OLEDB CONNECT32 TO [Provider=SQLNCLI10.1;Integrated Security=SSPI;Persist Security Info=False;User ID="";Initial Catalog=MyDB;Data Source=.;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=WID;Initial File Name="";Use Encryption for Data=False;Tag with column collation when possible=False;MARS Connection=False;DataTypeCompatibility=0;Trust Server Certificate=False];

TableStorePoc:

SQL Exec [dbo].[GetItemsByCriteriaForUser]

@userId = NULL,

@userIdType = NULL,

@statuses = NULL,

@maxRowCount = 100,

@dateFrom = NULL,

@dateTo = NULL,

@onlyBasketItems = NULL

Re: How to Call stored procedure in Qlikview

Thu, 14 Mar 2013 10:30:31 GMT

Not Working need moreSuggestion ??

Re: How to Call stored procedure in Qlikview

Thu, 14 Mar 2013 10:31:38 GMT

Is your connection string proper ? are you able to connect to DB ?

Re: How to Call stored procedure in Qlikview

Thu, 14 Mar 2013 10:49:19 GMT

Yes connection string is correct I can pull data from any table. but I am looking to call SP in Qlikview.

FYI: in Store Procedure I have multiple select, multiple inner joins and SP is written in two parts union is ussed to join two parts of SP.

Re: How to Call stored procedure in Qlikview

Thu, 14 Mar 2013 11:03:00 GMT

BTW, are you getting any errors? If not please check the SQL trace.

mean while you try to create test SP and call from your QV. See if it works or not.

Re: How to Call stored procedure in Qlikview

Thu, 14 Mar 2013 11:49:52 GMT

See the error below:

if I'm doing like this the following error is coming :

ErrorSource: Microsoft OLE DB Provider for SQL Server, ErrorMsg: The EXECUTE permission was denied on the object 'SP name', database 'DBNAME', schema 'dbo'.

SQL exec [DBNAME].[dbo].[TABLENAME]

@_EndTime ='2012-12-31 00:21:06.550', @_StartTime = '2012-03-10 00:21:06.550', @_UserName = 'Sam.moni@xyz.com'

@KStreak S: In case of your syntex it is giving the same error.

what kind of pemission it requires for execution.

please give me some idea.

Re: How to Call stored procedure in Qlikview

Thu, 14 Mar 2013 11:56:20 GMT

As error says, You need to get Permission to execute. Please check for the perticula user/role you have permission or not. If not ask your DBA to change the permission setting for

Sam.moni@xyz.com'

Re: How to Call stored procedure in Qlikview

Thu, 14 Mar 2013 12:00:25 GMT

Try this GRANT EXEC ON dbo.xxxxxxx TO <UserName>

Re: How to Call stored procedure in Qlikview

Thu, 14 Mar 2013 12:18:44 GMT

My DBA gave me some permission in SQL Server but still the problem is same.

Can you tell me to get permission what i need to do................... and where ??

Re: How to Call stored procedure in Qlikview

Fri, 15 Mar 2013 05:14:25 GMT

Did you tried

GRANT EXEC ON dbo.xxxxxxx TO<UserName> ?

Re: How to Call stored procedure in Qlikview

Fri, 15 Mar 2013 06:42:56 GMT

Greate thanks to you KSteeak it is connecting and working.

Re: How to Call stored procedure in Qlikview

Fri, 15 Mar 2013 07:03:19 GMT

Request you to mark as an answer

Re: How to Call stored procedure in Qlikview

Mon, 18 Mar 2013 07:43:41 GMT

Soory for the delay -

After getting Execution access the following syntex works for me:

SQL Exec [DBNAME].[dbo].[TABLENAME]

@_StartTime = NULL,

@_EndTime = NULL,

@_UserName = NULL;

Thank you

Re: How to Call stored procedure in Qlikview

Mon, 18 Mar 2013 08:50:29 GMT

In Addition to this one more Query i have :

This is something I am getting based on particular user details but i need the same for all users, for that I wants to Store the SP data into QVD on regular basis. that helps to relate other tables with QVD to produce meaningfull Dashboard. any idea how to do this ??