SQL 2005: Agent job step output file naming

Question

Does anyone know, please, if there's a way to specify "this machine" in a file path that SQL Server will like?

I have an Agent job I want to script onto all my servers. It outputs to a file on the local machine so I want to script the file name as

\\localhost\<shared-folder-name>\<file-name>

Another reason is I'm planning to use log shipping for disaster recovery and I want the jobs to work the same whichever server they're running on. The script sets the job up fine but when I run the job I get an error "Unable to open Step output file". I think I've eliminated share and folder permissions: it works fine with C:\<folder-name>\<file-name>.

Answer 1

have you tried @@SERVERNAME?

SELECT @@SERVERNAME

Or you can use this, the second one you can use if you have multiple instances of SQl server running

SELECT serverproperty('MachineName'),serverproperty('Instancename')

Answer 2

When you script it out You can utilize the @output_file_name parameter

EXECUTE msdb.dbo.sp_add_jobstep @job_name = @JobName01, @step_name = @JobName01, @subsystem = 'CMDEXEC', @command = @JobCommand01, @output_file_name = @OutputFile

To set the output log name, you can do

SET @OutputFile = @LogDirectory + '\SQLAGENT_JOB_$(ESCAPE_SQUOTE(JOBID))_$(ESCAPE_SQUOTE(STEPID))_$(ESCAPE_SQUOTE(STRTDT))_$(ESCAPE_SQUOTE(STRTTM)).txt'

To get the server name
$(ESCAPE_SQUOTE(SRVR))