CODEWITHSUNDEEP
sql-serverssmssqlcmd
LeGEC
LeGEC

Reputation: 52176

TSQL syntax not the same for SSMS and sqlcmd

Here is a simple tsql file :

# file foo.sql :
DROP TABLE IF EXISTS "Testing";

CREATE TABLE "Testing"
(
    "Id" BIGINT NOT NULL,
    "FilePath" NVARCHAR (442) NULL,
    CONSTRAINT "PK_Testing" PRIMARY KEY CLUSTERED ("Id" ASC)
);

GO

When I load it through SQL Server Management Studio, all goes fine :

When I use it as input through sqlcmd, I get an error :

> sqlcmd -S .\SQLEXPRESS -d DemoDb -i foo.sql
Msg 102, Level 15, State 1, Server MY-LAPTOP\SQLEXPRESS, Line 1
Incorrect syntax near 'Testing'.

Question

How do I tell sqlcmd to behave like SSMS in this case ?

Should I use another command line tool which acts like SSMS ?

version information :

> sqlcmd -?
Microsoft (R) SQL Server Command Line Tool
Version 15.0.1300.359 NT
Copyright (C) 2019 Microsoft Corporation. All rights reserved.

usage: ...

SSMS : v18.3.1

Component name                              Versions
SQL Server Management Studio                15.0.18183.0
Microsoft Analysis Services Client Tools    15.0.1487.0
Microsoft Data Access Components (MDAC)     10.0.18362.1
Microsoft MSXML                             3.0 6.0 
Microsoft Internet Explorer                 9.11.18362.0
Microsoft .NET Framework                    4.0.30319.42000
Operating System                            10.0.18362

Upvotes: 1

Views: 662

Answers (1)

EzLo
EzLo

Reputation: 14209

SQLServer has a QUOTE_IDENTIFIER {ON|OFF} option, which modifies the effects of " in TSQL queries.

You can turn it on with sqlcmd by using the -I option :

sqlcmd -I -S .\SQLEXPRESS -d DemoDb -i foo.sql

or by explicitly adding the following instruction in your sql script :

SET QUOTED_IDENTIFIER ON;

SSMS usually sets up a bunch of session configurations when you connect to a server. You can find them on your Options window, under SQL Server (this is an example from old SSMS 2008):

enter image description here

enter image description here

You should check which ones you have enabled and which ones you don't, then you can add them at the start of your .sql file.

For this particular example, the SET QUOTED_IDENTIFIER is making the execution by sqlcmd not identify correctly your object names that use double quotes, because it's set to OFF. You can add the -I parameter on sqlcmd to set this setting ON without modifying the script also.

Upvotes: 4

Related Questions