How to use variable in Exec statement

Question

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?

score 0 · Accepted Answer · answered Feb 06 '20 at 17:38

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

How to use variable in Exec statement

1 Answers1