SQL Server 2008 generate script wizard gives me a script that results in "unclosed quotation marks"

Question

Hi,

I have an SQL server 2008 database instance on one machine. Now I want to copy this database to another machine. I use the script wizard inside SQL Management Studio to generate a SQl-script with the schema and data. The script-file is rather big (around 17 GB).

Then I run the sql-script on the target machine it results in a :

Msg 105, Level 15, State 1 error with the message: Unclosed quotation mark after the character string

I do understand the problem of what unclosed quotation marks mean. But I don't understand why the error happens. Isn't the script generator able to handle quotations inside text strings like...hello, what's up...correctly and create a script that will escape such characters?

Is their a limit on the length of text for the script wizard? Is this causing the problem.

I don't want to and I cannot open the script-file in a text editor (too large, text editor will crash) and manually fix the problems.

Do you have any ideas?

Answer 1

Not a direct answer to the question but to duck this issue you could use one of the following other methods of copying the database to the new location.

1. Copying Databases with Backup and Restore
2. Using Detach and Attach

Method 1 is usually preferable as it keeps the source DB online and detaching can cause information held in the master database about the source to be lost (e.g. full text enabled status)

Answer 2

EDIT: Just noted from your comment that you're running sqlcmd -S server\database -i script.sql. There is a -I switch that stands for "Enable Quoted Identifiers". Try to run the command with this switch.

Btw, to edit a large file, consider using a nice editor like Notepad++ or UltraEdit. I wouldn't use a workstation without em :)