How to get an empty table from a sql query in VB 2005 (.net) without timeouts

Its simple – write a select that guarantees the return of nothing.  You will need to know your data.  In my case this is for adding rows to a log table.  The rows are timestamped when they are entered, so I had:

SELECT [Time], [Message] FROM [LogTable]

where [Time] > {fn Now() }

It worked.

Then it began to time out.

Every time the SELECT was run a table scan occurred.  The log now has just over 500,000 rows.

I removed some to a history table, down to 100,000, but still no good.

The execution plan says table scan, so that is the obvious cause.  I soon discounted indexes, the table is rarely read, and I’m prepared for the table scan when it is, as the select is likely to include [Message] Like ‘%something%’

Then I tried

SELECT [Time], [Message] FROM [LogTable]

where 1 = 2

the execution plan says ‘constant scan’, and the query analyser reports 00:00:00 as the execution time.

FIXED!

Implicit conversion of char value to char cannot be performed because the collation of the value is unresolved due to a collation conflict.

This problem occurs when the referenced database table field has a different collation sequence to the field being tested.

Surprisingly, for me the usual remedy to this type of problem applying “Collate Database_Default” to the other database did not resolve the issue.

Neither did applying it to the local one – but that’s not surprising.

Both the fields being compared were referenced through views stored in the local database (DB), but referencing tables in an external DB.

Even though there were no errors in the views, applying the ‘Collate Database_Default’ in the views resolved the issue.