search

olahallengren / sql-server-maintenance-solution

SQL Server Maintenance Solution
https://ola.hallengren.com
MIT License
2.91k stars 756 forks source link

(JOBNAME) variable not working on SQL Server 2014? #801

Closed tivivi63 closed 6 months ago

tivivi63 commented 7 months ago

Hello,

Getting following message when executing whichever Ola's job using JOBNAME variable in Advanced>Output file parameter, like $(ESCAPE_SQUOTE(SQLLOGDIR))\$(ESCAPE_SQUOTE(JOBNAME))_$(ESCAPE_SQUOTE(STEPID))_$(ESCAPE_SQUOTE(DATE))_$(ESCAPE_SQUOTE(TIME)).txt) in CommandLog Cleanup job.

Message
Unable to start execution of step 1 (reason: Variable JOBNAME not found).  The step failed.

Both are executed on mly laptop (though outputs differ regarding OS version 😕) but I do have the same behaviour on virtualized servers (e.g Windows Server 2016). Really looks like related to SQL version, not Windows Server build.

Fails:

Microsoft SQL Server 2014 (SP3-CU4-GDR) (KB5014164) - 12.0.6439.10 (X64)   Apr 20 2022 03:13:42   Copyright (c) Microsoft Corporation  Developer Edition (64-bit) on Windows NT 6.3 <X64> (Build 19045: ) (Hypervisor)

Works:

Microsoft SQL Server 2019 (RTM-CU20) (KB5024276) - 15.0.4312.2 (X64)   Apr  1 2023 12:10:46   Copyright (C) 2019 Microsoft Corporation  Developer Edition (64-bit) on Windows 10 Enterprise 10.0 <X64> (Build 19045: ) (Hypervisor)

Version of the script Version: 2022-12-03 17:23:44

Thanks for sharing this tremendous tool 🙏

Stiffboard commented 7 months ago

Installing scripts take care of that I believe... One of the variables, SQLLOGDIR I think, is not available in older versions. So if you script the job from a newer it won't work.

tivivi63 commented 6 months ago

https://learn.microsoft.com/en-us/sql/ssms/agent/use-tokens-in-job-steps?view=sql-server-ver16#sql-server-agent-tokens

As stated in the documentation, both JOBNAME and STEPNAME tokens are not availlable until SQL Server 2016 😕

(JOBNAME) | The name of the job. This token is only available on SQL Server 2016 and above. (STEPNAME) | The name of the step. This token is only available on SQL Server 2016 and above.