SQLCMD scripting error

Question

i am using SQLCMD to run a .sql file which is 270 MB. The script file (.sql) was generated using Red Gate SQL Data Compare synchronization wizard. I cannot run it from SSMS because of insufficient memory. I log into the server and go to command prompt and it opens up command prompt

C:\Users\USER1>

then i type in

> C:\Users\USER1>SQLCMD -U sa -P PWD -d DATA_FEAT -i F:\SYNC\DATA-DATA_FEAT-20140709.sql -o F:\SYNC\DATA-DATA_FEAT-20140709result.txt

but i get

Sqlcmd: Error: Scripting error.

i am able to use Red gate to synchronize it without error. Red gate runs the same .sql file

Any Help

Thanks

Desiccator

I suspect the file size is a problem. Refer to this question? – Arliearliene

Answer 1

I ran into this with a big script doing a lot of inserts. The solution was over in this other answer: Insert a GO periodically in the file so that everything wasn't being built up in one massive transaction. That answer even got the info from...a RedGate forum thread.

Since I'm using Linux and my file was one-statement-per-line, it was quite easy for me to use sed as outlined in this answer to add GO every few lines, e.g.:

$ sed ': loop; n; n; n; n; n; n; n; n; n; a GO
n; b loop' < bigfile.sql > bigfile2.sql

That inserts a GO every 10 lines (the number of times n appears in the sed script), which is probably overkill.

Nigrescent

SQL Server is now old and sucks unfortunately - so sad we have to resort to this – Centrosome

Great answer, thanks! For Windows-users: an easy way to add the GO statements is using the Macro function of Notepad++. - Click the Record button - Type the following keys: [PgDwn], [Home], [Enter], [Up-key], type GO - Click the Stop button - Click the 'Run Macro multiple times' button - Select 'Run until end of file' and click Run - Remove possible extra GO's and empty line at the end of the file - Save – Janitajanith

@Janitajanith Not if your file is over 3 gigs large :-( – Aldos

Answer 2

I had the same problem with an SQL file with 2.2M inserts.

Adding GO every few lines as suggested above didn't solve it for me I ended up having to split the file into smaller ones of xxx lines and running them seperately using the bash split command as follows: -

split -l xxx largefile.txt newfile.txt

I also have a nicer syntax for the sed solution listed above

sed '50~50i\GO' largefile.txt > newfile.txt

This will insert GO every 50 lines in a file and output the result to a new file.

Hope it helps someone.

Kata