Calling SQL server stored procedure from datastage.
Moderators: chulett, rschirm, roy
Calling SQL server stored procedure from datastage.
Can someone tell me how to call a SQL server stored procedure by passing parameters from datastage ?
-
- Participant
- Posts: 342
- Joined: Tue Nov 04, 2008 10:38 am
- Location: Chennai, India
Then you should be working with your official support provider.saraswati wrote:I need this to get this done urgently.
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.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
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.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
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.
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.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
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.
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.
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.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
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?
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?