sql server与openquery使用计数（*）对sqlite

Question

我有sqlite数据库连接通过sql server中的链接服务器。 下面的查询工作得很好：

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

但此查询将返回一个错误：

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

错误是：

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.

出了什么问题，如何解决？

Answer 1

错误消息中的数据类型为解决问题提供了线索。从Microsoft doc：

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

无论出于何种原因，在编译时假设查询返回一个字符串。

尝试要么返回字符串等，预计（符合一个怪癖通常是最快的路径中的溶液）：

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

或尝试一个明确的返回类型，在情况下，选择32位整数框架不能处理64位整数：

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

---

然而，“不工作”另一个微软的东西例子。