2

I am currently doing an internship and I received a 42 gb ms sql-server import script. I tried to import the script using the ms SQLCMD tool all at once. It ran for 30 minutes and then I got the following error:

msg 102 level 15 state 1 line 37 incorrect syntax near '7500'

So naturally I checked line 37 and searched for the value 7500 but I could not find it. Then I tried to find the last executed query in my server log. Having found the last inserted query, I knew what line in my script was causing the error.

Unfortunately I could not find the value 7500 in the error causing line. And the next 3 "batches" imported just fine...

So I cleared my database, and tried to import the script a second time with the exact same error as result.

So I tried to split the 42 gb script into 3000 smaller scripts.

I am now running the 3000 scripts sequentially and it seems to be running just fine. It has been running for 4 hours now and it is past the point of the previous error.

My question is: Did I find a bug in SQLCMD or is this a known problem with larger import scripts? If this is a know problem what is the best way to counter this (Splitting the file took a long time)

Thanks.

Improve this question
4
  • Can you add the offending script section?
    – LowlyDBA - John M
    Commented Mar 11, 2015 at 14:42
  • Well, the problem is, there is non. The slitted script ran fine. There was no output on what line the script failed other than on line 37, the script is about 1,300 000 000 lines long and it ran for half an hour before outputting the error. The lines consisted out of import lines. so I cant imagine that the script actually was at line 37.
    – Jelle de Fries
    Commented Mar 11, 2015 at 17:04
  • Maybe CTRL+F for 7500? Hard to diagnose a problem if we can't see where the issue is.
    – LowlyDBA - John M
    Commented Mar 11, 2015 at 17:33
  • Well, the file is 42 gb and a search for 7500 resulted in a lot of hits. The splitting the file approach worked. This makes it more likely that there was no problem with the syntax and there is some problem with the combination sqlcmd, my desktop and large files.
    – Jelle de Fries
    Commented Mar 11, 2015 at 18:25

1 Answer 1

Reset to default
1

It's possible that you hit SQL's size limit for batch size. Check out https://msdn.microsoft.com/en-us/library/ms143432.aspx.

If your file is just line after line of INSERT INTO [Table] (field, field) VALUES (value, value); then you have some better options than splitting the file.

  1. Add GO statements every few thousand lines, which helps break things up into smaller batches.
  2. Use Powershell or another language to create a stream reader send every line to sqlcmd. Chrissy LeMaire created a great script to do this for CSV files that you could use for inspiration: https://gallery.technet.microsoft.com/scriptcenter/Import-Large-CSVs-into-SQL-216223d9. I've used a variation on this method to import files with a half-million INSERT statements in less than a minute.
Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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