topic Re: Store procedure with parameters in QlikView

Store procedure with parameters

sona_sa — Thu, 16 Oct 2014 10:27:38 GMT

Hi,

I have a procedure with 5 parameters, Now I want to execute it in QlikView and want to Store all the records in QVD. I am using below the following script but it is giving error. So please 'let me know how it can be resolve.

Error is :

Script Which I am using in QlikView Script:

usp_EPCM_Get_MultipleSearch:

SQL EXECUTE [dbCertifiedInventory_Live].[dbo].[usp_EPCM_Get_MultipleSearch]

@LotID varchar(MAX)='262,260',

@FromDate varchar(150)='',

@ToDate varchar(150)='',

@JewlexRapPriceDate datetime = '01-Apr-2014',

@GIARapPriceDate datetime = '01-Apr-2014';

Store usp_EPCM_Get_MultipleSearch into E:\Amit Kumar\EPCM\usp_EPCM_Get_MultipleSearch.Qvd;

Thanks

Re: Store procedure with parameters

Peter_Cammaert — Thu, 16 Oct 2014 10:42:34 GMT

Strange. A typical Stored Procedure call should look like this:

usp_EPCM_Get_MultipleSearch:

SQL EXEC [dbCertifiedInventory_Live].[dbo].[usp_EPCM_Get_MultipleSearch] '262.260', '', '', '01-Apr-2014', '01-Apr-2014';

STORE usp_EPCM_Get_MultipleSearch INTO ...

Now, the syntax for everything between SQL and the semicolon should follow T-SQL rules, as it will be sent to the DB Engine just as if it came from a native SQL Server query. Your RDBMS is complaining that the calling format isn't correct, but that's more of a topic for a SQL Server forum...

Re: Re: Store procedure with parameters

sona_sa — Thu, 16 Oct 2014 11:20:04 GMT

Hi Peter,

Thanks for reply...

Please let me know why it is storing in my defined path.

Error is :

Re: Store procedure with parameters

Peter_Cammaert — Thu, 16 Oct 2014 11:26:29 GMT

The message says that the Reload engine cannot find the table to store. Most probably this is because the SQL EXEC returns 0 rows. In that case QlikView decides to not create a internal table called usp_EPCM_Get_MultipleSearch and therefor the STORE statement will fail.

Re: Store procedure with parameters

tresB — Thu, 16 Oct 2014 11:29:31 GMT

Try removing comma from the number, like:

@LotID varchar(MAX)='262,260',

@LotID varchar(MAX)='262260',

Re: Store procedure with parameters

sona_sa — Thu, 16 Oct 2014 11:35:06 GMT

Is there any way to store the same SP information in QVD.

Re: Store procedure with parameters

sona_sa — Thu, 16 Oct 2014 11:35:45 GMT

Hi tresesco,

Not done.

Re: Store procedure with parameters

Peter_Cammaert — Thu, 16 Oct 2014 11:45:04 GMT

I don't understand what you mean with "SP information"? The only interface to an RDBMS is via SQL queries that are sent as-is to the DB engine. The SQL EXEC you are using is just another SQL statement but it differs from a regular SQL SELECT in that it doesn't instruct the RDBMS to go and look for data rows, it just instructs the RDBMS to execute a predefined routine (a piece of code) that is stored in the DB itself. You could replace an SQL EXEC by a (probably) very complex SQL query in your QV Load Script but that would let performance take a deep dive.

A QVD is not a DB engine; it's just a file that stores a single table and is generated from an existing internal QV table by way of a single STORE statement. All this happens locally on your development/server platform A QV therefor cannot be used to store database code, unfortunately.

I think you're stuck with your Stored Procedure. But that's not a bad thing. You'll only need to figure out how to properly call the SP. Can you get in touch with the developer of the Stored Procedure?

Peter

Re: Store procedure with parameters

sona_sa — Fri, 17 Oct 2014 04:36:47 GMT

Yes I contact the SQL Developver. When I am executing the same SP with the same parameter I am getting 85 no. of rows, Same data I want to store in QVD by using same SP with the same parameter.

Thanks.

Re: Store procedure with parameters

jonathandienst — Fri, 17 Oct 2014 05:18:39 GMT

Remove the type definitions in the parameters, and conditionally store the results if data is returned:

usp_EPCM_Get_MultipleSearch:

SQL EXECUTE [dbCertifiedInventory_Live].[dbo].[usp_EPCM_Get_MultipleSearch]

@LotID = '262,260',

@FromDate = '',

@ToDate = '',

@JewlexRapPriceDate = '01-Apr-2014',

@GIARapPriceDate = '01-Apr-2014';

If Alt(NoOfRows('usp_EPCM_Get_MultipleSearch'), 0) > 0 Then

Store usp_EPCM_Get_MultipleSearch into E:\Amit Kumar\EPCM\usp_EPCM_Get_MultipleSearch.Qvd;

End If

HTH

Jonathan

Re: Store procedure with parameters

sona_sa — Fri, 17 Oct 2014 06:29:49 GMT

Hi Jonathan,

Thanks for reply.

Output is not coming.

But Same SP with same parameter it is giving 85 Rows in SQL.

Where I am wrong please let me know, It will very helpful for me.

Thanks

Re: Store procedure with parameters

sona_sa — Fri, 17 Oct 2014 07:18:08 GMT

Any Update

Re: Store procedure with parameters

sona_sa — Fri, 17 Oct 2014 11:02:29 GMT

Now the final SP Code with all the predefined parameters. Please check the code.

Is it correct or not ?

usp_EPCM_Get_MultipleSearch:

Load

[AvgClarity1_j],

[AvgClarity1_p],

[AvgClarity1_v],

[AvgClarity2_p],

[AvgClarity2_v],

[AvgClarity3_j],

[AvgClarity3_p],

[AvgClarity3_v],

[AvgColor_F],

[AvgColor_j],

[AvgColor_p],

[AvgColor_v],

[Certification_j],

[Certification_v],

[CertifiedPer],

[Clarity_P],

[Clarity1_j],

[Clarity1_v],

[Clarity2_j],

[Clarity2_P],

[Clarity2_v],

[Clarity3_j],

[Clarity3_P],

[Clarity3_v],

[Clarity4_j],

[Clarity4_P],

[Clarity4_v],

[Clarity5_j],

[Clarity5_P],

[Clarity5_v],

[ClarityAvg1_F],

[ClarityCode1_j],

[ClarityCode1_v],

[ClarityCode2_j],

[ClarityCode2_v],

[ClarityCode3_j],

[ClarityCode3_v],

[ClarityID1_j],

[ClarityID1_v],

[ClarityID2_j],

[ClarityID2_v],

[ClarityID3_j],

[ClarityID3_v],

[Color_j],

[Color_P],

[Color_v],

[Color1_j],

[Color1_v],

[Color2_j],

[Color2_P],

[Color2_v],

[Color3_P],

[Color4_j],

[Color4_P],

[Color4_v],

[Color5_j],

[Color5_P],

[Color5_v],

[ColorCode1_j],

[ColorCode1_v],

[ColorCode2_j],

[ColorCode2_v],

[ColorCode3_j],

[ColorCode3_v],

[ColorID_j],

[ColorID_v],

[Concatenate_j],

[Concatenate_v],

[CPF_P],

[CPF2_P],

[CPF3_p],

[CPS_j],

[CPS_v],

[cps1_j],

[cps1_v],

[cps2_j],

[cps2_v],

[cps3_j],

[cps3_v],

[CPS7_j],

[CPS7_v],

[CPS8_j],

[CPS8_v],

[Criteria_Mst_Count1_j],

[Criteria_Mst_Count1_v],

[Criteria_Mst_Count2_j],

[Criteria_Mst_Count2_v],

[Criteria_Mst_Count3_j],

[Criteria_Mst_Count3_v],

[Date_j],

[Date_v],

[Diff_perc],

[DiffHPtoActual],

[DollerIncentive_PenaltytoParty],

[EsRR],

[EstimatedLabourValue_j],

[EstimatedLabourValue_v],

[EstimatedRoughValue_j],

[EstimatedRoughValue_v],

[FinalColorCode1],

[FinalResultasPerJW],

[finaltotal],

[Fluorescence_j],

[Fluorescence_v],

[FluorescenceID1_j],

[FluorescenceID1_v],

[Galaxy_j],

[Galaxy_v],

[GD],

[HPEstPurity],

[HPTOFINAL],

[HPTOPARTY],

[ID_j],

[ID_v],

[Incentive_Penalty],

[IncentivePenaltyParty],

[Invoice$],

[jlxprice1_j],

[jlxprice1_v],

[jlxprice1Police_j],

[jlxprice1Police_v],

[jlxprice2_j],

[jlxprice2_v],

[jlxprice2Police_j],

[jlxprice2Police_v],

[jlxprice3_j],

[jlxprice3_v],

[jlxprice3Police_j],

[jlxprice3Police_v],

[Labour],

[LabourName_j],

[LabourName_v],

[Lot_v],

[LotDetailID_j],

[LotDetailID_v],

[LotID_j],

[LotID_v],

[LotNo],

[lotno_j],

[LotNo_P],

[lotno_v],

[Mix],

[OneExPer],

[PacketNo_j],

[PacketNo_P],

[PacketNo_v],

[PARTYTOFINAL],

[PolishCertWt],

[PolishNonCerWt],

[PolishWeight_P],

[PolishWeight1_j],

[PolishWeight1_v],

[PolishWeight2_j],

[PolishWeight2_P],

[PolishWeight2_v],

[PolishWeight3_j],

[PolishWeight3_P],

[PolishWeight3_v],

[PolishWeight4_j],

[PolishWeight4_v],

[PolishWeight5_j],

[PolishWeight5_P],

[PolishWeight5_v],

[PolishWeight6_P],

[RapPrice1_j],

[RapPrice1_v],

[RapPrice2_j],

[RapPrice2_v],

[RapPrice3_j],

[RapPrice3_v],

[Rate1_j],

[Rate1_v],

[Rate1Police_j],

[Rate2_j],

[Rate2_v],

[Rate2Police_j],

[Rate3_j],

[Rate3_v],

[Rate3Police_j],

[Rate7_j],

[Rate7_v],

[Rate8_j],

[Rate8_v],

[Rate9_P],

[Rate10_P],

[RoughRetWt],

[RoughWeight_j],

[RoughWeight_v],

[sarintotal],

[Shape_j],

[Shape_v],

[ShapeID_j],

[ShapeID_v],

[Size1_j],

[Size1_v],

[ThreeExPer],

[TotalJwelex],

[Totalpolise],

[TotalPolish],

[TotalVendor],

[TotalVendor_f],

[UploadType_j],

[UploadType_v],

[Value1_j],

[Value1_j_Polish],

[Value1_v],

[Value2_j],

[Value2_j_Polish],

[Value2_v],

[Value3_j],

[Value3_j_Polish],

[Value3_v],

[Value7_j],

[Value7_v],

[Value8_j],

[Value8_v],

[Value9_P],

[Value10_P],

[vendortotal],

[VG],

[VGXX];

SQL EXECUTE [dbCertifiedInventory_Live].[dbo].[usp_EPCM_Get_MultipleSearch]

@LotID = '262',

@FromDate = '',

@ToDate = '',

@JewlexRapPriceDate = '01-Apr-2014',

@GIARapPriceDate = '29-Sep-2014';

If Alt(NoOfRows('usp_EPCM_Get_MultipleSearch'), 0) > 0 Then

Store usp_EPCM_Get_MultipleSearch into ...\usp_EPCM_Get_MultipleSearch.Qvd;

End If

Thanks

Re: Store procedure with parameters

Peter_Cammaert — Fri, 17 Oct 2014 11:08:48 GMT

Could be access rights-related...

Is the user/password combination in the QlikView CONNECT string the same as the one you used in your SQL tests (that were producing 85 rows)?

Re: Store procedure with parameters

sona_sa — Fri, 17 Oct 2014 11:13:07 GMT

yes oledb connection are good and test connection are fine