0

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' --<--Insert target server name here.
IF @SQLVersion > '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?

  • 1
    Remove the quotes around the variable name so it's treated as a parameter value instead of literal. – Dan Guzman Feb 6 at 17:31
  • Worked like a charm, thanks you so much. I removed quotes along with letter N. – Ali Feb 6 at 17:36
  • 1
    You set @server_name to literal string '@TargetServer'. – Eric Feb 6 at 17:44
0

See @DanGuzman comment above. It did the trick. I have to remove the quotes and N from the EXEC statement for my variable. Credit goes to Dan Guzman.

EXEC msdb.dbo.sp_add_jobserver @job_id=N'jb3510a9-d75f-4906-b9e3-en74b947d7ev', @server_name = @TargetServer

| improve this answer | |

Not the answer you're looking for? Browse other questions tagged or ask your own question.