|
|
You're in the
sql server dts
group:scheduling a t-sql stored procedures name in table column without creating a job for each one??
sql server dts:
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?
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! [quoted text, click to view] 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@webdos.com> wrote in message > news:1169068840.747517.53220@l53g2000cwa.googlegroups.com: > > > 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?
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 [quoted text, click to view] "jobs" <jobs@webdos.com> wrote in message
news:1169068840.747517.53220@l53g2000cwa.googlegroups.com: > 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?
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 [quoted text, click to view] "jobs" <jobs@webdos.com> wrote in message
news:1169076061.147456.54260@m58g2000cwm.googlegroups.com: > 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@webdos.com> wrote in message > > news:1169068840.747517.53220@l53g2000cwa.googlegroups.com: > > > > > 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?
[quoted text, click to view] Allan Mitchell wrote: > You want to schedule or simply run/put them on a Q and they run? Yes. So from an SQL command, how can schedule or Queue a stored procedure? and will it return any information I can use to see if it's still runnning in another query? The idea being that if the SP takes an hour, the the SQL command will come right back after kicking it off..
Don't see what you're looking for? Try a search.
|
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
| home · blog · groups | Questions? Comments? Contact the dn |