Posted by Erland Sommarskog on 04/14/06 00:50

(JayCallas@hotmail.com) writes:
> If fmtonly is ON and the function is called with the list containing a
> single value (with no delimiters) (as in 'item1' versus 'item1,item2')
> then SQL throws a "Invalid length parameter passed to the substring
> function" error.
>
> After much testing it seems that certain parts of code in the function
> gets IGNORED. (In this case it is the WHILE loop condition check.)
> (Happens with IF statements also.)
>...
> While this seems to be by design (I tested it on SQL 2000 sp3, sp4, and
> SQL 2005 with the same results) this just seems silly to me.

You are not going to find any disgreement with me on that one!

FMTONLY is really a bad hack. It's a sort of NOEXEC mode, but EXEC
statements are carried out and so are variable assignments. The result
of conditions in IF statements is ignored, instead both IF and ELSE
branches are run through. There are a couple of false errors you can
run into. The most obvious is maybe hitting nestlevel when you have
recursive stored procedures. But we also ran into a similar issue
where variable assignment caused code inside a WHILE loop to bomb,
although according to the logic, the WHILE loop would never be
entered.

You can see a bug report of mine on
http://lab.msdn.microsoft.com/productfeedback/viewfeedback.aspx?feedbackid=be34e9cc-c32c-4dbe-a429-0f24867ad6c6
there is a non-commital answer in the Discussion section at the bottom
of the page.

The cure is try to avoid FMTONLY on. Alas some client APIs are quite
fond of spewing it around, most noticably ADO. And ADO has one more
bug which is even more horrible: if there is an error in the FMTONLY
phase, it drops the error on the floor. Now, assumed that you had
started a transaction, and the error aborts the batch, and thus rolls
back the transaction. The client is not made aware of this, and
continues as if it hadn't happened! All the ADO team has been able
to produce is
http://support.microsoft.com/default.aspx?scid=kb;en-us;810100. But
no fix of this serious problem.



--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]