Re: scheduling a t-sql stored procedures name in table column without creating a job for each one??
- From: "jobs" <jobs@xxxxxxxxxx>
- Date: 17 Jan 2007 15:21:01 -0800
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: SSIS: Help with importing text file of data into a table
- 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
|
