DSXchange

Hi,
I have a need to execute a MS SQL 2005 stored procedure from a datastage PX job and pass 4 parameters to this stored procedure via job parameters.

I created a new PX job and added one "Stored Procedure" Stage to it and set it up as "Transform" type.

I was able to configure the procedure name, connection details and all the required settings. However when i try to set the parameters, it prompts that the column name should match an existing column.

I tried importing the procedure into the repository and loaded into the Stored Procedure stage, but it still needs the column name to be existing in the job.

To satisfy this, I added a Row Generator stage that would supply dummy values and it worked well. Then i converted the Row Generator to generate only one record and set the columns to take the values from the job parameters.

I saw another post viewtopic.php?t=117033 where a "No Param" stored procedure was discussed and there were suggestions to use a transformer or DRS.

Are there any do's and don'ts in these approaches?

If i can execute the procedure without any additional stages, that would be best, but it appears that for most cases, a dummy transformer would be required to get things moving.

Thanks,

VCInDSX wrote:If i can execute the procedure without any additional stages, that would be best

You can't in a job. Something must drive it.

VCInDSX,
It's columns mapping of your STP stage's Maps to Column is wrong.
One of the enhanced features for 8.0.1 is that finally you can use STP stage against MS SQL Server.

Craig,
Thanks for the followup and your time.
Is there any overhead in using the Row Generator to route the param values to the job or should i look into using transformer for such purposes?


lstsaur,
Thanks for your time. I am afraid i am not following your feedback point(s) especially line 1 of your response.
It's columns mapping of your STP stage's Maps to Column is wrong.

You are correct, 8.0.1 has support for SQL Server Stored procedures. I am using the Stored Proecdure stage and have selected the DBMS type as SQL Server.

Thanks,

Craig,
Thanks for the followup and your time.
Is there any overhead in using the Row Generator to route the param values to the job or should i look into using transformer for such purposes?


lstsaur,
Thanks for your time. I am afraid i am not following your feedback point(s) especially line 1 of your response.
It's columns mapping of your STP stage's Maps to Column is wrong.

You are correct, 8.0.1 has support for SQL Server Stored procedures. I am using the Stored Proecdure stage and have selected the DBMS type as SQL Server.

Thanks,

I would think that, in a PX job, a Row Generator would be the most appropriate starting point. With no equivalent in Server to that stage, we use a Transformer. However, you could use either I would imagine. [shrug]

The warning message, the column name should match an existing column, tells that your Maps to Columns mapping of the Parameters tab is incorrect. Read STP pdf for detailed example how to map your parameters to existing columns.

As craig opined, it is not possible to have the Stored Procedure Stage on a job canvas by itself. For now, I will leave the Row Generator linked to the STP.

As for the matching column name, the Row Generator or some sort of input link needs to supply the column name that can be mapped to the Stored Procedure's parameter name. In my case, Row Generator is serving the purpose.

If there is any other way to do this, I will be willing to try that out.

Many Thanks,