T-SQL job creation and scheduling

T-SQL job creation and scheduling

Post by chri » Fri, 22 Feb 2002 13:02:13



Hi all,

I am totally lost on how to implement this correctly.

I have writted 2 SPROCS that will calculate some data from tables and insert
values into other tables used for charting.

I want to be create jobs that use these SPROCS and schedule the jobs to run
once a day.  I have to do it via T-SQL since my DB changes have to be run
against a copy of the live production DB without losing any data.

so basically I want to create a job that logic wise is my SPROC and then
schedule that job to run once per day...is that clear?  All using T-SQL.

I am unsure of how to carry this out, and am especially confused by the fact
that I have to 'USE msdb' in order to use the sp_add_job and
sp_schedule_job, but then how do I specify which database and SPROCS I want
the jobs to actually run?

Thanks in advance for any quick help....please reply or email if you have
questions...

Chris

 
 
 

T-SQL job creation and scheduling

Post by Greg Jone » Fri, 22 Feb 2002 14:08:47


Chris,

Enterprise Manager|Server|Management|Jobs
Create
In Jobs, right click, select 'NEW' job.

Establish Job Action
Go to 'STEPS' tab and add 'NEW' step.  Select Transact-SQL
as 'TYPE'.  In the command line, type exec
<<dbname>>..<<sproc>>

Establish Schedule
Go to Schedules and select 'New Schedule'.  
Select 'Recurring' option and set to 'Daily'

Hope this helps....

Quote:>-----Original Message-----
>Hi all,

>I am totally lost on how to implement this correctly.

>I have writted 2 SPROCS that will calculate some data

from tables and insert
Quote:>values into other tables used for charting.

>I want to be create jobs that use these SPROCS and

schedule the jobs to run
Quote:>once a day.  I have to do it via T-SQL since my DB

changes have to be run
Quote:>against a copy of the live production DB without losing
any data.

>so basically I want to create a job that logic wise is my
SPROC and then
>schedule that job to run once per day...is that clear?  
All using T-SQL.

>I am unsure of how to carry this out, and am especially

confused by the fact
Quote:>that I have to 'USE msdb' in order to use the sp_add_job
and
>sp_schedule_job, but then how do I specify which database
and SPROCS I want
>the jobs to actually run?

>Thanks in advance for any quick help....please reply or
email if you have
>questions...

>Chris

>.


 
 
 

T-SQL job creation and scheduling

Post by chri » Fri, 22 Feb 2002 14:41:08


Greg,

Thanks..

but this job has to be created and scheduled via T-SQL as well....basically
I have to write it into my update script for the database so all someone has
to do is run my update.sql script....it can't require a person to physically
go in and create the jobs and schedule them via the user interfaces or tools
in enterprise manager, etc....is that more clear?

Chris

Quote:> Chris,

> Enterprise Manager|Server|Management|Jobs
> Create
> In Jobs, right click, select 'NEW' job.

> Establish Job Action
> Go to 'STEPS' tab and add 'NEW' step.  Select Transact-SQL
> as 'TYPE'.  In the command line, type exec
> <<dbname>>..<<sproc>>

> Establish Schedule
> Go to Schedules and select 'New Schedule'.
> Select 'Recurring' option and set to 'Daily'

> Hope this helps....

> >-----Original Message-----
> >Hi all,

> >I am totally lost on how to implement this correctly.

> >I have writted 2 SPROCS that will calculate some data
> from tables and insert
> >values into other tables used for charting.

> >I want to be create jobs that use these SPROCS and
> schedule the jobs to run
> >once a day.  I have to do it via T-SQL since my DB
> changes have to be run
> >against a copy of the live production DB without losing
> any data.

> >so basically I want to create a job that logic wise is my
> SPROC and then
> >schedule that job to run once per day...is that clear?
> All using T-SQL.

> >I am unsure of how to carry this out, and am especially
> confused by the fact
> >that I have to 'USE msdb' in order to use the sp_add_job
> and
> >sp_schedule_job, but then how do I specify which database
> and SPROCS I want
> >the jobs to actually run?

> >Thanks in advance for any quick help....please reply or
> email if you have
> >questions...

> >Chris

> >.

 
 
 

T-SQL job creation and scheduling

Post by Andrew Joh » Fri, 22 Feb 2002 18:42:19


Chris,

In SQL2000 create the job using EnterpriseManager as normal.
Then <right><click> on the job and under 'All Task's select 'Generate
script'
The resulting code can be edited to suit target server.

This is a darn site easier than trying to write code from
scratch using system stored procs sp_add_job, sp_add_jobschedule ...

Regards
    AJ


> Greg,

> Thanks..

> but this job has to be created and scheduled via T-SQL as
well....basically
> I have to write it into my update script for the database so all someone
has
> to do is run my update.sql script....it can't require a person to
physically
> go in and create the jobs and schedule them via the user interfaces or
tools
> in enterprise manager, etc....is that more clear?

> Chris


> > Chris,

> > Enterprise Manager|Server|Management|Jobs
> > Create
> > In Jobs, right click, select 'NEW' job.

> > Establish Job Action
> > Go to 'STEPS' tab and add 'NEW' step.  Select Transact-SQL
> > as 'TYPE'.  In the command line, type exec
> > <<dbname>>..<<sproc>>

> > Establish Schedule
> > Go to Schedules and select 'New Schedule'.
> > Select 'Recurring' option and set to 'Daily'

> > Hope this helps....

> > >-----Original Message-----
> > >Hi all,

> > >I am totally lost on how to implement this correctly.

> > >I have writted 2 SPROCS that will calculate some data
> > from tables and insert
> > >values into other tables used for charting.

> > >I want to be create jobs that use these SPROCS and
> > schedule the jobs to run
> > >once a day.  I have to do it via T-SQL since my DB
> > changes have to be run
> > >against a copy of the live production DB without losing
> > any data.

> > >so basically I want to create a job that logic wise is my
> > SPROC and then
> > >schedule that job to run once per day...is that clear?
> > All using T-SQL.

> > >I am unsure of how to carry this out, and am especially
> > confused by the fact
> > >that I have to 'USE msdb' in order to use the sp_add_job
> > and
> > >sp_schedule_job, but then how do I specify which database
> > and SPROCS I want
> > >the jobs to actually run?

> > >Thanks in advance for any quick help....please reply or
> > email if you have
> > >questions...

> > >Chris

> > >.

 
 
 

T-SQL job creation and scheduling

Post by chri » Sat, 23 Feb 2002 04:36:21


ahh...ok...I'll try that...thanks...

Chris

> Chris,

> In SQL2000 create the job using EnterpriseManager as normal.
> Then <right><click> on the job and under 'All Task's select 'Generate
> script'
> The resulting code can be edited to suit target server.

> This is a darn site easier than trying to write code from
> scratch using system stored procs sp_add_job, sp_add_jobschedule ...

> Regards
>     AJ



> > Greg,

> > Thanks..

> > but this job has to be created and scheduled via T-SQL as
> well....basically
> > I have to write it into my update script for the database so all someone
> has
> > to do is run my update.sql script....it can't require a person to
> physically
> > go in and create the jobs and schedule them via the user interfaces or
> tools
> > in enterprise manager, etc....is that more clear?

> > Chris


> > > Chris,

> > > Enterprise Manager|Server|Management|Jobs
> > > Create
> > > In Jobs, right click, select 'NEW' job.

> > > Establish Job Action
> > > Go to 'STEPS' tab and add 'NEW' step.  Select Transact-SQL
> > > as 'TYPE'.  In the command line, type exec
> > > <<dbname>>..<<sproc>>

> > > Establish Schedule
> > > Go to Schedules and select 'New Schedule'.
> > > Select 'Recurring' option and set to 'Daily'

> > > Hope this helps....

> > > >-----Original Message-----
> > > >Hi all,

> > > >I am totally lost on how to implement this correctly.

> > > >I have writted 2 SPROCS that will calculate some data
> > > from tables and insert
> > > >values into other tables used for charting.

> > > >I want to be create jobs that use these SPROCS and
> > > schedule the jobs to run
> > > >once a day.  I have to do it via T-SQL since my DB
> > > changes have to be run
> > > >against a copy of the live production DB without losing
> > > any data.

> > > >so basically I want to create a job that logic wise is my
> > > SPROC and then
> > > >schedule that job to run once per day...is that clear?
> > > All using T-SQL.

> > > >I am unsure of how to carry this out, and am especially
> > > confused by the fact
> > > >that I have to 'USE msdb' in order to use the sp_add_job
> > > and
> > > >sp_schedule_job, but then how do I specify which database
> > > and SPROCS I want
> > > >the jobs to actually run?

> > > >Thanks in advance for any quick help....please reply or
> > > email if you have
> > > >questions...

> > > >Chris

> > > >.

 
 
 

1. TSQL question: How to find a scheduled job by name

Hi,
I am trying to write a stored procedure which can search for a job by
name and return a true or false if it is or is not found.



The simplest solution I know is to grant select access to
msdb.dbo.sysjobs but I don't want to do that.
Is there any other way to search for a job?
---
Thanks,
Sunil

Sent via Deja.com http://www.deja.com/
Before you buy.

2. Oracle DBA with financials

3. Return SP/TSQL output to scheduled job

4. Good Books on Sybase ??

5. scheduled task / job / creating one with tsql code

6. snapshots take more and more time

7. Problem w/ SQL Agent scheduled jobs [concurrent jobs executing 1 sec apart]

8. Publishing Data on the Web

9. Log Output From A Scheduled TSQL Task (SQL 6.5)

10. scheduled job disables the schedule everyday

11. Scheduled recurring backup jobs getting schedule disabled after last run of day

12. TSQL Dynamic Cursors Creation

13. DB Creation using TSQL