SQLCMD.exe exiting with syntax error where not syntax error exists

Question

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.

All anybody can do is guess as to why this might be occurring. The code you posted won't generate that error. I would hope you don't really have thousands of lines of code with no formatting like this. Do you have GO in your actual file? That would generate a syntax error because GO is NOT a t-sql statement. It is the default batch terminator in SSMS. — Sean Lange
– Sean Lange, Commented Oct 30, 2018 at 18:40
@SeanLange Huh? Sqlcmd.exe understands GO. SSMS inherited that syntax from sqlcmd. That's why SSMS has "SQLCMD mode" for the expanded syntax. Indeed, without the GO this will run into the batch size limit. — Bacon Bits
– Bacon Bits, Commented Oct 30, 2018 at 18:49
The only limitation to file size I'm aware of for sqlcmd.exe is that, at least in certain editions, there was a 2 GB limitation to the file due to to the program being 32-bit. The only way to really tell what's going on here would be to use extended events or profiler to see what sqlcmd is actually executing. — Bacon Bits
– Bacon Bits, Commented Oct 30, 2018 at 19:07
Exactly how far into the file does the error occur? In the first megabyte? 256MB in? — Bacon Bits
– Bacon Bits, Commented Oct 30, 2018 at 19:08

Bacon Bits · Accepted Answer · 2018-10-31 17:01:02Z

3

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.

answered Oct 31, 2018 at 17:01

Bacon Bits

32.5k6 gold badges63 silver badges79 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

K. Meke Over a year ago

The -x parameter did the trick for me because some of the text I was trying to update apparently contained '$(' which triggered variable substitution.

Collectives™ on Stack Overflow

SQLCMD.exe exiting with syntax error where not syntax error exists

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related