Error : Query must return exactly one record...

ccmcbride · Post by **ccmcbride** » Thu 24 May 2007 18:54

The query :
Select min(MaintExp) as MaintExp
from CustEQ
where maintexp null and ActivityCreated = 'false' and expActivity = 'true'

The problem -
it returns 0 records.

how to fix?

Jackson · Post by **Jackson** » Fri 25 May 2007 08:04

Do you have the same result using SQL Server Management Studio or Query Analyzer?
Please try to execute your query using one of these tools. If the result will be the same, please check your query. Otherwise specify the script for creating your database objects and populating tables with data. Also specify the exact SDAC version and the Delphi or C++Builder version that you use.

ccmcbride · Post by **ccmcbride** » Fri 25 May 2007 17:26

I am using Delphi 7 enterprise.
I am using Test version of sDac 4.00.0.7
Microsoft SQL Server: 09.00.2050
Microsoft SQL Native Client: 9.00.2047.00

I can run the query everywhere.
DBMonitor shows it as coming back with 0 records.
My app shows an access violation.
This is happening on more than one query.
In each case, it is coming back with 0 records, can be run in MS SQL server manager, comes back through dbMonitor with no errors, and errors out at the application level.
Being new to both MSSQL and sDac, it may be that I have a setting wrong on my query components.

In fact, in writing this, I change the flag for 'non-blocking' from true to false, and now it is not generating the error.

What is the purpose of non-blocking, and why would it generate the error?

Jackson · Post by **Jackson** » Tue 29 May 2007 09:18

When the NonBlocking option is True, TMSQuery fetch rows in a separate thread, and do not block the main thread. For more information about this property please see the SDAC help. Try to use "maintexp is null" instead of "maintexp null" in your query.

ccmcbride · Post by **ccmcbride** » Tue 29 May 2007 17:59

When the NonBlocking option is True, TMSQuery fetch rows in a separate thread, and do not block the main thread.
-->I understand that. What I don't understand is why it would cause the error.

Try to use "maintexp is null" instead of "maintexp null" in your query
-->But I only want the ones where maintExp has been set.

Jackson · Post by **Jackson** » Wed 30 May 2007 07:46

>>I understand that. What I don't understand is why it would cause the error.
We couldn't reproduce the problem.
Please send us (evgeniym*crlab*com) a complete small test project to reproduce the problem;
it is desirable to use Northwind or Master schema objects, otherwise include definition of your own database objects; don't use third party components.

Also supply us the following information:
- Exact version of Delphi or C++Builder.
- Exact version of SDAC. You can see it in the About sheet of the TMSConnection Editor.
- Exact version of Microsoft SQL Server and OLE DB provider you use. You can see it the Info sheet of TMSConnection Editor.

>>But I only want the ones where maintExp has been set.
Sorry for inaccuracy, I meant "not maintexp is null".