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 3 7098
(te********@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, es****@sommarskog.se
Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinf...2000/books.asp
Erland Sommarskog <es****@sommarskog.se> wrote in message news:<Xn**********************@127.0.0.1>... (te********@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.
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
Edward (te********@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, es****@sommarskog.se
Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinf...2000/books.asp This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Celebrate |
last post by:
I'm noticing that when my homepage first starts up, all the menu links
to other pages have the session info appended to the URL. For example:
...
|
by: Hal Halloway |
last post by:
Not sure if this is a php, mysql, or apache issue. I use win2k and I
have AMP on my system as a localhost. I use this to test PHP/MYSQL
scripts I write on my local PC.
I render a URL w/PHP by...
|
by: Xif |
last post by:
Hi
I want to have control over all items added to a list.
The first attempt was to subclass list and override its .append()
method.
Problem is, there are plenty of other ways the list can...
|
by: Martin |
last post by:
I run into a problem where an ASP CheckBoxList control disappears after being
added to a controlcollection. (Have not tested this with other Controls ...)
It takes a WebForm with 3 lines of codes...
|
by: Marcin Wolku |
last post by:
hi i have a question how can i delete last added row. I have 2 tables .
source and destination . I take a 1 row from source table , do some
operation on it and save to destination table . after...
| |
by: Johnny Ruin |
last post by:
Hi, Running a trace while operating my application (written in C++
w/SQLDataProvider) I'm seeing all my queries wrapped in SET FMTONLY
OFF; SET NO_BROWSETABLE ON;my query here;SET NO_BROWSETABLE...
|
by: Simon Harvey |
last post by:
Hi everyone,
I'm having a really weird problem with one of my pages. Whenever I hit the
submit button, it reloads the page but another side menu control appears
underneath the original one. This...
|
by: JayCallas |
last post by:
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...
|
by: kavin |
last post by:
Hi guys..
apart from the day-to-day monitoring, administring and perf tuning
activities, what are all the value added things than can be done by a
DB2 DBA?
|
by: Sreekanth |
last post by:
I am trying to update my data using tableadapters. I have table1 and
table2. table2 has a foreignkey reference to table1.I see that table2
has one new row (i = 2) but after table1 update, I read i...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
| |
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and...
| |
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The...
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |