SET FMTONLY being added to in-line SQL

[teddysnips]

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

 

[Peter Proost]

Hi is your login user in the correct role and has the role got the required
select/insert/update/delete permissions on every table? Because sometimes
when I update the database and forget to add the permissions for new tables
I also get errors.

Hth Peter

 

[teddysnips]

Hi is your login user in the correct role and has the role got the required
select/insert/update/delete permissions on every table? Because sometimes
when I update the database and forget to add the permissions for new tables
I also get errors.

Hth Peter

Thanks Peter, but that wasn't the issue. The problem was that the
database was corrupted from a bad backup/restore. I've now fixed it
and it works fine.

Edward

 

[Erland Sommarskog]

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.

 

[Edward]

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]

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!