sql server with openquery using count(*) against sqlite

Question

I have sqlite database connected through linked server in sql server. Following query works just fine:

select *
from openquery(
    eod, 
    'select id, min(dt) as mindt, max(dt) as maxdt from [tdata] group by id'
)

But this query returns an error:

select *
from openquery(
    eod,
   'select COUNT(*) as cnt from [tdata]'
)

Error is:

The OLE DB provider "MSDASQL" for linked server "eod" supplied inconsistent metadata for a column. The column "cnt" (compile-time ordinal 1) of object "select COUNT(*) as cnt from [tdata]" was reported to have a "DBTYPE" of 129 at compile time and 3 at run time.

What could be wrong and how to fix it?

Answer 1

The data types in the error message give clues to solve the problem. From the Microsoft doc:

DBTYPE_I4 = 3, A four-byte, signed integer: LONG
DBTYPE_STR = 129, A null-terminated ANSI/DBCS character string

For whatever reason, at compile time the query was assumed to return a string.

Try either returning a string like it expects (conforming to a quirk is usually the fastest path to a solution):

select cast(COUNT(*) as varchar(20)) as cnt from [tdata]

or try an explicit return type, choosing a 32-bit integer in case the framework can't handle a 64-bit integer:

select cast(COUNT(*) as int) as cnt from [tdata]

---

Yet another example of Microsoft stuff "not working".