Stored Procedures and Parameters
Posted: Fri Mar 04, 2005 9:28 am
This is a little lengthy but hopefully explains what I want to accomplish.
What I want to do:
I want to be able to call a sybase stored procedure and pass the result set to a file.
The stored procedure will have at least one parameter and the values for the parameters will be provided in a file. The file will have 100's of different values.
Basically I want to loop through these values one by one (many
procedure calls) and produce a file for each one. The filename will be the value in the file with .txt attached.
What I tried to do:
I created a new job server and placed an ODBC task and a
sequential file task onto the diagram editor.
I linked the two together from the ODBC task to the file.
On the Stage tab in ODBC I entered the relevant data source name information. (I initially tried using SYBASE_OC but its handling of stored procedures either is non-existant or not as easy to use as the ODBC task).
On the Outputs stage I selected the Stored Procedure option and imported the procedure that I wanted to run. In the Columns tab I specified the columns that match the result set coming back from the stored procedure.
In the Parameters tab I specfied the required details and supplied Values to be passed to the procedure.
On the Inputs tab on the sequential file I entered the required filename and on the columns tab entered the relevent details.
I then ran this and the data was written to the file as expected.
HOWEVER, I have a number of points regarding this.
(1) I want to be able to pass the parameters (from a file) to the procedure instead of having to hard-code them into the ODBC Output Tab, Parameter Tab. How can I do this ?
(2) The filename that is produced at the end of run time is one that I again hard-coded. I want this to be composed of the parameter value from the file + .txt. Again, is there a way that I can this ?
(3) I want this whole process to be written so that all values in the file are looped through one by one until they have all been processed. Again is this possible.
I've probably approached this in the wrong way so any advice would be welcome. I'm not sure if this can be done purely within a job server or if job sequences have to be used. This is a simple model that I need to set up before making things more complicated.
As always any help is appreciated.
What I want to do:
I want to be able to call a sybase stored procedure and pass the result set to a file.
The stored procedure will have at least one parameter and the values for the parameters will be provided in a file. The file will have 100's of different values.
Basically I want to loop through these values one by one (many
procedure calls) and produce a file for each one. The filename will be the value in the file with .txt attached.
What I tried to do:
I created a new job server and placed an ODBC task and a
sequential file task onto the diagram editor.
I linked the two together from the ODBC task to the file.
On the Stage tab in ODBC I entered the relevant data source name information. (I initially tried using SYBASE_OC but its handling of stored procedures either is non-existant or not as easy to use as the ODBC task).
On the Outputs stage I selected the Stored Procedure option and imported the procedure that I wanted to run. In the Columns tab I specified the columns that match the result set coming back from the stored procedure.
In the Parameters tab I specfied the required details and supplied Values to be passed to the procedure.
On the Inputs tab on the sequential file I entered the required filename and on the columns tab entered the relevent details.
I then ran this and the data was written to the file as expected.
HOWEVER, I have a number of points regarding this.
(1) I want to be able to pass the parameters (from a file) to the procedure instead of having to hard-code them into the ODBC Output Tab, Parameter Tab. How can I do this ?
(2) The filename that is produced at the end of run time is one that I again hard-coded. I want this to be composed of the parameter value from the file + .txt. Again, is there a way that I can this ?
(3) I want this whole process to be written so that all values in the file are looped through one by one until they have all been processed. Again is this possible.
I've probably approached this in the wrong way so any advice would be welcome. I'm not sure if this can be done purely within a job server or if job sequences have to be used. This is a simple model that I need to set up before making things more complicated.
As always any help is appreciated.