Re: scheduling a t-sql stored procedures name in table column without creating a job for each one??
- From: "Allan Mitchell" <allan@xxxxxxxxxxxxxxxxxx>
- Date: Wed, 17 Jan 2007 23:32:30 +0000
You want to schedule or simply run/put them on a Q and they run?
Again I would still connect to SQL Server however it is that you want to do it, get all the possible procs from your Control table. Loop through them and fire them.
The stored procs can log to a table where they are in the process. You as part of the monitoring can poll this table to see what is going on
To decide whether to execute the Oracle AND/OR SQL Server routines you can use Control Flow.
--
Allan Mitchell
http://wiki.sqlis.com | http://www.sqlis.com | http://www.sqldts.com |
http://www.konesans.com
"jobs" <jobs@xxxxxxxxxx> wrote in message news:1169076061.147456.54260@xxxxxxxxxxxxxxxxxxxxxxxxxxxx:
Thank you for that.
sorry, noob at heart here.
Imports System.Data.SqlClient
Imports System.Data.OracleClient
Imports System.Data.OleDb
and code like this:
dr = cmdProcess.ExecuteReader()
I guess I meant oledb.
I need to launch both t-sql stored procedures and plsql from this one
control process. The pl-sql job launch is and tracking is working from
the script task. A lot has been invested in the code already and it
would be scary to re-engineer that given everhting it's doing.
To simplify the question, I want to write a stored procedure that will
schedule a t-sql stored procedure passed to it as varchar, but I don't
want to create job or stored procedure for every procedure that will be
scheduled. And by schedule I mean run as a job now. How?
By tracking, I mean some job number or something is returned that I can
store that will later tell me if the job/process is still running,
failed or finished successfully.
I've been able to do the above with oracle from the ssis script and
scheduled job - and I thought that would have been the complicated
piece.
Thanks again!
Allan Mitchell wrote:
> For a start I do not think you want the ADO.NET provider as this, whilst
> being flexible, is slow. You also do not need to use a Script task to do
> this. The OLE DB provider would be better.
>
> You could using either an ExecuteSQL task or a Data Flow Task and read
> into a variable (rowset) the Stored Procs you need to execute. You then
> loop over them and execute them using a combination of the ForEachLoop
> container and and ExecuteSQL task.
>
> Tracking a stored proc?
>
>
> You can fire the package either through the object model, on the cmdline
> using DTExec or yes firing a job.
>
>
>
> --
>
>
> Allan Mitchell
> http://wiki.sqlis.com | http://www.sqlis.com | http://www.sqldts.com |
> http://www.konesans.com
>
>
>
>
> "jobs" <jobs@xxxxxxxxxx> wrote in message
> news:1169068840.747517.53220@xxxxxxxxxxxxxxxxxxxxxxxxxxxx:
>
> > I have an ssis script task written in vb.net that will connect to a sql
> >
> > server database via ado.net and can execute sql commands.
> >
> > A control table will hold a list of stored procedures and an action
> > column that will indicate an sp is ready to run.
> >
> >
> > what sql command can I use to schedule and track a stored procedure?
> >
> >
> > Ideally, I only want to create a single job or function to manage all
> > the stored procedures.
> >
> >
> > The asp.net pages will come right back, as there only db related
> > function will be to set the flag. The scheduled ssis script task
> > package will handle lauchning the stored procedure and recording its
> > job number and will also continue letting the sp run as it's own job.
> >
> >
> > Whats the best way to accomplish this? A job that runs a variable sp?
> >
> > possible? How?
> >
> >
> > In Oracle (from ado.net), I'm able to do this via a call to a function
> > that calls DBMS_JOB.SUBMIT and returns a job number. Tables like
> > all_jobs return information of the job for tracking the job. What's
> > comparable in sql server 2005?
.
- Follow-Ups:
- References:
- Prev by Date: Re: scheduling a t-sql stored procedures name in table column without creating a job for each one??
- Next by Date: Re: scheduling a t-sql stored procedures name in table column without creating a job for each one??
- Previous by thread: Re: scheduling a t-sql stored procedures name in table column without creating a job for each one??
- Next by thread: Re: scheduling a t-sql stored procedures name in table column without creating a job for each one??
- Index(es):
Relevant Pages
|
