Sample Header Ad - 728x90

How to use variable in Exec statement

0 votes
1 answer
195 views
I'm trying to add my target server to the Multi Server SQ LAgent Job (MSX). We have multiple versions of SQL Agent jobs and I'm trying to automate adding only related targets according to SQL Server version. I'm trying to add target server using the script below but the the EXEC statement does not recognized the @TargetServer variable. Declare @SQLVersion varchar(10) set @SQLVersion = (SELECT CASE SUBSTRING(CONVERT(VARCHAR(50), SERVERPROPERTY('productversion')), 1, 2) WHEN '8.' THEN '2000' WHEN '9.' THEN '2005' WHEN '10' THEN '2008' WHEN '11' THEN '2012' WHEN '12' THEN '2014' WHEN '13' THEN '2016' WHEN '14' THEN '2017' WHEN '15' THEN '2019' END) Declare @TargetServer varchar(20) SET @TargetServer = 'ProdEMM' -- '2014' EXEC msdb.dbo.sp_add_jobserver @job_id=N'jb3510a9-d75f-4906-b9e3-en74b947d7ev', @server_name = N'@TargetServer' IF @SQLVersion = '2012' EXEC msdb.dbo.sp_add_jobserver @job_id=N'9E0GAK8P-0H74-41H7-A619-80TCDFD3E5G7', @server_name = N'@TargetServer' IF @SQLVersion < '2012' EXEC msdb.dbo.sp_add_jobserver @job_id=N'9E0GAK8P-0H74-41H7-A619-80TCDFD3E5G7', @server_name = N'@TargetServer' ERROR: Msg 14262, Level 16, State 1, Procedure msdb.dbo.sp_add_jobserver, Line 88 [Batch Start Line 0] The specified @server_name ('@TARGETSERVER') does not exist. What would be the best way to handle this situation?
Asked by Ali (345 rep)
Feb 6, 2020, 05:26 PM
Last activity: Feb 6, 2020, 05:38 PM