Thursday 29 January 2009

Another great error message from MS

Spent the last 2 hours trying to diagnose an error message.  I am writing a batch file which simply executes a SQL script using sqlcmd.  This is the command:

sqlcmd -S %SQLServerName% -E -i InstallSql.sql > InstallSql.txt

For the life of me, I have no idea what is going wrong.  The error message is incredibly useful

Sqlcmd: Error: Microsoft SQL Server Native Client 10.0 : Syntax error, permission violation, or other nonspecific error

Ok, so it could be one thing, or maybe it’s another, oh actually it could be anything at all really.  Essentially they're saying YOU ARE ON YOUR OWN WITH THIS ONE, trial and error is the only way.

Anyone got any ideas what this could be? I’m gonna throw my laptop out of the Window soon!

4 comments:

Anonymous said...

I just hit this myself and I think that my batches were too big and caused the error. I added a couple fields to a scripted INSERT INTO program and I must have exceeded some internal buffer. I reduced the number of commands in each batch and the problem went away.

Anonymous said...

I agree that this is a sucky message from M$. I have also found that this is a buffer overflow problem and seems to relate to the number of lines in the script file and not the size of any one batch (between GO statements). There doesn't seem to be a work-around except to split the script file into a number of files.
Geoff.

Anonymous said...

After much trial and error removing lines from a 13000 line script that was failing with this error message in OSQL, I finally put my finger on the issue.

If your script contains
/* ... */ ... { ... }
in the order shown where ... can be any characters inside a comment block, it will fail with the message
[SQL Server Native Client 10.0]Syntax error, permission violation, or other nonspecific error

It seems that there is a bug in the OSQL parser that doesn't handle a block comment followed by {}

I often place a block comment around code containing the following to remove it temporarily from a patch, and the whole script fails.

/*
declare @s varchar(2000)
set @s = '
/* blah blah */;
macro()
{
$a:=1
};
*/

Frustrating :-(

Geoff

Neil Mosafi said...

Wow two years later someone finally comes up with the solution. Nice! ;-)