Resolving 'Unclosed quotation mark after the character string' on MS SQL Server generated .sql file

Question

I am trying to recreate a remote db on a local SQL Server install. I have a ~(6GB) .sql file generated from the remote db SSMS and am using sqlcmd locally to attempt the import.

Here is the command I am using:

sqlcmd -S SERVER -a 32000 -i inputfile.sql -o output.txt

Around 2200 records in it gives me: Unclosed quotation mark after the character string

Is there something I can do on the import settings (or even create a new exported .sql file) that will resolve this? It's extremely odd that the server generated sql does not appear to be valid sql.

Answer 1

This Stackoverflow thread will prove useful for you. Relevant details recreated below:

Issue description:

In some rare cases the sqlcmd utility can fail with the import and raise the following error: "Unclosed quotation mark after the character string ..." which indicates that one of SQL queries has not been executed. This happens because sqlcmd works using stream processing, i.e. it reads some piece of data, processes it, reads next piece and so on. In some cases an input file can contain huge SQL instruction which size is bigger than the amount of the data that could be processed by sqlcmd at a time, so sqlcmd tries to execute broken SQL and fails.

Possible solution

The sqlcmd utility can accept the "-a" parameter which defines the maximum size of packet (piece of data) that will be used during processing. The maximum value is 32767, the default value is 4096, so it makes sense to always use this parameter with maximum value.

sqlcmd -i input.sql -a 32767 -o import_log.txt

Alternative solution

If the above fails for you, you might want to consider using the Bulk Copy (BCP) utility. I believe it is currently the fastest way to move data from a remote source to a local clone or vice versa. You can find more details here: https://blogs.msdn.microsoft.com/sqlcat/2010/07/30/loading-data-to-sql-azure-the-fast-way/

bcp AdventureWorksLTAZ2008R2.SalesLT.Customer out C:\Users\user\Documents\GetDataFromSQLAzure.txt -c -U username@servername -S tcp:servername.database.windows.net -P password