2

I am maintaining and publishing a DBA tools database using a Visual Studio 2017 Database Project.

Now I am trying to add a SQL Server agent job using a post deployment script.

The job is scripted out by SSMS and is parsing/executing just fine there. It is created with the option "Script (not in build)" and called by the post-deployment script (":r /path/file.sql"). Several other scripts are called like this and the build is working like that.

VS is parsing the script in the editor as well until I try to build. The build fails with:

72006: Fatal scripting error: Incorrect syntax was encountered while parsing '$(Escape_SQUOTE('.

The lines it fails on is:

@command=N'sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d MSSYS -Q "EXECUTE dbo.IndexOptimize @Databases = ''USER_DATABASES'', @FragmentationLow = NULL, @FragmentationMedium = NULL, @FragmentationHigh = NULL, @UpdateStatistics = ''ALL'', @OnlyModifiedStatistics = ''Y'', @MaxDOP = 8, @LogToTable = ''Y''" -b', 
@output_file_name=N'$(ESCAPE_SQUOTE(SQLLOGDIR))\StatisticUpdate_$(ESCAPE_SQUOTE(JOBID))_$(ESCAPE_SQUOTE(STEPID))_$(ESCAPE_SQUOTE(STRTDT))_$(ESCAPE_SQUOTE(STRTTM)).txt', 

So it tries to parse something as code that is actually supposed to be just a Unicode sting.

I tried to put both strings into a variable ending up with the same error in the SET statements.

The full code for your reference:

USE [msdb]
GO


BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0

IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'Database Maintenance' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'Database Maintenance'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'MSSYS StatisticUpdate - USER_DATABASES', 
        @enabled=1, 
        @notify_level_eventlog=2, 
        @notify_level_email=0, 
        @notify_level_netsend=0, 
        @notify_level_page=0, 
        @delete_level=0, 
        @description=N'Source: https://ola.hallengren.com', 
        @category_name=N'Database Maintenance', 
        @owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'StatisticUpdate - USER_DATABASES', 
        @step_id=1, 
        @cmdexec_success_code=0, 
        @on_success_action=1, 
        @on_success_step_id=0, 
        @on_fail_action=2, 
        @on_fail_step_id=0, 
        @retry_attempts=0, 
        @retry_interval=0, 
        @os_run_priority=0, @subsystem=N'CmdExec', 
        @command=N'sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d MSSYS -Q "EXECUTE dbo.IndexOptimize @Databases = ''USER_DATABASES'', @FragmentationLow = NULL, @FragmentationMedium = NULL, @FragmentationHigh = NULL, @UpdateStatistics = ''ALL'', @OnlyModifiedStatistics = ''Y'', @MaxDOP = 8, @LogToTable = ''Y''" -b', 
        @output_file_name=N'$(ESCAPE_SQUOTE(SQLLOGDIR))\StatisticUpdate_$(ESCAPE_SQUOTE(JOBID))_$(ESCAPE_SQUOTE(STEPID))_$(ESCAPE_SQUOTE(STRTDT))_$(ESCAPE_SQUOTE(STRTTM)).txt', 
        @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'MSSYS StatisticUpdate - USER_DATABASES', 
        @enabled=0, 
        @freq_type=8, 
        @freq_interval=62, 
        @freq_subday_type=1, 
        @freq_subday_interval=0, 
        @freq_relative_interval=0, 
        @freq_recurrence_factor=1, 
        @active_start_date=20171010, 
        @active_end_date=99991231, 
        @active_start_time=190000, 
        @active_end_time=235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO
1

I found a solution to this.

The post-deployment script of the Visual Studio database project is using sqlcmd to execute the tasks.

Before execution it parses the code and tries to replace variables. You can deactivate variable substitution by calling sqlcmd by using the flag -x. But as far as i could determine it is not possible to do something similar within sqlcmd when referencing a script using :r.

The T-SQL used to create SQL Server agent jobs is containing Tokens that are substituted during job run-time. For example $(ESCAPE_SQUOTE(TIME)) will be replaced with the current time at job execution.

Variables in sqlcmd syntax are called like this: $(variablename)

So the build process is correctly failing becaus a variable is called that is not declared properly.

This post did put me on the right track: https://dba.stackexchange.com/a/131304/114323

Declaring the variables in the post-deployment script and filling it with the tokens before referencing the script will fix it.

:SETVAR JOBID "$(ESCAPE_SQUOTE(JOBID))"
:SETVAR STEPID "$(ESCAPE_SQUOTE(STEPID))"
:SETVAR STRTDT "$(ESCAPE_SQUOTE(STRTDT))"
:SETVAR STRTTM "$(ESCAPE_SQUOTE(STRTTM))"
:SETVAR SRVR "$(ESCAPE_SQUOTE(SRVR))"
:r .\PostScripts\AgentJobs\Script.Add_AgentJob_StatisticsUpdate.sql

Inside the referenced script the variables can be called like regular sqlcmd variables.

SELECT @logname = @logdir + N'\StatisticUpdate_$(JOBID)_$(STEPID)_$(STRTDT)_$(STRTTM).txt'

Using this method I am now able to deploy maintenance agent jobs alongside our dba tools database using a Visual Studio database project.

Script.Add_AgentJob_StatisticsUpdate.sql for your reference:

USE [msdb]
GO

-- Get current SQL Server Log Directory
DECLARE @sqlvers NUMERIC(4,2)
DECLARE @logdir NVARCHAR(4000)

SELECT @sqlvers = LEFT(CAST(SERVERPROPERTY('productversion') AS VARCHAR), 4)

IF @sqlvers < 12
        BEGIN
                    -- SQL Server 2012 or less
                    EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',N'Software\Microsoft\MSSQLServer\MSSQLServer',N'SQLArg1', @logdir OUTPUT, 'no_output'

                    IF @logdir is null
                    BEGIN
                                EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',N'Software\Microsoft\MSSQLServer\Setup',N'SQLDataRoot', @logdir OUTPUT, 'no_output'
                                SELECT @logdir = @logdir + N'\LOG'
                    END
        END

ELSE
        BEGIN
                    -- SQL Server 2014 or greater
                    SET @logdir = N'$(SQLLOGDIR)'
        END


-- Create Outputfile and execution statements
DECLARE @logname NVARCHAR(4000)
DECLARE @commandstatement NVARCHAR(4000)

SELECT @logname = @logdir + N'\StatisticUpdate_$(JOBID)_$(STEPID)_$(STRTDT)_$(STRTTM).txt'
SET @commandstatement = N'sqlcmd -E -S $(SRVR) -d MSSYS -Q "EXECUTE dbo.IndexOptimize @Databases = ''USER_DATABASES'', @FragmentationLow = NULL, @FragmentationMedium = NULL, @FragmentationHigh = NULL, @UpdateStatistics = ''ALL'', @OnlyModifiedStatistics = ''Y'', @MaxDOP = 0, @LogToTable = ''Y''" -b'


-- Create Agent Job
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0

IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'Database Maintenance' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'Database Maintenance'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END


-- Stop if job already exists
IF EXISTS (SELECT job_id FROM msdb.dbo.sysjobs_view WHERE name = N'MSSYS StatisticUpdate - USER_DATABASES')
BEGIN
GOTO EndSave
END


DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'MSSYS StatisticUpdate - USER_DATABASES',
                    @enabled=0,
                    @notify_level_eventlog=2,
                    @notify_level_email=0,
                    @notify_level_netsend=0,
                    @notify_level_page=0,
                    @delete_level=0,
                    @description=N'Source: https://ola.hallengren.com',
                    @category_name=N'Database Maintenance',
                    @owner_login_name=N'mssqlsu', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'StatisticUpdate - USER_DATABASES',
                    @step_id=1,
                    @cmdexec_success_code=0,
                    @on_success_action=1,
                    @on_success_step_id=0,
                    @on_fail_action=2,
                    @on_fail_step_id=0,
                    @retry_attempts=0,
                    @retry_interval=0,
                    @os_run_priority=0, @subsystem=N'CmdExec',
                    @command=@commandstatement,
                    @output_file_name=@logname,
                    @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'MSSYS StatisticUpdate - USER_DATABASES',
                    @enabled=0,
                    @freq_type=8,
                    @freq_interval=62,
                    @freq_subday_type=1,
                    @freq_subday_interval=0,
                    @freq_relative_interval=0,
                    @freq_recurrence_factor=1,
                    @active_start_date=20171010,
                    @active_end_date=99991231,
                    @active_start_time=190000,
                    @active_end_time=235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.