Tsql script to create backup jobs

[itmontyp]

I am trying to create a stored procedure to create a set of backup jobs 4 me. the idea is to pass the name of the new database to the stored procedure, which in turn will call 4 other procedures.

that is a long way off yet. as i cant quite get the first part to work, i have the feeling once i do i will be able to complete the rest.

the initial SP is listed below


CREATE PROC BUCREATE
@dbname varchar(40)
AS
DECLARE @job_name sysname,
@enabled tinyint,
@description nvarchar(512),
@category_name sysname,
@owner_login_name sysname,
@notify_level_eventlog int,
@notify_level_email int,
@notify_email_operator_name sysname,
@delete_level int,
@job_id uniqueidentifier


set @job_name = 'BU ' + @dbname + ' Backup'
set @enabled =convert(int,1)
set @description = 'BU ' + @dbname + 'Daily Backup'
set @category_name = 'Database Maintenance'
set @owner_login_name = 'sa'
set @notify_level_eventlog =convert(int, 2)
set @notify_level_email = convert(int,2)
set @notify_email_operator_name ='sql alerts alias'
set @delete_level =0

-- Run system stored procedure to add the job, passing the variables
exec sp_add_job
@job_name,
@enabled,
@description,
@category_name,
@owner_login_name,
@notify_level_eventlog,
@notify_level_email,
@notify_email_operator_name,
@delete_level,
@job_id OUTPUT

--Print out the Information Entered
print 'Job Name : ' + @job_name
print 'Enabled : ' + convert(varchar(20),@enabled)
print 'Description : ' +@description
print 'Category : ' + @category_name
print 'Owner Login : ' + @owner_login_name
print 'Event Log Notify Level : ' + convert(varchar(20),@notify_level_eventlog)
print 'Email Operator Name : ' + @notify_email_operator_name
print 'Delete level : ' + convert(varchar(20),@delete_level)


the print statements are for debugging only

when i run the SP i get the following output


Server: Msg 8114, Level 16, State 1, Procedure sp_add_job, Line 0
Error converting data type nvarchar to int.
Job Name : BU help Backup
Enabled : 1
Description : BU helpDaily Backup
Category : Database Maintenance
Owner Login : sa
Event Log Notify Level : 2
Email Operator Name : sql alerts alias
Delete level : 0


it is the line

Error converting data type nvarchar to int.

that is confusing me

any ideas ???



ITmontyp

So Long and thanks for all the fish

 

[JFoushee]

I think it's going to be because you gave sp_add_job all those parameters, but it doesn't know what is what.

For instance, I think the error is caused when it plugs your value @owner_login_name for the optional @categoryid param in sp_add_job.

So there are two ways of doing this...

#1:Build the statement, explicitly pairing parameters with values

DECLARE @SQLStmt varchar(8000)
SELECT @SQLStmt = 'exec sp_add_job @job_name=''' + @job_name + ''', @enabled=' +  @enabled + ','...
EXEC (@SQLStmt)

#2: Place NULL in the implied order sp_add_job expects

exec sp_add_job 
@job_name,
@enabled,
@description,
@category_name,
NULL,              -- <- for @categoryid
@owner_login_name,
@notify_level_eventlog,

(I think there are other values missing other than just @category_id)

Hope this helps.