Okay since there is not a lot of DQS content here, I will try to generate some discussion with various problems I am encountering as I try to stumble my way through a complex DQS plan. My latest block is when I try to execute a SQL Server stored procedure.

It takes 4 input parameters and returns two output parameters. Using Debug, I get a valid result with the SQL Execute Step configured thusly:

DECLARE @RC int
EXECUTE @RC = dbo.Validation_1
${NM_FIRST}
,${NM_MIDDLE}
,${NM_LAST}
,${DT_BIRTH}
,$+{Out1}
,$+{Out2}

However, when I run my plan with this step it fails with an error stating:

...unable to map output parameters.

The stack trace shows root cause is:

java.sql.SQLException: Query returns result set, but result set is not expected.

If I try to run this stored procedure using a SQL Select step it fails with:

... Mappings are defined but query returns no result data

These are two mutually exclusive errors leading me to doubt the ability for DQS to execute this particular stored procedure. The built-in Help page suggests using a procedure-call scheme, but I get a syntax error when I try to use "call dbo.Validation_1" or "@RC = call dbo.Validation_1". Has anyone had any luck calling a SQL Server stored procedure from DQS? Any alternatives?

This message has been edited. Last edited by: FP Mod Chuck,

Hi CanMan,
iWay DQS recommends "SQL Select step" to parameterize a SQL stored procedure using the data columns from the flow of data. Тhe data columns should be defined in the Columns tab in the SQL Select step.

I did the following to "sort of" replicate your issue.

1. Created a stored procedure

ALTER procedure [dbo].[Validation_1]
@NM_FIRST Varchar(100),
@NM_MIDDLE Varchar(100),
@NM_LAST Varchar(100),
@DT_BIRTH Varchar(100),
@OUT1 varchar(100) OUTPUT,
@OUT2 varchar(100) OUTPUT
AS

SET @Out1 = '1'
SET @Out2 = '2'

SELECT @Out1 as "OUT1",@Out2 as "OUT2"
GO

2. Created a DQS plan with SQL select step and mapped output parameters ( I can't paste screenshot here so I copied the XML mappings here)

columnMapping dataColumn="OUT1" sqlColumn="OUT1"
columnMapping dataColumn="OUT2" sqlColumn="OUT2"


exec dbo.Validation_1
${NM_FIRST}
,${NM_MIDDLE}
,${NM_LAST}
,${DT_BIRTH}
,$+{OUT1}
,$+{OUT2}


Hope this helps. if you want the sample plans I can email it.

Thanks,
Jikku

This message has been edited. Last edited by: Jikku.Jacob,

WebFOCUS 8
Windows, All Outputs

Jikku,

Thank you for the response! I had previously tried using the SQL Select step but it only resulted in the error:

...failed: Mappings are defined but query returns no result data[STEP SQL Select[SQL Select]]

That is when I tried using the SQL Execute, which does work when I use Debug, but fails when I execute the plan that includes that step. Very curious behavior that I don't understand.

I did duplicate your successful results using your stored procedure definition, so I suspect a problem with the fairly large, complex definition I am trying to execute. I will ask the developer of the stored procedure to investigate.

After extensive debugging of the stored procedure I was trying to execute. I determined that DQS throws a "Mappings are defined but query returns no result data[STEP SQL Select[SQL Select]]" exception when the stored procedure issues a Create Table statement.

The particular procedure I am trying to execute creates and drops temp tables as it runs through its logic.
Not sure why code inside the Stored Procedure causes a DQS exception. I expected the stored procedure to work as a black box as far as DQS is concerned.