CODEWITHSUNDEEP
sqlcmd
user176047
user176047

Reputation: 371

SQLCMD scripting error

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

Upvotes: 5

Views: 6126

Answers (2)

tedfire
tedfire

Reputation: 31

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.

Upvotes: 0

T.J. Crowder
T.J. Crowder

Reputation: 1075567

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.

Upvotes: 10

Related Questions