Query runs in SQL Server Management Studio but not in sqlcmd

Question

I wrote a script to copy data from one database to another database that has a (nearly) identical schema.

It runs fine in SSMS sqlcmd mode, but it fails when I try it in sqlcmd.

If it makes any difference, I am running sqlcmd from a php script in cli mode.

I've already confirmed that both parameters are valid database names.

BEGIN TRANSACTION copy_data_into_existing_database;
SET XACT_ABORT ON;

DECLARE @tables TABLE (table_name VARCHAR (MAX));
DECLARE @SQL VARCHAR (MAX);

INSERT INTO @tables
    SELECT table_name
    FROM [$(source)].information_schema.tables
    WHERE table_schema = 'dbo' AND table_type = 'BASE TABLE';

/*
  Dynamically generate the query for each table in the list
  The query template is
    - truncate the table
    - if the table has an identity SET IDENTITY_INSERT ON
    - INSERT INTO the destination DB with a list of all of the columns
    - SELECT FROM the source DB with a list of all of the columns
    - if the table has an identity SET IDENTITY_INSERT OFF
 */
SET @SQL =
(SELECT
  -- this line is the truncate statement
 'TRUNCATE TABLE [$(dest)].dbo.' + table_name + ';' +
 -- this case statement sets the identity insert if the table has an identity
  CASE WHEN (EXISTS(SELECT t.object_id
                    FROM [$(dest)].sys.tables t
                      INNER JOIN [$(dest)].sys.identity_columns ic ON t.object_id = ic.object_id
                    WHERE t.name = table_name))
  THEN 'SET IDENTITY_INSERT [$(dest)].dbo.' + table_name + ' ON;'
        ELSE '' END +
        -- this line is the insert statement, the substring-select gets a CSV of all non-computed columns for this table
        'INSERT INTO [$(dest)].dbo.' + table_name + '(' + SUBSTRING((SELECT ',' + c.name
                                                                      FROM [$(source)].sys.columns c
                                                                      INNER JOIN [$(source)].sys.tables t
                                                                        ON c.object_id = t.object_id
                                                                      WHERE is_computed = 0
                                                                        AND t.name = table_name
                                                                      FOR XML PATH ('')), 2, 99999) + ') ' +
        -- this line is the select statement, it repeats the same substring-select from the previous line
        'SELECT ' + SUBSTRING((SELECT ',' + c.name
                                FROM [$(source)].sys.columns c
                                INNER JOIN [$(source)].sys.tables t ON c.object_id = t.object_id
                                WHERE is_computed = 0 AND t.name = table_name
                               FOR XML PATH ('')), 2, 99999) + ' ' +
        'FROM [$(source)].dbo.' + table_name + ';' +
        -- this case statement sets the identity insert off it the table has an identity
        CASE WHEN (EXISTS(SELECT t.object_id
                          FROM [$(dest)].sys.tables t
                           INNER JOIN [$(dest)].sys.identity_columns ic ON t.object_id = ic.object_id
                          WHERE t.name = table_name))
          THEN 'SET IDENTITY_INSERT [$(dest)].dbo.' + table_name + ' OFF;'
        ELSE '' END
 FROM @tables tl
 FOR XML PATH (''));

SELECT (@SQL);
SET @SQL = 'SET NOEXEC ON;' + @SQL; --DEBUG
EXECUTE (@SQL);


COMMIT TRANSACTION copy_data_into_existing_database;

When I run it in sqlcmd there's a bunch of syntax errors.

Msg 102, Level 15, State 1, Server INTERNAL\INTERNAL, Line 24
Incorrect syntax near '].dbo.'.

Msg 102, Level 15, State 1, Server INTERNAL\INTERNAL, Line 29
Incorrect syntax near ')'.

Msg 156, Level 15, State 1, Server INTERNAL\INTERNAL, Line 39
Incorrect syntax near the keyword 'FOR'.

Msg 156, Level 15, State 1, Server INTERNAL\INTERNAL, Line 45
Incorrect syntax near the keyword 'FOR'.

Msg 102, Level 15, State 1, Server INTERNAL\INTERNAL, Line 51
Incorrect syntax near ')'.

Answer 1

The problem was that the strings were double quoted. I was entering this in the command line:

sqlcmd -Sinternal\internal -iV:\inventory2-temp-rollout\MigrationHelpers\CopyDataIntoEx‌​istingDb.sql -v source="'products_live'" -v dest="'products'"

I removed the single quotes:

sqlcmd -Sinternal\internal -iV:\inventory2-temp-rollout\MigrationHelpers\CopyDataIntoEx‌​istingDb.sql -v source="products_live" -v dest="products"