DSXchange

Hi All,

I am trying to execute a Sql-Server Stored procedure from Datasage parallel job and my job is like this:-

RowGen(1 row) ---->Stored Procedure stage ----> peek stage

I have imported the metadata from TableDefinition--> Stored Procedure Def.

I have selected below setting in StoredProcedure Stage:-

General Tab:
-------------------------------
Database Vendor: MySQL Server

Syntex Tab:
-------------------------
Procedure Type: Transform
Procedure Name: dbo.UP_IN_AMBIT_PRICE;
Procedure call Syntex: EXEC dbo.UP_IN_AMBIT_PRICE;

Parameter Tab:
------------------
I removed Return Value from parameter name since in output link I checked Procedure status to link.

Input Tab:
-----------------
Unchecked to Forward Raw data and Execute procedure for each row.

Output Tab:
--------------
Checked procedure status to link and procedure allow multiple rows.

Now when I am executing the job I am getting below error message:
Can anyone help me to trace the issue. I don't know where exactly I am doing wrong

Thanks
Prasoon

Hi,

I executed the PROC on sql server and its giving output:

USE[IMJID]
GO

DECLARE @return_value int

EXEC [odb].[UP_IN_AMBIT_PR]

SELECT 'Return Value' = @return_value

GO

In this case which stored procedure type will be appropriate (source/Transform).

Thanks
PRasoon

The type is very straight-forward:

If data only comes out: Source
If data only goes in: Target
If data goes in and out: Transform

Ok,
In my case data is going out so Source Stored Procedure will work.

Now i am struggling with my first issue,with error like

"al: Wrong number or types of arguments in call to dbo.UP_IN_AMBIT_PRICE"

Here we don't have any argument passing to stored procedure and yes we are getting a return value which i suppose to get in output column as ProCode.

Kindly need your help.

Thanks
PRasoon

prasson_ibm wrote:In my case data is going out so Source Stored Procedure will work.

For that to be true you'd need to get rid of the Row Generator.

Ok I have removed row regenerator.

Now my design is :

Stored Procedure Stage -----> Peek Stage.

Still I am getting same error as I have mentioned before.

What could be the reason for this issue.

Thanks
Prasoon

Take a good look at the UP_IN_AMBIT_PRICE stored procedure. What exactly does it do? From the name, I'd expect it to have an input argument and an output argument but, without seeing its actual SQL, cannot be totally certain.

Can you try this, and see if it helps?
On the Syntax tab of your stored procedure stage
a) Uncheck Generate prodecure call
b) Edit the Procedure call syntax so it looks like this

Hi All,

I am able to resolve the issue. I was getting error because there were two columns with same name and while importing the metadata in job, one of the column was not importing.

I raised this concern to associated team and they renamed one of the column.