-1

I'm doing an insert using sqlcmd -S command from a large .sql file which has both schema and data values. After insert operation starts I get an error in the middle

Msg 102, Level 15, State 1, Server My-PC\SQLEXPRESS, Line 39
Unclosed quotation mark after the character string FieldN

I can't open the file(as its too large to open) and fix the quotes in the row causing the error but wondering if we can possibly skip such rows with issues related to unclosed quotation marks?

rumi
  • 3,293
  • 12
  • 68
  • 109
  • It's not your source file, it's your query. If you're using dynamic sql, you're missing a quotation mark that allows it to parse. Are you able to check how the sql was built, and edit it to add the (probably) closing quotation mark? – Phoenix Mar 06 '15 at 21:17
  • Database scripts (both schema and data) were automatically generated using SSMS wizard and when I try to run the scripts to create the database and insert the data I get this error. – rumi Mar 06 '15 at 21:22
  • @Downvoter comment and help me correct the question. – rumi Mar 06 '15 at 21:23
  • there's no way to edit the create/insert scripts? Your issue is definitely with the dynamic SQL being used to insert. After thinking about it, it could be that one of your data values has an apostrophe in it. In that case you could escape the apostrophe, or use a param. Either way you'll need to edit those scripts. – Phoenix Mar 06 '15 at 21:26
  • @Closing-voter three people are communicating on this question I'm sure its making some sense to people who are trying to understand and help – rumi Mar 06 '15 at 22:07

2 Answers2

1

It could be a number of things. Without being able to see the code, I would suggest starting in two places:

  1. The SQL in your file is malformed, and needs to be edited to include a closing quotation mark somewhere in your INSERT. Line numbers aren't always concrete in errors, however, start at or near the line number it gave you.
  2. One or more of your data values has an apostrophe that needs to be escaped. Again, start near the line number it gave you.

In either case, you'll need to edit your \script.sql file to fix this issue.

Edit

Ultimately, you need to edit this script, or create a new one. If you decide to re-roll this script, you'll want to try and add text qualifiers. I'm not sure where your data set is coming from, but that will need to be caught prior to running this.

If you can't re-generate the script, then you have to find a way to open it. There's no working around this problem. It's buried in that script, and it will not successfully fire until it is fixed.

Phoenix
  • 1,881
  • 5
  • 20
  • 28
  • I understand the issue and would ideally escape quotation mark but the problem is I can't open the file and locate the row as the file is huge in size – rumi Mar 06 '15 at 21:33
  • Describe huge in size. Line 39, if that is close to accurate, shouldn't be hard to find. You could also open it in a text editor which might help. I routinely work with files 100k records in size or more in Notepad++ without issue. – Phoenix Mar 06 '15 at 21:33
  • The file size is 4.6 Gb and none of the editors is able to open it – rumi Mar 06 '15 at 21:43
  • Yeah that's rough. You could always try splitting it: http://superuser.com/questions/94083/how-to-split-large-file-on-windows although I wouldn't recommend that, as I do not think it respects line breaks. Can you re-create this script to include text qualifiers? Is it absolutely necessary to include schema `and` data in the same script? Afterthough: WordPad will open files up to 9GB I believe, although very slowly. – Phoenix Mar 06 '15 at 21:48
  • 1
    Try Notepad ++ to see if that will open the file. Not opening the file is not an option. – logixologist Mar 06 '15 at 21:53
  • Noetpad++ won't open it either. Its too big for it too. – rumi Mar 06 '15 at 21:59
  • Added an edit. You've got two options that I can see. Re-generate this script, or find an editor/machine that can handle opening the script you currently have. – Phoenix Mar 06 '15 at 22:03
  • I m following the solution given on this question http://stackoverflow.com/questions/431913/how-do-you-import-a-large-ms-sql-sql-file and just wondering if there is a parameter I can try which can skip these rows – rumi Mar 06 '15 at 22:05
  • You still don't know that the data rows are the issue. It could very well be that the generated dynamic sql is missing a closing quotation. It *could* be an escape character or it *could* be your generated sql. In either case, passing a parameter without knowing what to replace or remove could cause more problems. – Phoenix Mar 06 '15 at 22:09
0

The sql cmd process the sql commands in pieces. It works with streams - read some bunch of data, process it, after that take next bunch of data process it as well and so until the end of the file. If you do not specify the amount of data to be processed at once it takes 4096 bytes only, and if the bunch of sql query is bigger - the error "Unclosed quotation mark after the character string" is occured. Take into consideration the -a parameter, for instance: sqlcmd -s SERVER -a DATATOBEPROCESSEDATONCE -u USERNAME -p PASSWORD -i "FILEPATH". Set DATATOBEPROCESSEDATONCE at 32767.

Радослав Тодоров
  • 69
  • 6