December 13, 2021 at 3:20 pm
I could use some help figuring why this use of CONVERT isn't working as expected.
--T-SQL Code:
1 SET nocount ON
2 DECLARE @JobName sysname
3 SELECT @JobName = [name] FROM msdb.dbo.sysjobs
4 WHERE Job_id = CONVERT(uniqueidentifier, $(ESCAPE_NONE(JOBID)))
5 PRINT '>'+@JobName+'<'
--Error:
Msg 102, Level 15, State 1, Line 4
Incorrect syntax near '('.
--SQL Server Version/Edition:
Microsoft SQL Server 2017 (RTM-CU27) (KB5006944) - 14.0.3421.10 (X64) Oct 14 2021 00:47:52 Copyright (C) 2017 Microsoft Corporation Developer Edition (64-bit) on Windows Server 2019 Datacenter 10.0 <X64> (Build 17763: ) (Hypervisor)
--Instance Collation:
SQL_Latin1_General_CP1_CI_AS
--Database Collation:
SQL_Latin1_General_CP1_CI_AS
December 13, 2021 at 4:55 pm
I can't say with 100% certainty the cause of the error, but to me that doesn't look like TSQL.
What I mean is the $ part and ESCAPE_NONE. I don't know of any such function, so I am thinking it is a custom function OR is non-SQL Server code. I also don't think that sysjobs has a column called "JOBID", just Job_ID.
So your entire WHERE clause is wrong and honestly I am not sure what that WHERE is supposed to do.
Could you elaborate on what that WHERE clause is supposed to be doing?
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
December 13, 2021 at 5:45 pm
I can't say with 100% certainty the cause of the error, but to me that doesn't look like TSQL.
What I mean is the $ part and ESCAPE_NONE. I don't know of any such function, so I am thinking it is a custom function OR is non-SQL Server code. I also don't think that sysjobs has a column called "JOBID", just Job_ID.
So your entire WHERE clause is wrong and honestly I am not sure what that WHERE is supposed to do.
Could you elaborate on what that WHERE clause is supposed to be doing?
That is a valid token. https://docs.microsoft.com/en-us/sql/ssms/agent/use-tokens-in-job-steps?view=sql-server-ver15
This will not work in T-SQL. It will only work in SQLCMD mode or as an Operating System command.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
December 13, 2021 at 6:01 pm
Michael,
Thanks for the insight and clarity. Any further help/insight/advise would be greatly appreciated.
The failing code is part of a SQL Agent Job step from:
https://www.sqlservercentral.com/articles/sql-overview-ssis-package-iii-full-package-1
See:
Create job DBA-SQL Overview
This job's primary purpose is to execute the SSIS SQL Overview package and start the report jobs. Since the package uses tables in TEMPDB they are created before the package is executed. SSIS requires them to exist on the local instance before the package is executed. These tables are dropped every time the instance is restarted. So it's just safer to have the job create them every time.
Job Name: DBA-SQL Overview
Job Owner: Windows SERVICE Login ID for the SQL Server Instance
Step1
Save the datetime of the last successful execution of this job.
Step name: s01-Job Last Run Datetime
Type: Transact-SQL script (T-SQL)
Database: SQL_Overview
Command:
-- Get Job Name
SET nocount ON
DECLARE @JobName sysname
SELECT @JobName = [name] FROM msdb.dbo.sysjobs
WHERE Job_id = CONVERT(uniqueidentifier, $(ESCAPE_NONE(JOBID)))
PRINT '>'+@JobName+'<'
-- Get the date of the last successful execution of this job
DECLARE @dtLastRun datetime
DECLARE @chLastRun CHAR(26)
SELECT TOP 1 @dtLastRun =
CAST(CAST(run_date AS CHAR(8)) + ' ' + -- Convert run_date to DateTime data type
STUFF(STUFF( -- Insert : into Time
RIGHT('000000' + -- Add leading Zeros
CAST(run_time AS VARCHAR(6)) ,6) , 3, 0, ':'), 6, 0, ':') AS datetime)
FROM msdb.dbo.sysjobs A, msdb.dbo.sysjobhistory B
WHERE A.job_id = B.job_id AND B.run_status = 1
AND A.name = @JobName
ORDER BY 1 DESC
SELECT 'Job Last Run Date', @dtLastRun
IF @dtLastRun IS NULL SET @dtLastRun = '1900-01-01'
SET @chLastRun = CONVERT(CHAR(26),@dtLastRun)
PRINT 'Job Last Run Date = ' + @chLastRun
DELETE FROM [rep].[SQL_Overview_Last_Run_Date]
INSERT INTO [rep].[SQL_Overview_Last_Run_Date]
([LastJobRun_date])
VALUES
(@chLastRun)
December 13, 2021 at 6:45 pm
I glanced at this article. You do realize that this was written 12 years ago, and that there are far easier ways to do most of this?
Like this maybe?
If you read the discussions for all three parts of the article, you are not the only person who experienced this error. If you are committed to using this method, read the discussions. You answers are in there.
Me, I would go a different route.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
December 13, 2021 at 8:57 pm
One of the annoying things about the $(ESCAPE macros is that they are recognized only in job code (at least the last time I checked this).
So you'll have to run the code as a job step to determine if it's working or not.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
December 13, 2021 at 9:21 pm
One of the annoying things about the $(ESCAPE macros is that they are recognized only in job code (at least the last time I checked this).
So you'll have to run the code as a job step to determine if it's working or not.
No, you can run it in a query window in SSMS using SQLCMD mode
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
December 15, 2021 at 8:46 am
This was removed by the editor as SPAM
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply