SQL Server – Creating SQL Agent Job Using Dynamic SQL

dynamic-sqlsql server

I'm currently putting together Agent Jobs for Database backups, using the Ola Hallengren scripts. I've put together a script that creates an Agent Job to perform FULL backups and it's working perfectly.

Now, I want to make the script Dynamic. Why? Each environment will have a different Certificate for encrypting the backups and the Cleanup times may vary.

This is what I have thus far;

DECLARE @SQLCertificate NVARCHAR(50)
SET @SQLCertificate = N'00132'

EXECUTE ('
IF NOT EXISTS (SELECT [Name] FROM [dbo].[sysjobs] WHERE [Name] = N''Backups_Encrypted_FULL'')
BEGIN
    EXECUTE sp_add_job
        @job_name = N''Backups_Encrypted_FULL'',
        @enabled = 1,
        @notify_level_eventlog=2, 
        @notify_level_email=0, 
        @notify_level_netsend=0, 
        @notify_level_page=0, 
        @delete_level=0, 
        @owner_login_name = N''DBA_Maintenance'',
        @description = N''Performs a FULL backup of all User Databases'',
        @category_name = N''[Database Backups]''

    EXECUTE sp_add_jobstep  
        @step_id=1, 
        @job_name = N''Backups_Encrypted_FULL'',
        @step_name = N''Execute T-SQL'',
        @subsystem = N''TSQL'',
        @command = N''EXECUTE [dbo].[DatabaseBackup]
    @Databases = N''USER_DATABASES'',
    @Directory = N''C:\SQLBackup'',
    @DirectoryStructure = N''{DatabaseName}'',
    @FileName = ''{DatabaseName}_{Description}_{Partial}_{CopyOnly}_{Year}{Month}{Day}_{Hour}{Minute}{Second}_{FileNumber}.{FileExtension}'',
    @BackupType = ''FULL'',
    @Description = N''Full_Backup'',
    @CheckSum = ''Y'',
    @Verify = ''Y'',
    @Compress = ''Y'',
    @CleanupTime = ''0'',
    @CleanupMode = ''AFTER_BACKUP'',
    @Encrypt = ''Y'',
    @EncryptionAlgorithm = ''AES_256'',
    @ServerCertificate = '''+ @SQLCertificate +''',
    @LogToTable = ''Y'''',
        @database_name = N''DBA.Maintenance''

    EXECUTE sp_add_jobschedule
        @job_name = N''Backups_Encrypted_FULL'',
        @name = N''Saturday Night'',
        @enabled = 1,
        @freq_type = 8,
        @freq_interval = 64,
        @freq_subday_interval=0, 
        @freq_relative_interval=0, 
        @freq_recurrence_factor=1, 
        @active_end_date=99991231, 
        @active_start_time=220000, 
        @active_end_time=235959

    EXECUTE sp_add_jobserver
        @job_name = N''Backups_Encrypted_FULL'',
        @server_name = N''(Local)''
END ')

GO

Running it however, gives the following error;

Msg 102, Level 15, State 1, Line 22
Incorrect syntax near 'USER_DATABASES'.

Anyone have any thoughts on this?

Best Answer

The first problem is that you are enclosing escaped string delimiters inside an already-escaped string:

----------------vv this opens a string
    @command = N''EXECUTE [dbo].[DatabaseBackup]
@Databases = N''USER_DATABASES'', 
--------------^^ this tries to close it

This is similar to the following:

EXEC sys.sp_executesql N'PRINT ''foo''bar'';';

Which yields:

Msg 102, Level 15, State 1
Incorrect syntax near 'bar'.
Msg 105, Level 15, State 1
Unclosed quotation mark after the character string ';'.

To get around it, you need to double up the second layer of single quotes again.

EXEC sys.sp_executesql N'PRINT ''foo''''bar'';';

Which prints:

foo'bar

I don't think I have to tell you that putting four, eight, sixteen, and so on gets really ugly, really quickly.

A better solution

In the comment above, Dan was off on the reason for the error, but absolutely right on the question of why dynamic SQL in the first place. It's simply not necessary here - you can just use a local variable for the command, and concatenate or replace the certificate name.

DECLARE @SQLCertificate nvarchar(50) = N'00132';

DECLARE @cmd nvarchar(max) = N'EXECUTE [dbo].[DatabaseBackup]
          @Databases = N''USER_DATABASES'',
          @Directory = N''C:\SQLBackup'',
          @DirectoryStructure = N''{DatabaseName}'',
          @FileName = ''{DatabaseName}_{Description}_{Partial}_{CopyOnly}_{Year}{Month}{Day}_{Hour}{Minute}{Second}_{FileNumber}.{FileExtension}'',
          @BackupType = ''FULL'',
          @Description = N''Full_Backup'',
          @CheckSum = ''Y'',
          @Verify = ''Y'',
          @Compress = ''Y'',
          @CleanupTime = ''0'',
          @CleanupMode = ''AFTER_BACKUP'',
          @Encrypt = ''Y'',
          @EncryptionAlgorithm = ''AES_256'',
          @ServerCertificate = ''$cert$'',
          @LogToTable = ''Y'';';

SET @cmd = REPLACE(@cmd, N'$cert$', @SQLCertificate);

IF NOT EXISTS (SELECT 1 FROM [dbo].[sysjobs] WHERE [Name] = N'Backups_Encrypted_FULL')
BEGIN
    EXEC dbo.sp_add_job
        @job_name = N'Backups_Encrypted_FULL',
        @enabled = 1,
        @notify_level_eventlog = 2, 
        @notify_level_email = 0, 
        @notify_level_netsend = 0, 
        @notify_level_page = 0, 
        @delete_level = 0, 
        @owner_login_name = N'DBA_Maintenance',
        @description = N'Performs a FULL backup of all User Databases',
        @category_name = N'[Database Backups]';

    EXEC dbo.sp_add_jobstep  
        @step_id = 1, 
        @job_name = N'Backups_Encrypted_FULL',
        @step_name = N'Execute T-SQL',
        @subsystem = N'TSQL',
        @command = @cmd,
        @database_name = N'DBA.Maintenance';

    EXEC dbo.sp_add_jobschedule
        @job_name = N'Backups_Encrypted_FULL',
        @name = N'Saturday Night',
        @enabled = 1,
        @freq_type = 8,
        @freq_interval = 64,
        @freq_subday_interval = 0, 
        @freq_relative_interval = 0, 
        @freq_recurrence_factor = 1, 
        @active_end_date = 99991231, 
        @active_start_time = 220000, 
        @active_end_time = 235959;

    EXEC dbo.sp_add_jobserver
        @job_name = N'Backups_Encrypted_FULL',
        @server_name = N'(Local)';
END
Related Question