Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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...
Hi Peter,
Thanks for reply...
Please let me know why it is storing in my defined path.
Error is :
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.
Try removing comma from the number, like:
@LotID varchar(MAX)='262,260',
@LotID varchar(MAX)='262260',
Is there any way to store the same SP information in QVD.
Hi tresesco,
Not done.
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
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.
Hi
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