Scheduling a job in SQL server 2

[CompCodeBoy]

I have an idea how to schedule a job in SQL server 2005. The job will be calling a stroed procedure with few parameters. The values for those parameters come from a table in SQL server and then should be passed to the stored procedure called from the job schedule. Do I need to write a script to do that? If yes, can I do that? an example would be great! Thanks in advance

 

[NoCoolHandle]

in the jobstep

Declare @firstParam int,@nextParam varchar(100)
Select @firstparam=SomeIntCol,@nextParam=SomeVarcharFiled
From WhatEverYourTableIsCalled
Where SomeCol=SomeVal
--Now call the proc
execute usp_SomeProc @firstParam,@nextParam etc....


HTH

Rob

 

[CompCodeBoy]

Thanks for the help. I believe taht should work. Thanks again!

 

[CompCodeBoy]

Hey Rob,

Is there a way I can schedule running the job dynamically (by passing parameters containing schedule time based on input I receive from the user)

 

[NoCoolHandle]

So.. if I am getting this right..
(choices here)

The question is can the results of a query can you run the task?
or do you mean
Can you programaticly set the next time a job will run..
or
Based on a query (execution of a proc or via a trigger) can you cause the job to run now?


Rob

 

[CompCodeBoy]

Programmatically, can you set the job to run at a specific time? Basically, the user will decide if he/she wants to schedule the job daily, weekly, etc... I'll get the user input through a windows application and this input will be saved in SQL. Now I need to be able to get this input from the SQL and then schedule a job based on the input. Hope this clarifies my question. Thanks.


Ray

 

[NoCoolHandle]

Sure... there are a number of stored procs that perform that exact task in the MSDB database.

To make it easy open up "SQL Profiler" and run a trace on the MSDB database. Then Add a schedule via the GUI (enterprise manager) then look at the Commands that were used to attach a schedule to a task..

You could also make changes directly to the tables, but I think you are safer using the same steps that the Built in tools make..


Bottom line.. it isn't that hard.. but you will definitly find that with profiler most of your code can be written for you.



Rob

 

[nice95gle]

I believe you are looking for sp_add_jobschedule

Well Done is better than well said
- Ben Franklin

 

[CompCodeBoy]

Thanks, guys. But where do I add the sp_add_jobschedule procedure so that it runs? I believe I have to do something like that:

EXEC sp_add_jobschedule @job_name = 'test',

@name = 'Schedule_test',
@freq_type = 4, -- daily
@freq_interval = 1,
@active_start_time = '11:00:00'

Where do I need to write the above in order to get run automatically. I guess I'm confused

Thanks.

 

[NoCoolHandle]

The stored proc already exists...
It resides in the MSDB database with all the tables that support jobs etc..

So...
to execute it from a different db..

exec msdb.dbo.sp_addjobschedule

or via an ado connection called con
con.execute "msdb.dbo.sp_addjobschedule param etc..

 

[CompCodeBoy]

Thanks, guys. Rob, you wrote

in the jobstep

Declare @firstParam int,@nextParam varchar(100)
Select @firstparam=SomeIntCol,@nextParam=SomeVarcharFiled
From WhatEverYourTableIsCalled
Where SomeCol=SomeVal
--Now call the proc
execute usp_SomeProc @firstParam,@nextParam etc....


works great. But what if "SomeVal" matches many records in the database. That is, I need to loop and execute the stored proc for the 1st record, 2nd record, etc... and then union the result. What is the best way of doing that?

 

[NoCoolHandle]

Ok... so if I get this right.. You want to loop through a table and execute the proc so that it does an insert into a temp table that can be returned at the end of execution..
(you can't UNION the resultsets of a proc together, that could be done with a udf .. but even then not dynamicly..)

So.. If i have the question right.. you have 2 choices..
1 build a cursor and run it that way.. (but I hate cursors

or use the non-cursor step through rows technique below.
e.g. (you should be able to run the script in a db and see it work)
The following technique does require you have a unique col there somewhere, as it uses the min(col) where col>variable technique.. which means it would skip duplicates.

set nocount on
go
Create Proc ups_GetTableNameAndID
@objectID int
as
Select Name,id from sysobjects where id=@objectid
go
Declare @t table (TableName varchar(100),id int)
declare @id int -- Used to retrieve the rows..
set @id=0 -- Must be initialized or the loop will fail to begin with
while not @id is null
	begin
		select @id = min(id) from sysobjects where type='u' and id>@id
		if @id is null
			begin
				break -- We have hit the end of the table so it is
					  -- time to exit the loop
			end
		insert into @t
		exec ups_GetTableNameAndID @id -- Perform an insert using a proc as the source
	end
select * from @t -- Return all the rows we have so far..
go
-- Clean up -- remove the proc we created a the start 
drop proc ups_GetTableNameAndID