Fmtonly Problem (bug ??)

Apr 13, 2006

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.

View 6 Replies


ADVERTISEMENT

Odd FMTONLY Statements

Jul 21, 2006

Hi,
I recently started using ADO
for connecting to a remote SQL Server from Delphi.
Prior to this I used BDE (Borland Database Engine).



I noticed that all SQL communication seemed slow €“ only
half the speed of using BDE!

I ran a trace using the Profiler (from MS SQL Server)
and noticed that ADO
generated roughly twice the amount of traffic compared to BDE. Every SQL statement (SELECT,
INSERT and UPDATE) send from my application was preceded by the exact same
statement encapsulated in €˜SET FMTONLY ON/OFF€™.



-Example Start-

SET FMTONLY ON SELECT * FROM TabelVersion SET FMTONLY
OFF

SELECT * FROM TabelVersion

-Example End-



Why are these odd statements being passed to the
server?

Shouldn€™t the provider be able to handle the returned
dataset without sending the FMTONLY statement in advance? And if not, why doesn€™t
it cache this information?



Is it possible to minimize this traffic?



I also spotted quite a lot €˜SET NO_BROWSETABLE ON/OFF€™
statements. What is the use for these?



I found this article (http://support.microsoft.com/kb/836830/en-us)
on MSKB, but I should already have the hotfix for this installed by MDAC 2.8 SP1!


This slow pace is killing me €“ please help - egeskov



My configuration:

Workstation:

WinXP SP2

MDAC 2.8 SP1
(2.81.1117.0)

ConnectionString=€™Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist
Security Info=False;Application Name=PD:KE;Data Source=SMIL;Initial
Catalog=PDDebug€™

Server:

Windows
Server 2000
SP4

MS SQL
Server 8

View 12 Replies View Related

SET FMTONLY Being Added To In-line SQL

Jul 23, 2005

Here's the deal.I've been working on an Intranet application for my clients, and todayI went and installed the first prototype. It's a fairly standard thing- VS2003 ASP.NET/VB.NET on a SQL Server 2000 database.I restored the database onto their db server, and installed theapplication on their intranet server, and made the necessary changes tothe web.config and other configuration files.I logged on with no problem. The default form is a Search/Finder formwith no default recordset. I fired up the filter to return all therecords and the thing crashed. The filter form wouldn't get any data(there *was* data in the database - I checked that!). So I set a traceon the database and this is the SQL that was being sent to the server(note that this SQL is dynamically built and is being sent in-line):SET FMTONLY OFF; SET FMTONLY ON;SELECT fldID, fldReferenceNumber,fldRevision, fldPartNumber, fldIndentNumber, fldDescription,fldBatchNumber, fldCreatedDate FROM tblMyTable SET FMTONLY OFF;Note those SET FMTONLY OFF; SET FMTONLY ON;...SET FMTONLY OFF;directives. These direct SQL Server to return only metadata, and notdata rows. The SQL which SHOULD have been sent, and which I have justcaptured in the SQL Profiler on MY system here is:SELECT fldID, fldReferenceNumber, fldRevision, fldPartNumber,fldIndentNumber, fldDescription, fldBatchNumber, fldCreatedDate FROMtblMyTableWHERE (((tblMyTable.fldReferenceNumber LIKE N'%')))Again, note that the WHERE clause is missing from the first lot of SQL.Does anyone have any ideas about why this might be happening? I thinkit might have something to do with ownership/permissions, but Icouldn't find anything different about this database than the system onwhich this new application was modelled, which has no such problems.Edward

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved