Re: WHERE 1=2 with performance nightmares

Sarah schrieb:
Recently we started seeing this "WHERE 1=2" in sql profiler trace that even makes a lot of our sql statements invalid when there is *already* a WHERE clause in our sql.

Here is an example copied from a trace file:
SET FMTONLY ON SELECT Col1,Col2 from Table1 WHERE Col1 = 8 and Col2 = 6
WHERE 1=2 SET FMTONLY OFF

I don't know why you get a second WHERE clause. Do you really get the WHERE keyword twice?

SET FMTONLY queries on SQL Server are normal.
They are used by the OLEDB Provider to retrieve Parameter information and they return no result set. The Provider normally changes the Statement in a way so that it only returns the parameter columns and that the WHERE will always evaluates to false because an actual result set isn't needed. But i have never seen that it generates invalid SQL.

Your SQL looks like you are not using parameters. If so you should set Paramcheck = false for you ADOQuery/ADODataset. The dataset won't check for parameters then and this will prevent the provider from generating the SET FMTONLY query at all.


--
Ralf Jansen

deepinvent Software GmbH - Viersen, Germany - http://www.deepinvent.com
Archiving E-mails with MailStore: http://www.mailstore.com


.

Relevant Pages

  • Re: Better "Join" vs "Where" clause?
    ... running the SQL directly, in the SQL Server tools, rather than ... WHERE clause has been deprecated, ... AFAIK one cannot perform an outer join in Access without using the explicit ...
    (microsoft.public.access.queries)
  • Thanks
    ... Thanks for "With" clause, ... Why are you formatting data in the back end? ... how to use custom column heading in where clause ... This only works on SQL 2005. ...
    (microsoft.public.sqlserver.programming)
  • Re: Performance degradation with view and "left outer join" vs. "from x, outer y"
    ... Turned Every Which Way But Loose", I reminded the attendees that the ANSI SQL '92 parsing rules REQUIRE that filters in the WHERE clause of a query MUST be processed POST-JOIN in order for an RDBMS to be compliant. ... That means that your filter, c.custid = "AB1234", is applied after all rows of the customer table have been LEFT OUTER JOINed to the my_view VIEW with the results stored into a temp table. ... Now if I use the informix extended outer join syntax: ...
    (comp.databases.informix)
  • Re: Report Parameters - no results
    ... Allen Browne - Microsoft MVP. ... I did use the WHERE clause you suggested, and now I'm getting the "wrong ... > What else can I do to try to correct my sql? ... >> the WHERE clause in your query. ...
    (microsoft.public.access.reports)
  • Re: the "having" clause
    ... Start in the FROM clause and build a working table from all of the ... rest of the containing query. ... e) Go to the SELECT clause and construct the expressions in the list. ... As you can see, things happen "all at once" in SQL, not from left to ...
    (microsoft.public.sqlserver.programming)