:!!echo command not working from sqlcmd

Question

I have a sql script file which is executed from a batch file and is supposed to execute some sql scripts and write errors and output to files. Here is an example of how the script file looks like:

:error ".\DataAndTableScripts\9.0.0\_RunOrderedScripts.err"
:out ".\DataAndTableScripts\9.0.0\_RunOrderedScripts.out"
:on error ignore
go
set nocount on;
print 'Running .\DataAndTableScripts\9.0.0\OrderedFileList.txt'
print 'Start time: ' + convert( varchar, getdate(), 121 );
GO
:!!echo   Table_auth.IdentityApplication.sql
:error ".\DataAndTableScripts\9.0.0\Table_auth.IdentityApplication.err"
:out ".\DataAndTableScripts\9.0.0\Table_auth.IdentityApplication.out"
:r ".\DataAndTableScripts\9.0.0\Table_auth.IdentityApplication.sql"
go

The :!!echo Table_auth.IdentityApplication.sql piece is suppose to print this text in the command prompt window. I initially had this code on SQL 2014, but when I upgraded to SQL 2016 and to command line utilities 13 this echo piece stopped working. It seems like, even when I have :on error ignore, when it gets to :!!echo, if fails and the rest of the script is not executed.

Anyone has an idea of what the issue may be?

Answer 1

SQLCMD

Next versions do NOT work:

• Version 14.0.2027.2 NT (from: COMMAND LINE UTILITIES FOR MICROSOFT SQL SERVER 2017)

• Version 15.0.1300.359 NT (from: SQL Server 2019 (15.x))

This version works:

• Version 11.0.2100.60 NT x64 (from: Microsoft SQL Server 2012 Command Line Utilities)

(on my computer found in 'C:\Program Files\Microsoft SQL Server\110\Tools\Binn\SQLCMD.EXE')

This was tested by creating a file sqlcmd.txt which contains 1 line (:!! dir), and checking if this command was executed when performing sqlcmd -i sqlcmd.txt.

I think the version 11 that works can be downloaded here, but do NOT ask why this is named 'Command Line Utilities 13 for SQL Server'), and from a logfile i created during install is installen ina folder named 110.... 😕😵

Answer 2

I just tested that :!!echo works on this version.

Microsoft SQL Server 2016 (SP2-GDR) (KB4293802) - 13.0.5081.1 (X64) Jul 20 2018 22:12:40 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows Server 2016 Datacenter 10.0 (Build 14393: ) (Hypervisor)