ADO Bug: do NOT use comments in parameterized sql queries in ADO/OLEDB!

Yesterday I finally found the cause of a problem in our middle ware service: after 1 hour the service did not return any results from the database anymore. The problem was using a sql comment -- in a query within a report...

Today I did some more investigation and found out WHY we got no results but also no errors.

For example, we could create a very simple query like this:

select *
from (select 1 as value) as dummytable
where value > ?

When we prepare this query in a ADO query (e.g. TADOQuery in Delphi) it will send the following sql to the server to determine the types (used to determine parameter type):

 set fmtonly on select v from  (select 1 as v) as t where 1=2 set fmtonly off

So it removes the "where" clause, replaces it with "where 1=2" and surrounds it with "set fmtonly on/off". This "fmtonly" thing instructs SQL Server to return only metadata.

So far, so good.
But when you use "--" comments within your sql (which is normally no problem) in combination with parameters, you're getting big problems! Take a look at what ADO (or OLEDB?) does with the next statement:

select *
from (select 1 as value) as dummytable
--dummy comment
where value > ?

When you prepare this query, you will see the next sql statement in SQL Server Profiler:

set fmtonly on select v from  (select 1 as v) as t
--dummy comment where 1=2 set fmtonly off

Can you spot the problem? Now the "set fmtonly off" won't be executed! So your connection stays in a "metadata only" state! And so, all queries that you execute after this won't return any results!

Conclusion: DO NOT USE COMMENTS IN PARAMETERIZED QUERIES!