MSSQL 分析器执行下面的SQL代码: DECLARE @WIP_ENTITY_NAME VARCHAR(16)
declare @sql varchar(2000)
declare @str varchar(2000)
declare @sqlStr varchar(6000)
SET @WIP_ENTITY_NAME='D0721E068'
set @SQL= ' SELECT WE.WIP_ENTITY_NAME,
BSO.OPERATION_CODE,
BSO.OPERATION_DESCRIPTION
FROM WIP.WIP_OPERATIONS WO , -- 站別
BOM.BOM_STANDARD_OPERATIONS BSO ,
WIP.WIP_ENTITIES WE ,
WIP.WIP_DISCRETE_JOBS WDJ ,
BOM.BOM_OPERATION_SEQUENCES BOS ,
BOM.BOM_OPERATIONAL_ROUTINGS BOR
WHERE BOS.ROUTING_SEQUENCE_ID=BOR.ROUTING_SEQUENCE_ID
AND BOS.OPERATION_SEQUENCE_ID=WO.OPERATION_SEQUENCE_ID
AND BSO.STANDARD_OPERATION_ID=WO.STANDARD_OPERATION_ID
AND WE.WIP_ENTITY_ID=WO.WIP_ENTITY_ID
AND WE.WIP_ENTITY_ID=WDJ.WIP_ENTITY_ID
AND WE.WIP_ENTITY_NAME= '
set @STR=@SQL + ''+ @WIP_ENTITY_NAME + '' set @sqlStr= 'SELECT * FROM openquery(ln_Oracle,'''+ @Str +''')'
exec(@sqlStr)
提示:
伺服器: 訊息 7321,層級 16,狀態 2,行 1
準備要對 OLE DB Provider 'MSDAORA' 執行查詢時發生錯誤。
[OLE/DB provider returned message: ORA-00904: "D0721E068": invalid identifier
]
OLE DB 錯誤追蹤 [OLE/DB Provider 'MSDAORA' ICommandPrepare::Prepare returned 0x80040e14]。
请大家帮个,应该 怎样写才能带参数 查询 ORACLE数据库里的表信息。
下面代码 是正常执行的,
SELECT * FROM openquery(ln_Oracle,'
SELECT WE.WIP_ENTITY_NAME,
BSO.OPERATION_CODE,
BSO.OPERATION_DESCRIPTION
FROM WIP.WIP_OPERATIONS WO , -- 站別
BOM.BOM_STANDARD_OPERATIONS BSO , ---ROUTING
WIP.WIP_ENTITIES WE , --工單信息
WIP.WIP_DISCRETE_JOBS WDJ ,
BOM.BOM_OPERATION_SEQUENCES BOS ,
BOM.BOM_OPERATIONAL_ROUTINGS BOR ---ROUTING
WHERE BOS.ROUTING_SEQUENCE_ID=BOR.ROUTING_SEQUENCE_ID
AND BOS.OPERATION_SEQUENCE_ID=WO.OPERATION_SEQUENCE_ID
AND BSO.STANDARD_OPERATION_ID=WO.STANDARD_OPERATION_ID
AND WE.WIP_ENTITY_ID=WO.WIP_ENTITY_ID
AND WE.WIP_ENTITY_ID=WDJ.WIP_ENTITY_ID
' )
declare @sql varchar(2000)
declare @str varchar(2000)
declare @sqlStr varchar(6000)
SET @WIP_ENTITY_NAME='D0721E068'
set @SQL= ' SELECT WE.WIP_ENTITY_NAME,
BSO.OPERATION_CODE,
BSO.OPERATION_DESCRIPTION
FROM WIP.WIP_OPERATIONS WO , -- 站別
BOM.BOM_STANDARD_OPERATIONS BSO ,
WIP.WIP_ENTITIES WE ,
WIP.WIP_DISCRETE_JOBS WDJ ,
BOM.BOM_OPERATION_SEQUENCES BOS ,
BOM.BOM_OPERATIONAL_ROUTINGS BOR
WHERE BOS.ROUTING_SEQUENCE_ID=BOR.ROUTING_SEQUENCE_ID
AND BOS.OPERATION_SEQUENCE_ID=WO.OPERATION_SEQUENCE_ID
AND BSO.STANDARD_OPERATION_ID=WO.STANDARD_OPERATION_ID
AND WE.WIP_ENTITY_ID=WO.WIP_ENTITY_ID
AND WE.WIP_ENTITY_ID=WDJ.WIP_ENTITY_ID
AND WE.WIP_ENTITY_NAME= '
set @STR=@SQL + ''+ @WIP_ENTITY_NAME + '' set @sqlStr= 'SELECT * FROM openquery(ln_Oracle,'''+ @Str +''')'
exec(@sqlStr)
提示:
伺服器: 訊息 7321,層級 16,狀態 2,行 1
準備要對 OLE DB Provider 'MSDAORA' 執行查詢時發生錯誤。
[OLE/DB provider returned message: ORA-00904: "D0721E068": invalid identifier
]
OLE DB 錯誤追蹤 [OLE/DB Provider 'MSDAORA' ICommandPrepare::Prepare returned 0x80040e14]。
请大家帮个,应该 怎样写才能带参数 查询 ORACLE数据库里的表信息。
下面代码 是正常执行的,
SELECT * FROM openquery(ln_Oracle,'
SELECT WE.WIP_ENTITY_NAME,
BSO.OPERATION_CODE,
BSO.OPERATION_DESCRIPTION
FROM WIP.WIP_OPERATIONS WO , -- 站別
BOM.BOM_STANDARD_OPERATIONS BSO , ---ROUTING
WIP.WIP_ENTITIES WE , --工單信息
WIP.WIP_DISCRETE_JOBS WDJ ,
BOM.BOM_OPERATION_SEQUENCES BOS ,
BOM.BOM_OPERATIONAL_ROUTINGS BOR ---ROUTING
WHERE BOS.ROUTING_SEQUENCE_ID=BOR.ROUTING_SEQUENCE_ID
AND BOS.OPERATION_SEQUENCE_ID=WO.OPERATION_SEQUENCE_ID
AND BSO.STANDARD_OPERATION_ID=WO.STANDARD_OPERATION_ID
AND WE.WIP_ENTITY_ID=WO.WIP_ENTITY_ID
AND WE.WIP_ENTITY_ID=WDJ.WIP_ENTITY_ID
' )
应该为
set @STR=@SQL + ''''+ @WIP_ENTITY_NAME + ''''最好将 @sqlStr 结果 print 出来,检查一下。