ODBC Stage and Stored Procs

[aaronej]

Can someone please help me with this? I am being told by Ascential support that calling a Stored Proc (SQLServer 2000) through an ODBC stage and returning data to a sequential file is NOT SUPPORTED. Can this possibly be true?

Support told me that the only way Stored Procs are supported is if you are passing data from a file into a parameter and then calling the proc with no return data.

I find it impossible to believe that we are the only ones using Stored Proc to help simplify or flatten out data prior to the ETL process.

Any help would be great!

Thanks!

Aaron

[kcbland]

If the stored procedure is meant to stream data (basically it's a suped up SQL statement) then you simply use it in an ODBC stage in replace of SQL. The ODBC stage should link to a transformer and then to a sequential file.

If the stored procedure is doing something in the remote database (dropping/creating indices/tables, etc), then you should use either a BATCH job or a command stage to invoke an isql session giving it the sql script to execute the stored procedure. This is a really good design practice.

No offense, either you didn't articulate the issue to support or support doesn't understand what you're doing. Think of DataStage as a hammer. You're calling technical support with questions about how to build a house. They can help you with how to hit a nail.

[trobinson]

I may be off-base here but, it sounds like Aaron is asking why you can't have a stored procedure be both an input and output at the same time. In other words, call a stored procedure with parameters passed from a sequential file stage (stream link) and have the output from the call passed down an output stream link. No can do-ski in DataStage! Either the stored procedure gets passed data or it returns data, never both at the same time. Bummer! Unless... (and I'm referring to Oracle here) you wrap the stored procedure in a function and call it from a look-up ODBC stage using User defined SQL to invoke the function wrapper. This will return A result but then if you've got multiple values, you've got to return them as a single field like an array.

[kcbland]

I find it impossible to believe that we are the only ones using Stored Proc to help simplify or flatten out data prior to the ETL process.

I hate having to parse a post when it doesn't clearly articulate the issue. I took this statemen to mean the Stored Procedure is being used in replace of a SQL statement in an ODBC stage.

If the poster wanted to use a Stored Procedure that itself has file writing mechanisms internalized, then my second point previously outlines a recommendation to fire off that command.

[aaronej]

OK, sorry for the confusion. All I really want to do it use an ODBC Stage to call a Stored Procedure which returns records and writes them to a sequential file for later processing.

Any help would be great. Again, sorry for any confusion.

Thanks!!!

Aaron

[rabs]

OK, sorry for the confusion. All I really want to do it use an ODBC Stage to call a Stored Procedure which returns records and writes them to a sequential file for later processing.

Any help would be great. Again, sorry for any confusion.

Thanks!!!

Aaron

I don't see any reason why this cannot be done, in fact I have done something similar, albeit using Oracle and PL/SQL stored procs. The problem I had was that my source was in another country, and using the OCI stage was too slow. So I called a stored proc which performed my extract and dumped the output into a flat file which lived on the db server. Then I ftp'ed that file to my ETL server and went from there. (The verification of success of the ftp command is another matter).

Is this something similar to what you want to do? That is have the stored procedure write out the flat file for you?

What error do you get when you try this?