Microsoft SQL Server forum - SET FMTONLY being added to in-line SQL

Author

SET FMTONLY being added to in-line SQL

teddysnips@hotmail.com

2005-04-25, 7:23 am

Here's the deal.

I've been working on an Intranet application for my clients, and today
I 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 the
application on their intranet server, and made the necessary changes to
the web.config and other configuration files.

I logged on with no problem. The default form is a Search/Finder form
with no default recordset. I fired up the filter to return all the
records 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 trace
on 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 not
data rows. The SQL which SHOULD have been sent, and which I have just
captured in the SQL Profiler on MY system here is:

SELECT fldID, fldReferenceNumber, fldRevision, fldPartNumber,
fldIndentNumber, fldDescription, fldBatchNumber, fldCreatedDate FROM
tblMyTableWHERE (((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 think
it might have something to do with ownership/permissions, but I
couldn't find anything different about this database than the system on
which this new application was modelled, which has no such problems.

Edward

Erland Sommarskog

2005-04-25, 8:24 pm

(teddysnips@hotmail.com) writes:
> I've been working on an Intranet application for my clients, and today
> I went and installed the first prototype. It's a fairly standard thing
> - VS2003 ASP.NET/VB.NET on a SQL Server 2000 database.

Which .Net provider do you use? Unless you have reqiurements to be
portable between different DB engines, I recomnmend that you use
SqlClient.

> I logged on with no problem. The default form is a Search/Finder form
> with no default recordset. I fired up the filter to return all the
> records 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 trace
> on 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 not
> data rows. The SQL which SHOULD have been sent, and which I have just
> captured in the SQL Profiler on MY system here is:

The SET FMTONLY stuff is generated behind your back by the data
provider to get the metadata information. Normally it should be
followed by the real thing. The fact that it doesn't indicates that
the SELECT statement fails already with FMTONLY ON. Since you are
on a new environment, my guess goes for a permissions issue.

This should throw an exception. Classic ADO would be silent about
such an error, but experience is that the .Net Data providers do
not commit this sin. You may need to review your error handling code
so that you are not dropping erros on the floor.

Why the WHERE clause was not incluced in the SET FMTONLY bit, I don't
know, but then again I have not seen your client code.

I've set followups to comp.databases.ms-sqlserver only.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Edward

2005-04-27, 11:23 am

Erland Sommarskog <esquel@sommarskog.se> wrote in message news:< Xns9643F2315F48CYazo
rman@127.0.0.1>...
> (teddysnips@hotmail.com) writes:
>
> Which .Net provider do you use? Unless you have reqiurements to be
> portable between different DB engines, I recomnmend that you use
> SqlClient.
>
>
> The SET FMTONLY stuff is generated behind your back by the data
> provider to get the metadata information. Normally it should be
> followed by the real thing. The fact that it doesn't indicates that
> the SELECT statement fails already with FMTONLY ON. Since you are
> on a new environment, my guess goes for a permissions issue.
>
> This should throw an exception. Classic ADO would be silent about
> such an error, but experience is that the .Net Data providers do
> not commit this sin. You may need to review your error handling code
> so that you are not dropping erros on the floor.
>
> Why the WHERE clause was not incluced in the SET FMTONLY bit, I don't
> know, but then again I have not seen your client code.
>
> I've set followups to comp.databases.ms-sqlserver only.

I eventually traced the problem to a "torn page" in one of the data
tables. It wasn't a problem if I did
SELECT * FROM tblMyTable
but failed if I did
SELECT COUNT(*) FROM tblMyTable

I've now successfully restored the database and it works correctly.
You are right, incidentally, in flagging the exception handling. It's
woeful in this particular class, but sadly I've no time or money to
revisit at present.

Thanks

Edward

Erland Sommarskog

2005-04-27, 8:24 pm

Edward (teddysnips@hotmail.com) writes:
> I eventually traced the problem to a "torn page" in one of the data
> tables. It wasn't a problem if I did
> SELECT * FROM tblMyTable
> but failed if I did
> SELECT COUNT(*) FROM tblMyTable
>
> I've now successfully restored the database and it works correctly.

Ah! That was a little difficult to tell from a distance. I am glad to
hear that you were able to track down the problem. Thanks for taking
the time to report back!

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Sponsored Links