Calling SQL server stored procedure from datastage.

saraswati · Post by **saraswati** » Wed Sep 21, 2011 1:31 pm

Can someone tell me how to call a SQL server stored procedure by passing parameters from datastage ?

vinothkumar · Post by **vinothkumar** » Wed Sep 21, 2011 2:55 pm

Have you tried with StoredProcedure stage.

saraswati · Post by **saraswati** » Thu Sep 22, 2011 9:01 am

No.....I have not tried Stored Procedure stage.

I don't want to return any values from the stored procedure.

Can I use ODBC stage? If yes,then pls let me know how?

I want to call a SQL server Stored procedure by passing some input parameters

saraswati · Post by **saraswati** » Fri Sep 23, 2011 1:17 pm

can anyone help me on it? I need this to get this done urgently.

chulett · Post by **chulett** » Fri Sep 23, 2011 1:49 pm

saraswati wrote:I need this to get this done urgently.

Then you should be working with your official support provider.

Using the Stored Procedure stage doesn't mean you are forced to return values, you're not so don't think that's why you couldn't use it. However, I doubt it will support SQL Server from a UNIX server.

For ODBC, put whatever 'normal' syntax there you'd need for SQL Server and then bind any input columns in that you need as parameters using the normal ? positional bind variables. For Oracle the 'sql' you'd need owuld be an anonymous block but for yours I have no clue. However, I'm sure someone where you work could help with that part if no-one here chimes in.

saraswati · Post by **saraswati** » Fri Sep 23, 2011 2:47 pm

Can I go for putting the below query into the User Defined SQL part in ODBC stage ?

EXECUTE PROC_NAME ( PARAMETER1,PARAMETER2)

If yes then what I need to mention in the columns tab. Can I use a dummy column ? How basically the approach should be for designing this job?

ray.wurlod · Post by **ray.wurlod** » Fri Sep 23, 2011 4:01 pm

If EXECUTE doesn't work, try CALL.

chulett · Post by **chulett** » Fri Sep 23, 2011 4:08 pm

AFAIK, it should be:

EXECUTE PROC_NAME(?,?)

And the question marks would match up to your first and second columns in the tab - assuming you are passing in parameter values from the link. If they are 'hard-coded' just put the values in the call and whatever columns you'd like in the stage as they will be ignored.

And as Ray noted, if it doesn't like EXECUTE, try CALL.

saraswati · Post by **saraswati** » Sat Sep 24, 2011 11:39 pm

How can the parameter passed through the job and the column name in the ODBC stage be the same?

If possible,can you pls provide some more details?

chulett · Post by **chulett** » Sun Sep 25, 2011 8:59 am

First off, stick to posting questions in your thread, there's no need to send private emails as well.

There is no name concern here. As noted, the parameter markers are positional here so the first ? is automagically matched to the value in the first column, the second to the second, etc.

saraswati · Post by **saraswati** » Sun Sep 25, 2011 1:07 pm

Then...how will the columns are the ODBC stage be handled?

Pls let me know the job details which you think this will work.

chulett · Post by **chulett** » Sun Sep 25, 2011 1:40 pm

I've already provided those details, twice now.

You however, have not. You have yet to specify where the parameters you need to pass into the proc need to come from. That would go a long ways towards wrapping this up.

saraswati · Post by **saraswati** » Mon Sep 26, 2011 8:56 am

The parameters to the stored proc will be passed through the job.

So as you say....I need to put EXECUTE PROC_NAME( param1,param) in the User-defined SQL portion in the ODBC stage.

My doubt is what will the column names what I need to mention in the ODBC column stage and if I connect the output of the ODBC into a transformer then how will the columns be handled?

Can I do it in this way?

ODBC ----> Peek stage

Let me know if you have some different approach in getting this done.

chulett · Post by **chulett** » Mon Sep 26, 2011 9:31 am

And what does "through the job" mean? If that means as job parameters, then you are getting closer though you may need to wrap them in hash signs and whatever columns you include in the stage will be ignored. If that means as data on the input link, then you need to refer back to the post with the EXECUTE PROC_NAME(?,?) discussion.

saraswati · Post by **saraswati** » Mon Sep 26, 2011 12:54 pm

Tell me one thing....The ODBC stage must be used as a source and not as a target.

If it is acting as a source then we can set the read method as User-Defined SQL and put the EXECUTE on the query portion.

I had put the ODBC as source and the output link from it is connected to a peek stage.On the ODBC stage,I had put the EXECUTE PROC(param1,param2) on the query part after setting read method as User-Defined SQL.

Let me know if this should work out fine or do you have some other approach?