Skip to main content

Uniface Integration: SQL Server Service Stored Procedures

  • November 19, 2018
  • 4 replies
  • 0 views
Jan Cees Boogaard

SQL Server Service Stored Procedures

Author: i.sharp@pcisystems.co.uk (Iain Sharp)

Hi, I want to define and use an SSP on $MSS, I want to pass in a single string, and get an entity parameter back. I can define the procedure as (effectively) create procedure get_stock_levels @table_out varchar(max) OUTPUT as set nocount on select * from stv_stock_levels and set up a signature with one table valued parameter. When run the table painted on the component is populated. I wish to amend the procedure to (effectively) alter procedure get_stock_levels @product_pattern varchar(50), @table_out varchar(max) OUTPUT as set nocount on select * from stv_stock_levels where prd_cd like @product_pattern When I amend the signature to have two parameters, a basic string in parameter with a literal name of either @product_pattern or product_pattern I get an error status of -150, and the message log shows "ICCDRV-MSS-ERR Call ICC component failed" Can anyone tell me what I'm doing wrong here? We need to do this so we can embed the selection parameter in the (complex) sql query which I have replaced with 'stv_stock_levels' above. Otherwise the sql server optimiser cannot switch to using index seeks and returns waaay too much data.

4 replies

Jan Cees Boogaard

SQL Server Service Stored Procedures

Author: i.sharp@pcisystems.co.uk (Iain Sharp)

Hi, I want to define and use an SSP on $MSS, I want to pass in a single string, and get an entity parameter back. I can define the procedure as (effectively) create procedure get_stock_levels @table_out varchar(max) OUTPUT as set nocount on select * from stv_stock_levels and set up a signature with one table valued parameter. When run the table painted on the component is populated. I wish to amend the procedure to (effectively) alter procedure get_stock_levels @product_pattern varchar(50), @table_out varchar(max) OUTPUT as set nocount on select * from stv_stock_levels where prd_cd like @product_pattern When I amend the signature to have two parameters, a basic string in parameter with a literal name of either @product_pattern or product_pattern I get an error status of -150, and the message log shows "ICCDRV-MSS-ERR Call ICC component failed" Can anyone tell me what I'm doing wrong here? We need to do this so we can embed the selection parameter in the (complex) sql query which I have replaced with 'stv_stock_levels' above. Otherwise the sql server optimiser cannot switch to using index seeks and returns waaay too much data.

I should add that I worked this out, something in the way the string was passed was preventing the query from returning any data, adding a set @product=rtrim(@product) cured this. The ICCDV error appears when the resultset is empty as well, which is a trifle unhelpful.


Author: Iain Sharp (i.sharp@pcisystems.co.uk)
Jan Cees Boogaard

SQL Server Service Stored Procedures

Author: i.sharp@pcisystems.co.uk (Iain Sharp)

Hi, I want to define and use an SSP on $MSS, I want to pass in a single string, and get an entity parameter back. I can define the procedure as (effectively) create procedure get_stock_levels @table_out varchar(max) OUTPUT as set nocount on select * from stv_stock_levels and set up a signature with one table valued parameter. When run the table painted on the component is populated. I wish to amend the procedure to (effectively) alter procedure get_stock_levels @product_pattern varchar(50), @table_out varchar(max) OUTPUT as set nocount on select * from stv_stock_levels where prd_cd like @product_pattern When I amend the signature to have two parameters, a basic string in parameter with a literal name of either @product_pattern or product_pattern I get an error status of -150, and the message log shows "ICCDRV-MSS-ERR Call ICC component failed" Can anyone tell me what I'm doing wrong here? We need to do this so we can embed the selection parameter in the (complex) sql query which I have replaced with 'stv_stock_levels' above. Otherwise the sql server optimiser cannot switch to using index seeks and returns waaay too much data.

And on to the next stored procedure. I am now getting the error ICCDRV-MSS-ERR Get parameter data from UNIFACE failed, Error - PRMUTYPE Which I am assuming means it is having an issue with the parameter type from uniface. I am using the same parameter types as the previous call (String in, entity out) mapped to the same parameter types within the stored procedure. I have checked the sp is returning the same columns as the view I am trying to replace, (in fact, I have altered the SP to just retrieve the data from the view, to check). I did think it was down to the SP returning a very large text field, which wouldn't actually need that much data, so I trimmed it to a VC200, this changed the error temporarily to one about connection already in use, so I found and set the mars:on setting in the MSS_PARAMS. Does anyone out there have either an idea what this error means, and how to diagnose it to a particular column or parameter in the SP definition or a better piece of documentation for the MSS SP driver than that included in the help for UNiface, which is extremely short on either worked examples or error definitions.


Author: Iain Sharp (i.sharp@pcisystems.co.uk)
Jan Cees Boogaard

SQL Server Service Stored Procedures

Author: i.sharp@pcisystems.co.uk (Iain Sharp)

Hi, I want to define and use an SSP on $MSS, I want to pass in a single string, and get an entity parameter back. I can define the procedure as (effectively) create procedure get_stock_levels @table_out varchar(max) OUTPUT as set nocount on select * from stv_stock_levels and set up a signature with one table valued parameter. When run the table painted on the component is populated. I wish to amend the procedure to (effectively) alter procedure get_stock_levels @product_pattern varchar(50), @table_out varchar(max) OUTPUT as set nocount on select * from stv_stock_levels where prd_cd like @product_pattern When I amend the signature to have two parameters, a basic string in parameter with a literal name of either @product_pattern or product_pattern I get an error status of -150, and the message log shows "ICCDRV-MSS-ERR Call ICC component failed" Can anyone tell me what I'm doing wrong here? We need to do this so we can embed the selection parameter in the (complex) sql query which I have replaced with 'stv_stock_levels' above. Otherwise the sql server optimiser cannot switch to using index seeks and returns waaay too much data.

In this particular case, the PRMUTYPE error meant' "Your stored procedure is returning two columns which are not defined in the model, please redefine the model or remove these columns." Not very informative though.


Author: Iain Sharp (i.sharp@pcisystems.co.uk)
Jan Cees Boogaard

SQL Server Service Stored Procedures

Author: i.sharp@pcisystems.co.uk (Iain Sharp)

Hi, I want to define and use an SSP on $MSS, I want to pass in a single string, and get an entity parameter back. I can define the procedure as (effectively) create procedure get_stock_levels @table_out varchar(max) OUTPUT as set nocount on select * from stv_stock_levels and set up a signature with one table valued parameter. When run the table painted on the component is populated. I wish to amend the procedure to (effectively) alter procedure get_stock_levels @product_pattern varchar(50), @table_out varchar(max) OUTPUT as set nocount on select * from stv_stock_levels where prd_cd like @product_pattern When I amend the signature to have two parameters, a basic string in parameter with a literal name of either @product_pattern or product_pattern I get an error status of -150, and the message log shows "ICCDRV-MSS-ERR Call ICC component failed" Can anyone tell me what I'm doing wrong here? We need to do this so we can embed the selection parameter in the (complex) sql query which I have replaced with 'stv_stock_levels' above. Otherwise the sql server optimiser cannot switch to using index seeks and returns waaay too much data.

We had a similar issue to this but it only occurred when we added additional fields to the stored procedure.  After adding fields to the model and compiling the signature we got "ICCDRV-MSS-ERR Get parameter data from UNIFACE failed, Error – PRMUTYPE". Turns out all we were missing is to select "Analyze Model" from the file menu in the model entity screen. So the process is:

  • Change the stored procedure
  • Add the field to the model
  • Analyze the model
  • Compile signature
  • Add field to form
  • Compile form

Author: RussellHarper (russell.harper@sanderson.com)

Recent badge winners

Explore all badges
Terms & ConditionsAccessibility statement