May 1, 2012 at 10:07 am
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.
May 1, 2012 at 10:20 am
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:
May 1, 2012 at 10:30 am
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.
May 1, 2012 at 10:32 am
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.
May 1, 2012 at 10:38 am
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.
May 1, 2012 at 11:04 am
Did you also make the change Robin suggested?
If so, and it still didn't work, what error message(s) do you get.
May 1, 2012 at 11:10 am
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.
May 1, 2012 at 11:13 am
Please post all the code you are using.
May 1, 2012 at 11:26 am
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
May 1, 2012 at 11:39 am
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
May 1, 2012 at 11:49 am
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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy