Scheduling a job using a stored procedure through a Stored Procedure

I currently new to SQL server and have been assigned a project to develop an auction site as part of my course. I would like to create a stored procedure which schedules a job to modify the 'auction state' field in a table to 'active' once the auction start time is reached and also run a scheduled job to close the auction at the end time.

I was thinking about using a stored procedure which calls on the sp_add_job but you have to use the msdb for this and you cannot use the 'USE' keyword inside a stored produre to call this. Am I going the wrong way about this or is it possible?EXECUTE msdb.dbo.sp_add_job-PatP|||The code snippet below has completed successfully, but i there seems to be a problem with it, can you use a local variable as a value for job name like below?

exec msdb.dbo.sp_add_job @.job_name = @.AuctionID
exec msdb.dbo.sp_add_jobstep @.job_name = @.AuctionID,
@.stepname = 'Step1',
@.command = 'Update Auction
SET AuctionState = ''closed''
WHERE AuctionID = @.AuctionID'

thanks for the earlier response.|||Can anyone see a problem with the code below, STEP1 doesn't get included when the job is scheduled. Suspected errors highlighted in red. The errors haven't been added cos they are confusing.
_______________________________________________
INSERT INTO Auction
(...

)

VALUES
(
...)

SELECT
@.AuctionID = @.@.Identity
declare @.Aid varchar
set @.Aid = convert(varchar,@.auctionID,15)
Declare @.sqlcommand varchar(255)
Set @.sqlcommand = 'UPDATE Auction SET AuctionState = ''closed'' WHERE AuctionID = ' + @.AID

exec msdb.dbo.sp_add_job @.job_name = @.AUCTIONID
exec msdb.dbo.sp_add_jobstep @.job_name = '@.AUCTIONID',
@.step_name = 'Step1',
@.command = @.sqlcommand,
@.database_name = 'Auction',
@.server = 'XX'
exec msdb.dbo.sp_add_jobschedule @.job_name = @.AuctionID,
@.name = 'AuctionsScheduled',
@.freq_type = 1,
@.active_start_time ='120000',
@.active_start_date = '20040703',
@.freq_recurrence_factor = 1
exec msdb.dbo.sp_add_jobserver @.job_name = @.auctionID,
@.server_name = 'XX'

_____________________________________________

schedule backup job

Hi,
I have assigned a backupoperation role to a user and try to
schedule a backup job with this user. But it won't run at all and there is a
warning message in application event log as following:
"SQL Server Scheduled Job 'HRISPROD backup Tues'
(0x23C034EB3B83734487481D0F623D3309) - Status: Failed - Invoked on:
2004-05-18 08:35:01 - Message: The job failed. Unable to determine if the
owner (csdbackup) of job HRISPROD backup Tues has server access (reason:
Unable to connect to server - check SQL Server and SQL Server Agent
errorlogs). "
But I can do the backup manually with this login. Please help. Many thanks.
Hi,
Can you restart your SQL Server and SQL Agent service and try executing the
job again.
If the service is not getting started then , reissue the password in the
control - panel -- services -- Log on option and start the service. I feel
that there is some issues in the service.
Thanks
Hari
MCDBA
"00Allen Iverson" <no_spam@.bk.com> wrote in message
news:#qMQDkJPEHA.904@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I have assigned a backupoperation role to a user and try to
> schedule a backup job with this user. But it won't run at all and there is
a
> warning message in application event log as following:
> "SQL Server Scheduled Job 'HRISPROD backup Tues'
> (0x23C034EB3B83734487481D0F623D3309) - Status: Failed - Invoked on:
> 2004-05-18 08:35:01 - Message: The job failed. Unable to determine if the
> owner (csdbackup) of job HRISPROD backup Tues has server access (reason:
> Unable to connect to server - check SQL Server and SQL Server Agent
> errorlogs). "
>
> But I can do the backup manually with this login. Please help. Many
thanks.
>

schedule backup job

Hi,
I have assigned a backupoperation role to a user and try to
schedule a backup job with this user. But it won't run at all and there is a
warning message in application event log as following:
"SQL Server Scheduled Job 'HRISPROD backup Tues'
(0x23C034EB3B83734487481D0F623D3309) - Status: Failed - Invoked on:
2004-05-18 08:35:01 - Message: The job failed. Unable to determine if the
owner (csdbackup) of job HRISPROD backup Tues has server access (reason:
Unable to connect to server - check SQL Server and SQL Server Agent
errorlogs). "
But I can do the backup manually with this login. Please help. Many thanks.Hi,
Can you restart your SQL Server and SQL Agent service and try executing the
job again.
If the service is not getting started then , reissue the password in the
control - panel -- services -- Log on option and start the service. I feel
that there is some issues in the service.
Thanks
Hari
MCDBA
"00Allen Iverson" <no_spam@.bk.com> wrote in message
news:#qMQDkJPEHA.904@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I have assigned a backupoperation role to a user and try to
> schedule a backup job with this user. But it won't run at all and there is
a
> warning message in application event log as following:
> "SQL Server Scheduled Job 'HRISPROD backup Tues'
> (0x23C034EB3B83734487481D0F623D3309) - Status: Failed - Invoked on:
> 2004-05-18 08:35:01 - Message: The job failed. Unable to determine if the
> owner (csdbackup) of job HRISPROD backup Tues has server access (reason:
> Unable to connect to server - check SQL Server and SQL Server Agent
> errorlogs). "
>
> But I can do the backup manually with this login. Please help. Many
thanks.
>

schedule backup job

Hi,
I have assigned a backupoperation role to a user and try to
schedule a backup job with this user. But it won't run at all and there is a
warning message in application event log as following:
"SQL Server Scheduled Job 'HRISPROD backup Tues'
(0x23C034EB3B83734487481D0F623D3309) - Status: Failed - Invoked on:
2004-05-18 08:35:01 - Message: The job failed. Unable to determine if the
owner (csdbackup) of job HRISPROD backup Tues has server access (reason:
Unable to connect to server - check SQL Server and SQL Server Agent
errorlogs). "
But I can do the backup manually with this login. Please help. Many thanks.Hi,
Can you restart your SQL Server and SQL Agent service and try executing the
job again.
If the service is not getting started then , reissue the password in the
control - panel -- services -- Log on option and start the service. I feel
that there is some issues in the service.
Thanks
Hari
MCDBA
"00Allen Iverson" <no_spam@.bk.com> wrote in message
news:#qMQDkJPEHA.904@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I have assigned a backupoperation role to a user and try to
> schedule a backup job with this user. But it won't run at all and there is
a
> warning message in application event log as following:
> "SQL Server Scheduled Job 'HRISPROD backup Tues'
> (0x23C034EB3B83734487481D0F623D3309) - Status: Failed - Invoked on:
> 2004-05-18 08:35:01 - Message: The job failed. Unable to determine if the
> owner (csdbackup) of job HRISPROD backup Tues has server access (reason:
> Unable to connect to server - check SQL Server and SQL Server Agent
> errorlogs). "
>
> But I can do the backup manually with this login. Please help. Many
thanks.
>