I am running a moderately large script (about 900MB) with SQLCMD.exe and I am running into an odd situation.
The script consists of several thousand insert statements. When I run it, it will run for some time, executing fine, then at some point it will exit with the error:
Sqlcmd: Error: Syntax error at line _SOME_LINE_NUMBER_ in file 'd:\myscript.sql'.
Now, the problem is, there IS no error at that line number (or anywhere else in the script). If I go to the given line and grab the statement block in which it is contained, it will run no problem in SSMS. I usually go to the file and truncate it such that it starts at the statement statement that SQLCMD didn't like (actually, I start a couple statements before it) - i.e. I remove the stuff that has already run. I run the new sql file with SQLCMD and the whole process repeats itself with the same error referencing a different line (the original line that it complained about causes no problems this time).
If I re-run the script without editing it (cutting out the already processed parts), then it seems to fail on the same line number every time I re-run it. Again, there is actually no syntax error.
This is an example of the several thousand statement blocks in the file:
DECLARE @tblOutput table (ID int);
BEGIN TRANSACTION
IF NOT EXISTS (SELECT * FROM MyTable WHERE ID=123)
BEGIN
INSERT INTO MyTable(Col1, Col2, Col3, Col4, Col5, Col6, Col7, Col8, Col9)
OUTPUT INSERTED.ID INTO @tblOutput(ID)
VALUES(1, '2', '3', '4', 5, 6, (SELECT TOP 1 ID FROM SomeOtherTable WHERE Name='Something'), (SELECT TOP 1 ID FROM YetAnotherTable WHERE Name='321'), 9 )
INSERT INTO MyTable2(Col1, Col2, FKID)
VALUES(1, 0, (SELECT TOP 1 ID FROM @tblOutput) )
END
IF NOT EXISTS (SELECT * FROM RelatedTable WHERE stuff='something')
BEGIN
INSERT INTO RelatedTable(Col1, Col2)
VALUES ('test', (SELECT TOP 1 ID FROM @tblOutput) )
END
COMMIT TRANSACTION
DELETE FROM @tblOutput
GO
Running SQLCMD.exe as follows:
SQLCMD.EXE -S localhost -d mydatabase -V 17 -i dataimport.sql -o dataimport.log
Can anybody provide a guess as to why this might be occurring? I thought it might be memory-related (I'm running this on a local instance that only had 512MB allocated to SQL Server) so I bumped available memory up to 2GB but with no change in outcome.
FYI, this is SQL Server 2014 and I run the script with -V 17 (which has no effect I guess because a syntax error is sufficiently high error level?).
Thanks in advance.
To summarize from my comments:
Verify the command line options you're using for sqlcmd.exe. Note that the options are case sensitive. You should be able to use -b -e to tell sqlcmd to stop executing when it encounters an error and to also echo the command that it's trying to run.
You should also consider the -x (disable variable substitution) and -X (disable script commands) parameters. If data you're inserting potentially contains data that looks like an sqlcmd substitution variable, $(variable), you will need to specify -x to disable that feature.