5

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.

  • 1
    I know you've probably already checked, but you have the step set up to run as Operating System (CmdExec), right? – Erik Darling Jun 14 '18 at 19:04
  • 1
    Just making sure you ran EXEC msdb.dbo.sp_set_sqlagent_properties @alert_replace_runtime_tokens = 1 and restarted sql Agent as well ? – Kin Shah Jun 14 '18 at 19:20
  • 1
    I'd be curious to hear back from you if running the sqlcmd as c:\Windows\System32\cmd.exe /c sqlcmd -E -S MyExplicitServerName -d master -Q "EXECUTE MyStoredProc etc..." in this sort of format has any effect as well. Tag me back and I will explain if that helps you but when c:\Windows\System32\cmd.exe /c if prepended before the sqlcmd command this will ensure cmd.exe executes the sqlcmd within its shell or whatever as PowerShell can interpret $(ESCAPE_SQUOTE(SRVR)) as a variable if powershell.exe executes this code. – Pimp Juice IT Jun 14 '18 at 19:53
  • @Kin That had no effect, but thanks for the suggestion. – theog Jun 14 '18 at 20:21
  • @PimpJuiceIT That worked. Interesting... – theog Jun 14 '18 at 20:21
2

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.

  • 1
    I just sent you an edit to add some additional content as well. What does using a semicolon ; in your command do? – Pimp Juice IT Jun 14 '18 at 21:39
  • @PimpJuiceIT I accepted your edit, thanks. And yes, a semicolon at the end of the execute statement in the sqlcmd does work in place of the 'GO'. – theog Jun 14 '18 at 21:59
  • Would you mind adding an example of how the logic works with the ending semicolon when you get a chance with an edit? I think it's be a helpful addition to this answer as well. – Pimp Juice IT Jun 15 '18 at 21:55
1

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://docs.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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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