I am having a problem with "SET fmtonly ON" and a function I
implemented in my database. (The function is actually Erland's
delimited string to tmp table function for purposes of passing in
multiple values to a stored procedure.)

The stored procedure which uses the function is used to return a
resultset. But sometimes the stored procedure is called with bogus
values (preceded by "SET fmtonly ON") to get the column names. And this
is where the problem happens.

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.)

I created some sample code to prove this.

--SET fmtonly ON

DECLARE @flag INT
SET @flag = 0

IF 0 > 0 -- always false
SELECT @flag = 1 -- should never execute

SET fmtonly OFF

PRINT 'DEBUG | ' + LTRIM(STR(@flag))

If executed as is the code executes correctly and the value printed is
"0". But if fmtonly is ON then for whatever reason 0 > 0 evaluates to
true and the @flag variable gets set to "1".

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.

(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/produc...9-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...b;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/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

So FMTONLY should be avoided. Is there an alternative?

For straight SELECTS against tables you could just do "SELECT * FROM
[Table] WHERE 1 = 0" but what about stored procdures? Obviously, with a
not-so-short stored proc I would not want to run the full query just to
get the schema.

(JayCallas@hotmail.com) writes:
> So FMTONLY should be avoided. Is there an alternative?
>
> For straight SELECTS against tables you could just do "SELECT * FROM
> [Table] WHERE 1 = 0" but what about stored procdures? Obviously, with a
> not-so-short stored proc I would not want to run the full query just to
> get the schema.

If you for some reason want only the structure of the result set, then
SET FMTONLY ON may be the best bet. But it is definitely a gamble, since
a stored procedure could be written to generate different result sets
depending on weekday, phase of the moon etc.

Best is to write the code so that it adapts to the result set of a
particular execution. Particularly if the stored procedures can be anything.
(FMTONLY would be OK if you know that the procedure are simple-minded
things without IF or nested SP calls.)

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

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

So then I am screwed... Bummer.

The stored procedure is simple enough but it uses that function to JOIN
against the comma-separated turned temp table... which throws the
error.

(The reason we use FMTONLY to get the schema is because we do not want
to roll out a new version of the app each time we add/remove a column
from the resultset. An add-in to Excel that we wrote allows the users
to drop data into a spreadsheet. Using the schema, we give the user the
ability to select which columns they want to see and in which order.)

A workaround seems to be to make sure that the "sample" list passed to
the stored proc contains at least two values... (need to have that
comma in there to keep the error from happening..)

JayCallas@hotmail.com wrote:
> So then I am screwed... Bummer.


I am not sure if your options would include this but please do check
out CampaignRunner. It does what you are trying to accomplish easily
and across a number of different systems.

Sincerely,



> The stored procedure is simple enough but it uses that function to JOIN
> against the comma-separated turned temp table... which throws the
> error.
>
> (The reason we use FMTONLY to get the schema is because we do not want
> to roll out a new version of the app each time we add/remove a column
> from the resultset. An add-in to Excel that we wrote allows the users
> to drop data into a spreadsheet. Using the schema, we give the user the
> ability to select which columns they want to see and in which order.)
>
> A workaround seems to be to make sure that the "sample" list passed to
> the stored proc contains at least two values... (need to have that
> comma in there to keep the error from happening..)

(JayCallas@hotmail.com) writes:
> So then I am screwed... Bummer.
>
> The stored procedure is simple enough but it uses that function to JOIN
> against the comma-separated turned temp table... which throws the
> error.
>
> (The reason we use FMTONLY to get the schema is because we do not want
> to roll out a new version of the app each time we add/remove a column
> from the resultset. An add-in to Excel that we wrote allows the users
> to drop data into a spreadsheet. Using the schema, we give the user the
> ability to select which columns they want to see and in which order.)
>
> A workaround seems to be to make sure that the "sample" list passed to
> the stored proc contains at least two values... (need to have that
> comma in there to keep the error from happening..)

Or try to change the function so it does not blow up with FMTONLY
on...



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

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx