Using double quotes in a T-SQL script fails in SQLCMD, but not SSMO

Question

Executing the following script via sqlcmd fails. However, executing it via ssmo or SQL Server Management Studio works.

sqlcmd -S . -d test -i input.sql

input.sql:

CREATE FUNCTION test()  
RETURNS  @t TABLE ("ID" INT)  
AS  
BEGIN  
 RETURN  
END  

Even when I put SQL Server Management Studio into sqlcmd mode, it still fails. This is a problem as we test our scripts with SSMS, but deploy with SQLCMD. Thus we only find out our code doesn't work when we attempt to deploy.

Why does sqlcmd behave like this? Is there any way to turn it off?

Answer 1

Use square brackets for this: [ID]

Answer 2

If you really need to use double quotes to delimit identifiers in your scripts, you'd need to set the quoted_identifier option on SQLCMD by using the -Ienable switch. It is OFF by default in SQLCMD but ON by default in SSMS, which is why your tests work in SSMS.

Read more about QUOTED_IDENTIFIER here.