SQL Server 2016 SQL Agent Token Not Working

Question

I have this in scores of SQL Agent Job steps throughout my enterprise and it works as expected:

sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d master -Q "EXECUTE MyStoredProc etc..."

But on my new SQL Server 2016 instance, it simply produces a Named Pipes connection error (which is a complete red herring).

This, on the other hand, works just fine on my new server:

sqlcmd -E -S MyExplicitServerName -d master -Q "EXECUTE MyStoredProc etc..."

Why does the SRVR token not work?
If I fire up sqlcmd in a command prompt and tell it to print $(ESCAPE_SQUOTE(SRVR)) it says:

'SRVR' scripting vaiable not defined.

This is a pretty basic, no-frills install with just the default instance.

Answer 1

Okay, I'm now thinking this is something with Windows Server 2016 as this is the first SQL Server 2016 install I've done on Windows Server 2016.

On my Windows Server 2012 R2 (Standard) machine with SQL Server 2016, this works in a SQL Agent CmdExec job step:

sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d master -Q "EXECUTE MyStoredProc etc..."

But on my new Windows Server 2016 (Standard) with SQL Server 2016, the above does not work. I have to do either:

C:\Windows\System32\cmd.exe -c sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d master -Q "EXECUTE MyStoredProc etc..."

Or this works as well:

sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d master -Q "EXECUTE MyStoredProc etc..." 
GO

The cmd.exe -c argument (I was unfamiliar with) is a batch terminator; from Microsoft docs:

-c batch_terminator

Specifies the batch terminator. By default, commands are terminated and sent to SQL Server by typing the word "GO" on a line by itself. When you reset the batch terminator, do not use Transact-SQL reserved keywords or characters that have special meaning to the operating system, even if they are preceded by a backslash.

I don't know why the batch terminator is necessary in this situation...

---

The GO Command and the Semicolon Terminator

The GO Command

“GO” is a batch terminator. Technically speaking, the GO command is not even a part of the Transact-SQL language. It is really a command used by the SQLCMD, OSQL and ISQL utilities that can also be used within Query Analyzer and the Query Editor window.

NOTE: A batch should not be confused with a script. A batch is a set of T-SQL statements that are submitted for execution as a group. A script is simply a file containing set of T-SQL statements. One script can contain many batches.

Answer 2

it is normal that if you open a new tab in SSMS and switch to sqlcmd, the command

print $(ESCAPE_SQUOTE(SRVR))

does not work, since $(variable) is the syntax for using variables in sqlcmd, hence the error message.

Note that tokens are only to use within the SQL Agent scope.

https://learn.microsoft.com/en-us/sql/ssms/agent/use-tokens-in-job-steps?view=sql-server-2017

I tried to reproduce your error in 2 instances I got handy, SQL2014 and SQL2016 and I created the following job, which works just fine on both versions

USE [msdb]
GO

/****** Object:  Job [token test]    Script Date: 14/06/2018 21:14:35 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object:  JobCategory [[Uncategorized (Local)]]    Script Date: 14/06/2018 21:14:35 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'token test', 
        @enabled=1, 
        @notify_level_eventlog=0, 
        @notify_level_email=0, 
        @notify_level_netsend=0, 
        @notify_level_page=0, 
        @delete_level=0, 
        @description=N'No description available.', 
        @category_name=N'[Uncategorized (Local)]', 
        @owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [step1]    Script Date: 14/06/2018 21:14:35 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'step1', 
        @step_id=1, 
        @cmdexec_success_code=0, 
        @on_success_action=1, 
        @on_success_step_id=0, 
        @on_fail_action=2, 
        @on_fail_step_id=0, 
        @retry_attempts=0, 
        @retry_interval=0, 
        @os_run_priority=0, @subsystem=N'CmdExec', 
        @command=N'sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d master -Q "SELECT @@servername"
', 
        @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

GO

So it must be something else, since SQL2016 in this specific matter behaves just as previous version.

/******** Edit ****************/

I have created the above SQL Agent job in a machine which runs Win Server 2016 - SQL Server 2017 both Standard Edition and it just runs fine, as I pointed earlier maybe it's worth trying something simple like

SELECT @@SERVERNAME

to try narrow the problem down