Using the @@servicename variable within a SQL Server agent job

  • I have been trying to use this line within my job so that the directory name (corresponding to the servicename of the instance the job runs from) will be dynamically replaced at run time. This is so that I can use the same job on many servers. I tried this The line about 6 lines down (depending on how much line wrapping) and starts with @output_file_name :

    ON dbo.sysjobschedules.schedule_id = dbo.sysschedules.schedule_id

    inner join msdb..sysjobhistory jh

    on dbo.sysjobs.job_id = jh.job_id

    order by enabled desc, name desc, observed_end_time desc',

    @database_name=N'msdb',

    @output_file_name=N'E:\SQL Server Backup\@@servicename\Output\Master Job schedule.csv',

    @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_jobserver @job_id = @jobId, @server_name = N'(local)'

    if (@@ERROR <> 0 OR @ReturnCode <> 0) goto QuitWithRollback

    I'd like the @@servicename to expand with the name of my instance.

    If I execute the line: print @@servicename within SSMS, I see the servicename. I've tried doublequotes around the output_file_name string thinking that the variable would be interpolated (That would work in SAS ;-).

    Thanks for any help.

  • Hi farmkittie,

    Does this example help? Paste below in to SSMS and execute; you should see the service name in output.

    DECLARE @output_file_name NVARCHAR(255)

    SET @output_file_name = N'E:\SQL Server Backup\' + CAST(@@SERVICENAME AS NVARCHAR) + '\Output\Master Job schedule.csv'

    PRINT @output_file_name

    :exclamation: "Be brave. Take risks. Nothing can substitute experience." :exclamation:

  • Thanks Robin for the super fast reply. I'm afraid that I cannot get it work within the context I'm using it though. Please let me know what I'm doing wrong:

    inner join msdb..sysjobhistory jh

    on dbo.sysjobs.job_id = jh.job_id

    order by enabled desc, name desc, observed_end_time desc',

    @database_name=N'msdb',

    @output_file_name=N'E:\SQL Server Backup\' + CAST(@@SERVICENAME AS NVARCHAR) + '\Output\Master Job schedule.csv',

    @flags=0

    I have the declare statement up above the start of my select query.

    The error I get is: "Incorrect syntax near '+', which is the first concatenation symbol in the @output_file_name string before the CAST(@@SERVICENAME as...

    Thanks.

  • The only other thing I would recommend, take the spaces out of your filename. Spaces in the names of directories and files just cause problems with automated processes. They tend to make simple things complex.

  • Thanks Lynn. I tried that and I still got the same error about the '+' character/operator. If it had been the problem I would have been in for a world of hurt because all the servers are set up (predating my arrival) with the "SQL Server Backup" folder. It would have taken an act of Congress to get that changed. Oh - I also took the spaces out of my nfilename and it gave the same error.

    Would it help anyone help me if I posted the whole script that creates the job? I have only posted a snippet so far.

    Thanks.

  • Did you also make the change Robin suggested?

    If so, and it still didn't work, what error message(s) do you get.

  • Yes I did make the original change that Robin suggested. My reply is right after that (The third down if you count my original question as 1). I included the error that I got. I am continuing with Robin's suggestion and building on that with any suggestions people make unless the new suggestion means I need to replace it.

    To be clear, I am running a script that builds a sql server agent job. I can build it and execute it and it works fine if I hard code the instance name in the directory path. I wanted to replkce the instance name with @@servicename swo that the script is portable to many other servers and no one has to remember to change the instance name to correspond to the server the script to build the job is tun from.

    Thanks for the help and follow up.

  • Please post all the code you are using.

  • Here it is:

    use msdb

    go

    begin transaction

    declare @ReturnCode int

    select @ReturnCode = 0

    if not exists (select name from msdb.dbo.syscategories where name=N'[Uncategorized (Local)]'

    and category_class = 1)

    begin

    exec @ReturnCode = msdb.dbo.sp_add_category @class = N'JOB', @type =N'LOCAL', @name = N'[Uncategorized (Local)]'

    if (@@ERROR <> 0 or @ReturnCode <> 0) goto QuitWithRollback

    end

    declare @jobId binary(16)

    exec @ReturnCode = msdb.dbo.sp_add_job @job_name = N'Generate Master Job Schedule',

    @enabled=1,

    @notify_level_eventlog=0,

    @notify_Level_email=0,

    @notify_level_netsend=0,

    @notify_Level_page=0,

    @delete_Level=0,

    @description=N'Run this job to use the scalar function named MasterJobSchedule to produce a current job schedule. A comma separated value file, suitable for import into Excel, is generated. Find it in the Output subfolder of the SQL Server Backup folder on the same server, in the same instance folder, of the server from which you run this job. Please be sure to use the import function of Excel rather than just double clicking on the file if you want the fields parsed into columns properly. The delimiter is a tilde (~) ',

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

    @owner_login_name=N'SchdUsr', @job_id=@jobId output

    if (@@ERROR <> 0 or @ReturnCode <> 0) goto QuitWithRollback

    EXEC @ReturnCode = sp_add_jobstep @job_id=@jobId, @step_name=N'Step one',

    @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'

    declare @freq_type as int

    declare @freq_interval as int

    declare @freq_subday_type INT

    declare @freq_subday_interval INT

    declare @freq_relative_interval INT

    declare @freq_recurrence_factor INT

    declare @active_start_date INT

    declare @active_end_date INT

    declare @active_start_time INT

    declare @active_end_time INT

    DECLARE @output_file_name VARCHAR(max)

    SELECT distinct dbo.sysjobs.name

    ,"~"

    , dbo.MasterJobSchedule (dbo.sysschedules.freq_type

    , dbo.sysschedules.freq_interval

    , dbo.sysschedules.freq_subday_type

    , dbo.sysschedules.freq_subday_interval

    , dbo.sysschedules.freq_relative_interval

    , dbo.sysschedules.freq_recurrence_factor

    , dbo.sysschedules.active_start_date

    , dbo.sysschedules.active_end_date

    , dbo.sysschedules.active_start_time

    , dbo.sysschedules.active_end_time)

    AS ScheduleDscr

    ,"~"

    , dbo.sysjobs.enabled

    ,"~"

    ,observed_end_time = DATEADD

    (

    SECOND,

    jh.run_duration,

    CAST

    (

    CONVERT(VARCHAR, jh.run_date)

    + " " + STUFF(STUFF(RIGHT("000000"

    + CONVERT(VARCHAR,jh.run_time),6),5,0,":"),3,0,":")

    AS DATETIME

    )

    )

    , "~"

    , CONVERT(char(8), dateadd(second, jh.run_duration, "00:00:00"),108) as "Duration in Hrs:Mins:Secs"

    ,"~"

    , NextRunDate =

    DATENAME(dw, CONVERT(CHAR(8), next_run_date, 112))

    + " "

    + DATENAME(m, CONVERT(CHAR(8), next_run_date, 112))

    + " "

    + CONVERT(VARCHAR(12), DAY(CONVERT(CHAR(8), next_run_date, 112)))

    + " "

    + CONVERT(CHAR(4), YEAR(CONVERT(CHAR(8), next_run_date, 112)))

    + " "

    + STUFF(STUFF(RIGHT("000000"

    + CONVERT(VARCHAR(8), next_run_time), 6), 5, 0, ":"), 3, 0, ":")

    FROM dbo.sysjobs INNER JOIN

    dbo.sysjobschedules

    ON dbo.sysjobs.job_id = dbo.sysjobschedules.job_id

    INNER JOIN dbo.sysschedules

    ON dbo.sysjobschedules.schedule_id = dbo.sysschedules.schedule_id

    inner join msdb..sysjobhistory jh

    on dbo.sysjobs.job_id = jh.job_id

    order by enabled desc, name desc, observed_end_time desc',

    @database_name=N'msdb'

    ,@output_file_name='E:\SQL Server Backup\' + CAST(@@SERVICENAME AS VARCHAR) + '\Output\MasterJobschedule.csv'

    ,@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_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

  • Try the following as it looks like it does like you concatenating the string while assigning to a parameter:

    use msdb

    go

    DECLARE @OutputFileName VARCHAR(128);

    SET @OutputFileName = 'E:\SQL Server Backup\' + CAST(@@SERVICENAME AS VARCHAR) + '\Output\MasterJobschedule.csv';

    begin transaction

    declare @ReturnCode int

    select @ReturnCode = 0

    if not exists (select name from msdb.dbo.syscategories where name=N'[Uncategorized (Local)]'

    and category_class = 1)

    begin

    exec @ReturnCode = msdb.dbo.sp_add_category @class = N'JOB', @type =N'LOCAL', @name = N'[Uncategorized (Local)]'

    if (@@ERROR <> 0 or @ReturnCode <> 0) goto QuitWithRollback

    end

    declare @jobId binary(16)

    exec @ReturnCode = msdb.dbo.sp_add_job @job_name = N'Generate Master Job Schedule',

    @enabled=1,

    @notify_level_eventlog=0,

    @notify_Level_email=0,

    @notify_level_netsend=0,

    @notify_Level_page=0,

    @delete_Level=0,

    @description=N'Run this job to use the scalar function named MasterJobSchedule to produce a current job schedule. A comma separated value file, suitable for import into Excel, is generated. Find it in the Output subfolder of the SQL Server Backup folder on the same server, in the same instance folder, of the server from which you run this job. Please be sure to use the import function of Excel rather than just double clicking on the file if you want the fields parsed into columns properly. The delimiter is a tilde (~) ',

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

    @owner_login_name=N'SchdUsr', @job_id=@jobId output

    if (@@ERROR <> 0 or @ReturnCode <> 0) goto QuitWithRollback

    EXEC @ReturnCode = sp_add_jobstep @job_id=@jobId, @step_name=N'Step one',

    @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'

    declare @freq_type as int

    declare @freq_interval as int

    declare @freq_subday_type INT

    declare @freq_subday_interval INT

    declare @freq_relative_interval INT

    declare @freq_recurrence_factor INT

    declare @active_start_date INT

    declare @active_end_date INT

    declare @active_start_time INT

    declare @active_end_time INT

    DECLARE @output_file_name VARCHAR(max)

    SELECT distinct dbo.sysjobs.name

    ,"~"

    , dbo.MasterJobSchedule (dbo.sysschedules.freq_type

    , dbo.sysschedules.freq_interval

    , dbo.sysschedules.freq_subday_type

    , dbo.sysschedules.freq_subday_interval

    , dbo.sysschedules.freq_relative_interval

    , dbo.sysschedules.freq_recurrence_factor

    , dbo.sysschedules.active_start_date

    , dbo.sysschedules.active_end_date

    , dbo.sysschedules.active_start_time

    , dbo.sysschedules.active_end_time)

    AS ScheduleDscr

    ,"~"

    , dbo.sysjobs.enabled

    ,"~"

    ,observed_end_time = DATEADD

    (

    SECOND,

    jh.run_duration,

    CAST

    (

    CONVERT(VARCHAR, jh.run_date)

    + " " + STUFF(STUFF(RIGHT("000000"

    + CONVERT(VARCHAR,jh.run_time),6),5,0,":"),3,0,":")

    AS DATETIME

    )

    )

    , "~"

    , CONVERT(char(8), dateadd(second, jh.run_duration, "00:00:00"),108) as "Duration in Hrs:Mins:Secs"

    ,"~"

    , NextRunDate =

    DATENAME(dw, CONVERT(CHAR(8), next_run_date, 112))

    + " "

    + DATENAME(m, CONVERT(CHAR(8), next_run_date, 112))

    + " "

    + CONVERT(VARCHAR(12), DAY(CONVERT(CHAR(8), next_run_date, 112)))

    + " "

    + CONVERT(CHAR(4), YEAR(CONVERT(CHAR(8), next_run_date, 112)))

    + " "

    + STUFF(STUFF(RIGHT("000000"

    + CONVERT(VARCHAR(8), next_run_time), 6), 5, 0, ":"), 3, 0, ":")

    FROM dbo.sysjobs INNER JOIN

    dbo.sysjobschedules

    ON dbo.sysjobs.job_id = dbo.sysjobschedules.job_id

    INNER JOIN dbo.sysschedules

    ON dbo.sysjobschedules.schedule_id = dbo.sysschedules.schedule_id

    inner join msdb..sysjobhistory jh

    on dbo.sysjobs.job_id = jh.job_id

    order by enabled desc, name desc, observed_end_time desc',

    @database_name=N'msdb'

    ,@output_file_name = @OutputFileName --'E:\SQL Server Backup\' + CAST(@@SERVICENAME AS VARCHAR) + '\Output\MasterJobschedule.csv'

    ,@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_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

  • Wow, it works! Thanks so much Lynn. I replaced the line you partially commented out and added the two lines up near the top and it worked perfectly. Thanks again!!

Viewing 11 posts - 1 through 11 (of 11 total)

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