Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  iWay Software Product Forum on Focal Point    [SOLVED] DQS SQL Execute/Query against SQL Server Stored Procedure
Go
New
Search
Notify
Tools
Reply
  
[SOLVED] DQS SQL Execute/Query against SQL Server Stored Procedure
 Login/Join
 
Member
posted
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,


iWay Service Manager 7.07
iWay Data Quality Server
 
Posts: 11 | Registered: June 22, 2017Reply With QuoteReport This Post
Member
posted Hide Post
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
 
Posts: 6 | Registered: August 05, 2015Reply With QuoteReport This Post
Member
posted Hide Post
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.


iWay Service Manager 7.07
iWay Data Quality Server
 
Posts: 11 | Registered: June 22, 2017Reply With QuoteReport This Post
Member
posted Hide Post
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.


iWay Service Manager 7.07
iWay Data Quality Server
 
Posts: 11 | Registered: June 22, 2017Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  iWay Software Product Forum on Focal Point    [SOLVED] DQS SQL Execute/Query against SQL Server Stored Procedure

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.