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?

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

1 Answer 1

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

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