2

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?

  • 2
    :!! does not work, it's a bug. see: SO and/or microsoft – Luuk Feb 28 at 15:32
  • Luuk that was very helpful. Do you know if the issue is solve in a newer version of the command line utilities? Or is there a way to fix it? – Daniela Feb 29 at 1:30
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.... 😕😵

| improve this answer | |
0

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)

| improve this answer | |
  • 1
    it works fine if you run it from SSMS with sqlcmd mode, but if you execute it from a bat file with sqlcmd it doesn't work – Daniela Feb 29 at 1:29

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.