CREATE VIEW AIS_titem
AS
select FNumber,FName,FShortNUmber,FItemClassID from openrowset( 'SQLOLEDB', '192.168.0.150'; 'sa'; 'sa',AIS20130121103113.dbo.t_Item)
--可以
SELECT PartNo,parttype=CASE WHEN parttype='原材料' THEN '外购' WHEN parttype='产品' THEN '自制' END,partName,isnull(unitmeasure,'个'),isnull(partspec,''),isnull(FigureNo,'') FROM parts
WHERE partno IN(SELECT FNumber FROM AIS_titem WHERE FItemClassID=4)
--不可以
SELECT PartNo,parttype=CASE WHEN parttype='原材料' THEN '外购' WHEN parttype='产品' THEN '自制' END,partName,isnull(unitmeasure,'个'),isnull(partspec,''),isnull(FigureNo,'') FROM parts
WHERE partno NOT IN(SELECT FNumber FROM AIS_titem WHERE FItemClassID=4)
--报错提示：
链接服务器"(null)"的 OLE DB 访问接口 "SQLNCLI" 返回了消息 "不能在手动或分布事务模式下创建新的连接。"。
消息 7320，级别 16，状态 2，第 2 行
无法对链接服务器 "(null)" 的 OLE DB 访问接口 "SQLNCLI" 执行查询"SELECT TOP 1 1 FROM "AIS20130121103113"."dbo"."t_Item" "Tbl1006" WHERE "Tbl1006"."FItemClassID"=(4)"。
执行环境如下：
Microsoft SQL Server Management Studio 9.00.4035.00
Microsoft Analysis Services 客户端工具 2005.090.4035.00
Microsoft 数据访问组件 (MDAC) 2000.086.3959.00 (srv03_sp2_rtm.070216-1710)
Microsoft MSXML 2.6 3.0 6.0
Microsoft Internet Explorer 8.0.6001.18702
Microsoft .NET Framework 2.0.50727.42
操作系统 5.2.3790
SELECT * FROM OPENDATASOURCE('SQLOLEDB','Data Source=192.168.30.82;User ID=sa;Password=sa').库.dbo.表 where 1=1
SELECT a.PartNo,parttype=CASE WHEN a.parttype='原材料' THEN '外购' WHEN a.parttype='产品' THEN '自制' END,
a.partName,isnull(a.unitmeasure,'个'),isnull(a.partspec,''),isnull(a.FigureNo,'')
FROM parts a
left join AIS_titem b on a.partno=b.FNumber and b.FItemClassID=4
where b.FNumber is null
SELECT PartNo ,
parttype = CASE WHEN parttype = '原材料' THEN '外购'
WHEN parttype = '产品' THEN '自制'
END ,
partName ,
ISNULL(unitmeasure, '个') ,
ISNULL(partspec, '') ,
ISNULL(FigureNo, '')
FROM parts b
WHERE NOT EXISTS ( SELECT FNumber
FROM AIS_titem a
WHERE a.FItemClassID = 4
AND a.FNumber = b.partno )
