3

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!

  • @DanGuzman Wow that worked thanks! (reading the docs it makes total sense, so it was misinterpreting data as part of the script), the script ran (mostly) successfully. The only issue I'm left with now is that at the end of the script I get a bunch of warnings about "The maximum key length is 900 bytes. The index 'idx_blahblah_key' has a maximum length of 1020 bytes. For some combination of large values, the insert/update operation will fail", what can I do to fix that? – Will Dec 12 '18 at 17:17
  • Glad it worked for you. I moved my comment to an answer. The warning message indicates your index has potentially over 900 bytes. Reduce the underlying column size to avoid the warning, or just ignore it if you know the actual length will not exceed 900 bytes. – Dan Guzman Dec 12 '18 at 17:27
  • @Will since this worked I highly recommend accepting the answer Dan has provided. This will help other users to know that this will solve their issue. Also it gives dan some of those sweet sweet unicorn points. – Zane Dec 12 '18 at 17:45
3

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.