在SQL上连接ORCAL数据库正常，查询数据时报错

[ypshisz]

我在SQL上建立链接服务器连接ORCAL数据正常，查询也正常，后来在ORCAL中的GETACCREDITDATA增加了一个LASTACCREDITTIME字段，日期型后就报如下错误

查询语句
select sn,stbmac,cmmac,nuid,LastAccreditTime from link_orcal..CPEA.GETACCREDITDATA


服务器: 讯息 7354，层级 16，状态 1，行 1
OLE DB Provider 'MSDAORA' 提供数据行 'LASTACCREDITTIME' 无效的中继数据 (Metadata)。The data type is not supported.
OLE DB 错误追踪 [Non-interface error: Column 'LASTACCREDITTIME' (ordinal 5) of object '"CPEA"."GETACCREDITDATA"' reported an unsupported value for DBTYPE of 13]。

[「已注销」]

OLE DB Provider 'MSDAORA' 提供数据行 'LASTACCREDITTIME' 无效的中继数据 (Metadata)。The data type is not supported.
=============
错误不是告诉你了么
The data type is not supported.

[xman_78tom]

试用 openquery 函数查询

select * from openquery(link_orcal,
'select sn,stbmac,cmmac,nuid,to_char(LastAccreditTime,'yyyymmdd') from CPEA.GETACCREDITDATA');

[xman_78tom]

-- 改一下
select * from openquery(link_orcal,  
 'select sn,stbmac,cmmac,nuid,to_char(LastAccreditTime,''yyyymmdd'') from CPEA.GETACCREDITDATA');

[ypshisz]

高手帮我调下这个联合查询
sqlstr = "select box.scph,box.box,box.boxchild,box.dvr,box.date,box.boxchild1,box.mac,box.id,box.pellet, b.sn,b.stbmac,b.cmmac,b.nuid,c.power_supply from box INNER JOIN link_orcal..CPEA.GETACCREDITDATA b ON box.id=b.sn inner join process c ON box.dvr=c.dvr where scph='" & Text3.Text & "'"

[「已注销」]

语法没有问题：

SELECT X.SCPH,X.BOX,X.BOXCHILD,X.DVR,X.DATE
       ,X.BOXCHILD1,X.MAC,X.ID,X.PELLET, B.SN,B.STBMAC
       ,B.CMMAC,B.NUID,C.POWER_SUPPLY 
FROM BOX X ----学会用笔名
INNER JOIN LINK_ORCAL..CPEA.GETACCREDITDATA B 
ON X.ID=B.SN 
INNER JOIN PROCESS C 
ON X.DVR=C.DVR 
WHERE SCPH='YYYYY'----传参

[ypshisz]

我在执行
SELECT X.SCPH,X.BOX,X.BOXCHILD,X.DVR,X.DATE
,X.BOXCHILD1,X.MAC,X.ID,X.PELLET, B.SN,B.STBMAC
,B.CMMAC,B.NUID,C.POWER_SUPPLY
FROM BOX X ----学会用笔名
INNER JOIN LINK_ORCAL..CPEA.GETACCREDITDATA B
ON X.ID=B.SN
INNER JOIN PROCESS C
ON X.DVR=C.DVR
WHERE SCPH='YYYYY'----传参

会报[Non-interface error: Column 'LASTACCREDITTIME' (ordinal 5) of object '"CPEA"."GETACCREDITDATA"' reported an unsupported value for DBTYPE of 13]。

3楼的朋友的
select * from openquery(link_orcal,
'select sn,stbmac,cmmac,nuid,to_char(LastAccreditTime,''yyyymmdd'') from CPEA.GETACCREDITDATA');

这句解决了这个问题，可以查出数据，
我想将
select * from openquery(link_orcal,
'select sn,stbmac,cmmac,nuid,to_char(LastAccreditTime,''yyyymmdd'') from CPEA.GETACCREDITDATA');
和我的
SELECT X.SCPH,X.BOX,X.BOXCHILD,X.DVR,X.DATE
,X.BOXCHILD1,X.MAC,X.ID,X.PELLET, B.SN,B.STBMAC
,B.CMMAC,B.NUID,C.POWER_SUPPLY
FROM BOX X ----学会用笔名
INNER JOIN LINK_ORCAL..CPEA.GETACCREDITDATA B
ON X.ID=B.SN
INNER JOIN PROCESS C
ON X.DVR=C.DVR
WHERE SCPH='YYYYY'
结合起来做一个联合查询，请高手指点

[「已注销」]

这还不容易嘛：

SELECT X.SCPH,X.BOX,X.BOXCHILD,X.DVR,X.DATE
  ,X.BOXCHILD1,X.MAC,X.ID,X.PELLET, B.SN,B.STBMAC
  ,B.CMMAC,B.NUID,C.POWER_SUPPLY 
FROM BOX X ----学会用笔名
INNER JOIN (select * from openquery(link_orcal,  
 'select sn,stbmac,cmmac,nuid,to_char(LastAccreditTime,''yyyymmdd'')
from CPEA.GETACCREDITDATA')) B 
ON X.ID=B.SN 
INNER JOIN PROCESS C 
ON X.DVR=C.DVR 
WHERE SCPH='YYYYY'

[chosenboa123]

试用 openquery 函数查询