SSMS generated script failing to execute

So I am in a situation where I need to import a small DB (under 1GB) from a newer version of SQL Server (I am restoring to a 2014 SQL Server from a 2016 SQL server), without a direct connection between the two. This DB is the backend for a CMS and so stores not only conventional data but html content as well.

The solution I found (I followed this guide.)was to use "Generate Scripts" in SSMS (schema+data) to script out the database, and then execute the script to recreate it on the destination server. The resulting script is about 900MB total.

The first issue I ran into was that SSMS would not execute the script as it was apparently too large for it. Sqlcmd was able to start executing the script, but then failed 10000+ lines into it. When I cut out the script at the failed point, it continued to run no problem where it left off, so I'm guessing it was out-of-memory related or something? The specific error I received was a

"Sqlcmd Error: Syntax Error at line 14769 near command ' " ' in file ".

The actual line at 14769 is:

INSERT [dbo].[sf_control_properties] (
    [val], 
    [validation], 
    [prnt_prop_id], 
    [ordinal], 
    [nme], 
    [last_modified],
    [language], 
    [id], 
    [flags], 
    [description_], 
    [control_id], 
    [caption_], 
    [app_name], 
    [voa_version]
    ) 
VALUES (
    N'RulesGroupBackend', 
    NULL, 
    NULL, 
    1, 
    N'ControlDefinitionName', 
    CAST(N'2018-03-23T12:35:50.977' AS DateTime), 
    NULL, N'7cf975d2-f37d-4716-a634-0d6424921f38', 
    1, 
    NULL, 
    N'27c93f05-386b-4fd2-8aae-d6664ee7e57a', 
    NULL, 
    N'Title/', 
    1)

The line before (multiple line statement 14766-14768) is:

```
INSERT [dbo].[sf_control_properties] (
[val],
[validation],
[prnt_prop_id],
[ordinal],
[nme],
[last_modified],
[language],
[id],
[flags],
[description_],
[control_id],
[caption_],
[app_name],
[voa_version]
)
VALUES (
N'$

Try running SQLCMD with the -x argument to disable variable substation. This will avoid issues when the script includes SQLCMD variable tokens (e.g. $(stuff-here)) but are not actually SQLCMD variables.

StackExchange Database Administrators Q#224796, answer score: 3