SSMS generated script failing to execute

Question

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 <path\script.sql>".

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'$(document).ready(function(){
       $("#content > div > section:nth-child(2) > div > div > div > div > div:nth-child(1) > h2").text("English Support");
});', 
    NULL, 
    N'5d056cac-51db-492c-b601-e88c82a8bc72', 
    1, 
    N'InlineCode',
     CAST(N'2018-11-27T20:33:59.060' AS DateTime), 
     NULL, 
     N'1f8ac206-d771-42b2-9991-0ca66bceff19', 
     1, 
     NULL, 
     NULL, 
     NULL, 
     N'Title/', 
     1
     )

I don't know what the error is referring to when it says syntax error on line 14769 near command ' " '

So in summary I'm wondering A) What may be going wrong with the script at these lines B) Whether this is an issue with sqlcmd (since the script was also failing in SSMS). To me the script appears fine, and after all it was automatically generated by SSMS so I'm confused what the problem could be. Thanks!

Answer 1

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.