Sp to create SQL job

  • After my failed attempt last time, I thought of a new way to get my scheduled backups in place.

    I have basically created an SP and put it in model, this creates the folder structure for the backups, deletes old backups then takes a full backup of the database. To automate this, I am created another SP, which I will run, feeding in the database name of which I Want it to schedule backups for.

    create procedure dbo.CreateBackupAgentJob @DatabasenameInput varchar(100) = null as

    declare @JobCode varchar(max)

    declare @thisserver nvarchar(100)

    select @thisserver = (select @@SERVERNAME)

    set @JobCode =

    'begin transaction

    DECLARE @jobId BINARY(16)

    EXEC msdb.dbo.sp_add_job @job_name=N''' + @DatabasenameInput + '- backup Job'',

    @enabled=1,

    @notify_level_eventlog=0,

    @notify_level_email=2,

    @notify_level_netsend=2,

    @notify_level_page=2,

    @delete_level=0,

    @category_name=N''[Uncategorized (Local)]'',

    @owner_login_name=N''sa'', @job_id = @jobId OUTPUT

    select @jobId

    GO

    EXEC msdb.dbo.sp_add_jobserver @job_name=N''' + @DatabasenameInput + '- backup Job'', @server_name = @thisserver

    GO

    USE [msdb]

    GO

    EXEC msdb.dbo.sp_add_jobstep @job_name=N''' + @DatabasenameInput + '- backup Job'', @step_name=N''' + @DatabasenameInput + ' Backup'',

    @step_id=1,

    @cmdexec_success_code=0,

    @on_success_action=1,

    @on_fail_action=2,

    @retry_attempts=0,

    @retry_interval=0,

    @os_run_priority=0, @subsystem=N''TSQL'',

    @command=N''exec dbo.backupdatabase'',

    @database_name=@databasenameinput,

    @flags=0

    GO

    USE [msdb]

    GO

    EXEC msdb.dbo.sp_update_job @job_name=N''' + @DatabasenameInput + '- backup Job'',

    @enabled=1,

    @start_step_id=1,

    @notify_level_eventlog=0,

    @notify_level_email=2,

    @notify_level_netsend=2,

    @notify_level_page=2,

    @delete_level=0,

    @description=N'',

    @category_name=N''[Uncategorized (Local)]'',

    @owner_login_name=N''sa'',

    @notify_email_operator_name=N'',

    @notify_netsend_operator_name=N'',

    @notify_page_operator_name=N''

    GO

    USE [msdb]

    GO

    DECLARE @schedule_id int

    EXEC msdb.dbo.sp_add_jobschedule @job_name=N''' + @DatabasenameInput + 'backup Job'', @name=N''10pm''',

    @enabled=1,

    @freq_type=4,

    @freq_interval=1,

    @freq_subday_type=1,

    @freq_subday_interval=0,

    @freq_relative_interval=0,

    @freq_recurrence_factor=1,

    @active_start_date=20130815,

    @active_end_date=99991231,

    @active_start_time=220000,

    @active_end_time=235959, @schedule_id = @schedule_id OUTPUT

    select @schedule_id

    GO )

    exec @jobcode

    I am getting a syntax error on the highlighted row? please help 😀

  • I have also just realised I pasted this in the wrong forum (should be in 2008)

  • Hi Steve,

    I think you've got one too many ' after 10pm, you'll need to remove that and put one in after the last GO.

    Not sure where the closing ) following that GO has it's opening?

    And @thisserver in the EXEC msdb.dbo.sp_add_jobserver line needs to be concatenated in I think.

    You also have a begin transaction without a matching commit/rollback - and note transactions don't work across GO commands. In fact, I'm not sure that it'll run - GO is a SMSS/sqlcmd command that marks separate T-SQL batches, it's not a T-SQL command in itself.

    You can probably get away with removing them or replacing with ;

    Hope that helps!

  • I don't see any highlighting?

    But I do see a syntax error. This is your stored procedure:

    create procedure dbo.CreateBackupAgentJob @DatabasenameInput varchar(100) = null as

    declare @JobCode varchar(max)

    declare @thisserver nvarchar(100)

    select @thisserver = (select @@SERVERNAME)

    set @JobCode =

    'begin transaction

    DECLARE @jobId BINARY(16)

    EXEC msdb.dbo.sp_add_job @job_name=N''' + @DatabasenameInput + '- backup Job'',

    @enabled=1,

    @notify_level_eventlog=0,

    @notify_level_email=2,

    @notify_level_netsend=2,

    @notify_level_page=2,

    @delete_level=0,

    @category_name=N''[Uncategorized (Local)]'',

    @owner_login_name=N''sa'', @job_id = @jobId OUTPUT

    select @jobId

    There is no ' to close the string.

    Yes, yes, you intended the GO that follows to be part of the SQL string, but SSMS will intercept the GO right there and pass text before the GO as a batch.

    You need something like this:

    DECLARE @go varchar(6) = char(13) + char(10) + 'go' + char(13) + char(10)

    which you use in this way:

    select @jobId' +

    @go +

    'EXEC msdb.dbo.sp_add_jobserver @job_name=N''' + @DatabasenameInput + '- backup Job'', @server_name = @thisserver' +

    @go +

    'USE [msdb]

    But didn't I tell you that you should use quotestring and quotename to avoid issues with SQL injection. What if there is a database with the name mydb' SHUTDOWN WITH NOWAIT --?

    [font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

  • Cheers guys.

    Erland, your right. I should of stayed with your origional suggestion but the quotes were driving me nuts. However, Im going to persist.

    How do I get aroudn the quotes using quotestring? Seems like an odd question to ask.

    alter procedure [dbo].[CreateBackupProc] @DatabaseNameInput nvarchar(200) = null

    as

    declare @jobname nvarchar(100)

    declare @setDatabasename varchar(100)

    declare @deletebackupscmd varchar(200)

    declare @backupdatabaseCMD varchar(300)

    select @setDatabasename = dbo.quotestring(@DatabaseNameInput)

    SELECT @JobName = dbo.quotestring(@DatabaseNameInput) + ' - daily backup'

    select @backupdatabaseCMD = 'Exec dbo.BackupDatabase'

    DECLARE @jobId BINARY(16)

    EXEC msdb.dbo.sp_add_job @job_name= @jobname,

    @enabled=1,

    @notify_level_eventlog=0,

    @notify_level_email=2,

    @notify_level_netsend=2,

    @notify_level_page=2,

    @delete_level=0,

    @category_name=N'[Uncategorized (Local)]',

    @owner_login_name=N'sa', @job_id = @jobId OUTPUT

    select @jobId

    EXEC msdb.dbo.sp_add_jobserver @job_name= @Jobname , @server_name =N'(local)'

    EXEC msdb.dbo.sp_add_jobstep @job_name= @jobname, @step_name=N'Backup Database',

    @step_id=1,

    @cmdexec_success_code=0,

    @on_success_action=1,

    @on_fail_action=2,

    @retry_attempts=0,

    @retry_interval=0,

    @os_run_priority=0, @subsystem=N'TSQL',

    @command=@BackupDatabaseCMD,

    @database_name=@setDatabasename,

    @flags=0

    EXEC msdb.dbo.sp_update_job @job_name= @jobname,

    @enabled=1,

    @start_step_id=1,

    @notify_level_eventlog=0,

    @notify_level_email=2,

    @notify_level_netsend=2,

    @notify_level_page=2,

    @delete_level=0,

    @description=N'',

    @category_name=N'[Uncategorized (Local)]',

    @owner_login_name=N'sa',

    @notify_email_operator_name=N'',

    @notify_netsend_operator_name=N'',

    @notify_page_operator_name=N''

    DECLARE @schedule_id int

    EXEC msdb.dbo.sp_add_jobschedule @job_name = @Jobname, @name=N'Daily 2am',

    @enabled=1,

    @freq_type=4,

    @freq_interval=1,

    @freq_subday_type=1,

    @freq_subday_interval=0,

    @freq_relative_interval=0,

    @freq_recurrence_factor=1,

    @active_start_date=20130807,

    @active_end_date=99991231,

    @active_start_time=20000,

    @active_end_time=235959, @schedule_id = @schedule_id OUTPUT

    select @schedule_id

    Everything is working fine now, the job is being created with the steps i need etc. But obviously the SP is trying to find a database called ''Steve'' as ive used Steve as the database to backup (From my input). But as its in quotestring.

    If i just specify the database Steve.. it works so this is the last piece to my prolonged agony!

    Really appreciate your help with this

  • Erland.. in addition, I the reason i am doing it this way, is due to what you said about the time\date stamp in my origional plan.. it would have been the day of job creation every time the database was backed up, not the day of the backup.. so thanks for pointing that out!

  • But now you have changed the stored procedure, so there is no dynamic SQL, and you should have to use quotestring. What I had in mind was:

    EXEC msdb.dbo.sp_add_job @job_name=N''' + @DatabasenameInput + '- backup Job'',

    This should have been:

    EXEC msdb.dbo.sp_add_job @job_name=N' + dbo.quotestring(@DatabasenameInput + '- backup Job') + ',

    Note that it is the full job name that should be put in quotes.

    On the other hand, now you have:

    SELECT @JobName = dbo.quotestring(@DatabaseNameInput) + ' - daily backup'

    EXEC msdb.dbo.sp_add_job @job_name= @jobname,

    So if the name of the database is [font="Courier New"]Gurka[/font], the name of the backup job becomes [font="Courier New"]'Gurka'- backup Job[/font]. That is, the quotes become part of the name.

    [font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

  • Erland,

    Even with the below, I get syntax errors it still doesnt run through. It doesn't like the dash

    EXEC msdb.dbo.sp_add_job @job_name=N' + dbo.quotestring(@DatabasenameInput + '- backup Job') + ',

    am i approaching this in the wrong way?

  • EXEC msdb.dbo.sp_add_job @job_name=N' + dbo.quotestring(' + @DatabasenameInput + '- backup Job') + ',

    try this... but what is "quotestring".

    Regards
    Durai Nagarajan

  • durai nagarajan (8/19/2013)


    EXEC msdb.dbo.sp_add_job @job_name=N' + dbo.quotestring(' + @DatabasenameInput + '- backup Job') + ',

    try this... but what is "quotestring".

    It still doesnt like the initial + in the brackets.

  • Apply the closing parenthesis in the '

    Regards
    Durai Nagarajan

  • WHat i currently have is

    Variables table -

    Stored prod 1 - dbo.backup database - creates folder structure, backups up database, deletes old backups

    stored proc 2 - input the database name when calling it, and it creates a job to run Stored proc 1 on the sepcified database

    its just the job which isnt being created right.

    all files attached

  • durai nagarajan (8/19/2013)


    Apply the closing parenthesis in the '

    Already tried mate still didnt like it.

    dbo.quote string info - http://www.sommarskog.se/dynamic_sql.html < search that page - ive attached this script

  • SQLSteve (8/19/2013)


    Erland,

    Even with the below, I get syntax errors it still doesnt run through. It doesn't like the dash

    EXEC msdb.dbo.sp_add_job

    @job_name=N' + dbo.quotestring(@DatabasenameInput + '- backup Job') + ',

    In difference to many other languages, T-SQL does not accept expressions for actual parameter values; you can only pass variables and constants.

    Thus, you would need

    SELECT @jobname = N' + dbo.quotestring(@DatabasenameInput + '- backup Job') + '

    EXEC msdb.dbo.sp_add_job @job_name = @jobname

    However, that still does not make sense, because written in tokens you now have:

    SELECT @jobname = Unicode_string - identifier identifier string

    That is, from a lexical point of view,

    SELECT @jobname = N' + dbo.quotestring(@DatabasenameInput + '- backup Job') + '

    is the same as

    SELECT @jobname = N'ABC'- backup Job 'XYZ'

    You seem to make things unnecessarily difficult for yourself. What you need is probably

    SELECT @jobname = @DatabasenameInput + '- backup Job'

    All assuming that you stay away from dynamic SQL (which definitely is above your level of confusion, to be honest).

    To be honest, I don't know what the rules for job names are, but as long as you stick to static SQL, there is no risk for SQL injection.

    [font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

  • Your right i do over complicate things.

    I tried using the @job_name = @jobname before but to no avail when I was create an SP inside an SP, but with this approach, It has worked!

    Thanks for your help with that! got there eventually 😉

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply