Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QLIKWORLD LIVE! MAY 16 - 19TH, EARLY BIRD DISCOUNTS! REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
abdulsubhan_it
Contributor
Contributor

How to execute a sql stored procedure in Qlikview?

Hi Everyone,

I'm new to Qlikview, I have made a connection to the sql server.

Then i was trying to execute the sql stored procedure but unfortunately i didn't any thing where i can select the fields.

i'm enclosing my file plz check it out and let me know where i'm getting.

Thanks

ABDUL SUBHAN

9 Replies
d_koti24
Creator II
Creator II

OLEDB CONNECT TO [Provider=SQLNCLI10.1;Integrated Security="";Persist Security Info=...............................................];


SQL EXEC DatabaseName.dbo.ProcedureName @Para1 = $(vPara1), @Para2 = $(vPara2), @Para3 = $(vPara3), @Para4 = $(vPara4) ;

abdulsubhan_it
Contributor
Contributor
Author

tried so far,

FieldService_Cost:

LOAD "Tickte",

  "drss",

  "rig",

  "well",

  "jobtype",

  "Mobilization_date",

  "fromdate",

  "todate",

  "Equipments",

  jobcost;

SQL EXEC Sp_TicketCost @DeptID=4,@fromDate="03/01/2015", @toDate="03/31/2015";

i have tired.

FieldService_Cost:

LOAD "Tickte",

  "drss",

  "rig",

  "well",

  "jobtype",

  "Mobilization_date",

  "fromdate",

  "todate",

  "Equipments",

  jobcost;

SQL EXEC Sp_TicketCost 4,"03/01/2015", "03/31/2015";

i have tried this.. but still i didn't get.

plz help me.

Thanks

tamilarasu
Champion
Champion

Hi,

Try the below one

FieldService_Cost:

LOAD Tickte,

  drss,

  rig,

  well,

  jobtype,

  Mobilization_date,

  fromdate,

  todate,

  Equipments,

  jobcost;

SQL EXEC Sp_TicketCost @DeptID=4,@fromDate=03/01/2015, @toDate=03/31/2015;

Also try the below one,

SQL EXEC Sp_TicketCost @DeptID='4',@fromDate='03/01/2015', @toDate='03/31/2015';


If you get any error message, post here.

abdulsubhan_it
Contributor
Contributor
Author

When i tried your 1st code

i got the error

<pre>

ErrorSource: Microsoft OLE DB Provider for SQL Server, ErrorMsg: Incorrect syntax near '/'.

SQL EXEC Sp_TicketCost @DeptID=4,@fromDate=03/01/2015, @toDate=03/31/2015

</pre>

abdulsubhan_it
Contributor
Contributor
Author

I tried your 2nd code too,

still i'm not getting anything..

i mean, when i reloaded it, its nothing showing in fields tab of sheet-properties.

abdulsubhan_it
Contributor
Contributor
Author

i have tired using a stored procedure without any parameters and it was successfully executed.

<pre>

SET ThousandSep=',';

SET DecimalSep='.';

SET MoneyThousandSep=',';

SET MoneyDecimalSep='.';

SET MoneyFormat='$#,##0.00;($#,##0.00)';

SET TimeFormat='h:mm:ss TT';

SET DateFormat='M/D/YYYY';

SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

OLEDB CONNECT TO [Provider=SQLOLEDB.1;Persist Security Info=True;User ID=sa;Initial Catalog=DB_9B08BD_automobile;Data Source=localhost;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=DEVELOPER-PC;Use Encryption for Data=False;Tag with column collation when possible=False] (XPassword is EOIeQYIGTBNKDbEF);

Rigs:

LOAD "Rig_Name";

SQL EXEC SP_GETALL_RIGS;

</pre>

But when i'm using the stored procedure with parameters its not getting executed. why??

Can any one please help me.

Thanks

abdulsubhan_it
Contributor
Contributor
Author

Hi,

I have added this line in stored procedure

<pre>

SET NOCOUNT ON;

</pre>

and executed my stored procedure, thanks to almighty i successfully got it.

<pre>

OLEDB CONNECT TO [Provider=SQLOLEDB.1;Persist Security Info=True;User ID=sa;Initial Catalog=DB_9B08BD_automobile;Data Source=localhost;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=DEVELOPER-PC;Use Encryption for Data=False;Tag with column collation when possible=False] (XPassword is EOIeQYIGTBNKDbEF);

FieldService_Cost:

LOAD

  "Tickte",

  "drss",

  "rig",

  "well",

  "jobtype",

  "Mobilization_Date",

  "fromdate",

  "todate",

  JobCost;

SQL EXEC Sp_TicketCost '4','03/01/2015', '03/31/2015';

</pre>

Thanks Everyone

Not applicable

Hi Abdul,

Thank you for the discussion!!.

I am facing issues in integrating oracle stored procedure data with QlikView.

I am not able to fetch Oracle stored procedure data into QlikView as well as insert records into Oracle table through stored procedure.

I have posted the issue in forum atQlikView integration with Oracle Stored procedure FAILS in fetching data and inserting data to oracl...

Could you please take a look at and advise for corrections.

Thanks a lot in advance.

Regards,

Laxmaiah Chowdary