need help to turn a scheduled job into a stored procedure

[rebeccah2004]

hello. i am a little new to sqlserver and its differences from oracle but i need assistance on a stored procedure. i created a nightly job in sqlserver agent, and generated it as a script. i took that script and tried to make it a stored procedure. it doesn't seem to be working, and what i don't know is if i am missing syntax in the beginning of the sp, or if this just can't be done. when i test it in query analyzer, i get "Stored Procedure: CAMTEST.dbo.sp_BkupCam
Return Code = 0"
but it does not backup the db or execute any of the steps. the script is below. any thoughts would be greatly appreciated!!
_____________________________________
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GO

/****** Object: Stored Procedure dbo.sp_BkupCam Script Date: 01.27.04 11:33:43 ******/
ALTER PROCEDURE [dbo].[sp_BkupCam] AS

-- Script generated on 01.27.04 11:19 AM
-- By: sa
-- Server: CAMRANY1

BEGIN TRANSACTION
DECLARE @JobID BINARY(16)
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
IF (SELECT COUNT(*) FROM msdb.dbo.syscategories WHERE name = N'Database Maintenance') < 1
EXECUTE msdb.dbo.sp_add_category @name = N'Database Maintenance'

-- Delete the job with the same name (if it exists)
SELECT @JobID = job_id
FROM msdb.dbo.sysjobs
WHERE (name = N'BkupCam')
IF (@JobID IS NOT NULL)
BEGIN
-- Check if the job is a multi-server job
IF (EXISTS (SELECT *
FROM msdb.dbo.sysjobservers
WHERE (job_id = @JobID) AND (server_id <> 0)))
BEGIN
-- There is, so abort the script
RAISERROR (N'Unable to import job ''BkupCam'' since there is already a multi-server job with this name.', 16, 1)
GOTO QuitWithRollback
END
ELSE
-- Delete the [local] job
EXECUTE msdb.dbo.sp_delete_job @job_name = N'BkupCam'
SELECT @JobID = NULL
END

BEGIN

-- Add the job
EXECUTE @ReturnCode = msdb.dbo.sp_add_job @job_id = @JobID OUTPUT , @job_name = N'BkupCam', @owner_login_name = N'sa', @description = N'No description available.', @category_name = N'Database Maintenance', @enabled = 1, @notify_level_email = 0, @notify_level_page = 0, @notify_level_netsend = 0, @notify_level_eventlog = 2, @delete_level= 0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

-- Add the job steps
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id = 1, @step_name = N'bkupdb', @command = N'BACKUP DATABASE camtest TO DISK = &quot;\\camrany1\d$\backups\camdata\beckey\camtest.dmp&quot;', @database_name = N'master', @server = N'', @database_user_name = N'', @subsystem = N'TSQL', @cmdexec_success_code = 0, @flags = 0, @retry_attempts = 0, @retry_interval = 1, @output_file_name = N'', @on_success_step_id = 0, @on_success_action = 3, @on_fail_step_id = 0, @on_fail_action = 2
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id = 2, @step_name = N'zipitup', @command = N'gzip \\camrany1\d$\backups\camdata\beckey\camtest.dmp', @database_name = N'', @server = N'', @database_user_name = N'', @subsystem = N'CmdExec', @cmdexec_success_code = 0, @flags = 0, @retry_attempts = 0, @retry_interval = 1, @output_file_name = N'', @on_success_step_id = 0, @on_success_action = 3, @on_fail_step_id = 0, @on_fail_action = 2
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id = 3, @step_name = N'nightlybatch', @command = N'beck.bat >> d:\backups\camdata\beckey\itworked.log', @database_name = N'', @server = N'', @database_user_name = N'', @subsystem = N'CmdExec', @cmdexec_success_code = 0, @flags = 0, @retry_attempts = 0, @retry_interval = 1, @output_file_name = N'', @on_success_step_id = 0, @on_success_action = 1, @on_fail_step_id = 0, @on_fail_action = 2
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXECUTE @ReturnCode = msdb.dbo.sp_update_job @job_id = @JobID, @start_step_id = 1

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

-- Add the Target Servers
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @JobID, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:



GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
________________________________

regards,
rebecca

 

[Corran007]

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

CREATE PROCEDURE dbo.runbackup AS

BACKUP DATABASE camtest TO DISK = '\\camrany1\d$\backups\camdata\beckey\camtest.dmp'

Exec xp_cmdshell 'gzip \\camrany1\d$\backups\camdata\beckey\camtest.dmp'

exec xp_cmdshell 'beck.bat >> d:\backups\camdata\beckey\itworked.log'
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

Try something like that. you may have to work with the cmdshell a bit to get it right

 

[JayKusch]

Hmmmm .... would be interested in knowing the reasoning behind this feat ... but maybe later.

OK ... when you put this Job creation into a store procedure and executed it, it created the stored procedure ... sp_BkupCam.

When you execute the stored procedure sp_BkupCam ... it only created the job. It DID NOT run the job.

If you want to run the job, you need to either set a daily time for the job to execute within the job creation OR you need to start the job manually or by executing the following command ...

EXEC sp_start_job @job_name = 'BkupCam'



Thanks

J. Kusch

 

[rebeccah2004]

wow, thank you both for the quick response. basically, we are trying to implement this software that is not very user friendly. we are setting up a nightly.bat to run nightly. the backup of the database on camrany1 must run first, then there are programs on another server, metaman02, that are specific to that server and must run there. my intentions were to be able to run the nightly.bat on metaman02, having the stored procedure run first, and if it fails the entire script fails. but, if the backup is successful, the rest of the nightly scripts run on metaman02. i hope i'm not trying to do the impossible. this is a crazy project i have here. any thoughts or ideas you have i would GREATLY appreciate.

thank you,
rebecca

 

[rebeccah2004]

also, when i run the stored procedure from a batch, it immediately returns 'Job 'BkupCam' started successfully.' so if i have other scripts to run after the backup finishes, it is too late. the scripts end up running before the bkup completes. is there a switch to make the other scripts wait til the bkup is complete?

thanks again,
rebecca

 

[SQLSister]

When you set up jobs you can do them in steps. and have the steps run on success or on failure. YOu really need to read up more on jobs in books online or some other source.

 

[rebeccah2004]

yeah i know that when you create jobs you can do them in steps AND that you can run them on success or failure. however. that is not my issue here.

 

[SQLSister]

Perhaps I suggested that because it seems a better solution than trying to run it as a stored procedure. I saw nothing in your explanation that precluded you doing this with a scheduled job run in steps.

 

[rebeccah2004]

yeah, sorry, i didn't include that i had tried that already. that is not going to work for what i am trying to accomplish here because if i include batch scripts in the stored procedure (or in scheduling a job, w/ steps from sqlserver agent) that batch will be run from the same server as my database is on. i need the batches to run on a different server (where the software is) than the database.

thanks for your input. i may or may not have something working. i am testing now.

regards,
rebecca

 

[JayKusch]

you still can fire off batch jobs on other servers. use the xp_cmdshell syntax as in ...

exec xp_cmdshell '\\ServerName\ShareName\MyBat.bat'

Thanks

J. Kusch

 

[rebeccah2004]

sorry for the delay, but thank you thank you everyone for your input! it was all helpful in different ways. i have figured out a way to sort this all out, and it seems to be working. i did find out that if i try to run a batch from the sp on a different machine other than the sqlserver, that it uses the sqlserver, rather than the other machine. i know you said do the exec xp_cmdshell, but it was diehard to use the sqlserver.

thanks again.

regards,
rebecca