T-SQL：无法从链接服务器的 OLE DB 提供程序"OraOLEDB.Oracle"获取行的数据

Question

错误是：

无法从链接服务器的 OLE DB 提供程序"OraOLEDB.Oracle"获取行的数据

我正在尝试将Oracle SQL查询转换为T-SQL。运行此版本的查询时出现错误：

SELECT B.SAAS_ACTIVITY, A.LAST_NAME, A.FIRST_NAME, A.MIDDLE_NAME,  
B.DIVISION_CODE, B.GRANT_YEAR, B.ACTIVITY_CODE, B.ACT_ACTIVITY_CODE,
B.COST_CENTER, B.HOURS_BEG, B.LEAVE_ADJ_HOURS, B.CURRENT_MONTH,
B.HOURS_BEG + B.LEAVE_ADJ_HOURS TOTAL_HOURS, B.PERCENT_WORK, 
B.DIV_MONTHLY_SALARY, C.DIV_DESCRIPTION,
B.DIV_SALARY_PLUS_FRINGE - B.DIV_MONTHLY_SALARY FRINGE, 
B.DIV_SALARY_PLUS_FRINGE
FROM ORCL_RIVENDELL..BP2K.EMT_EMPLOYEE A, 
ORCL_RIVENDELL..BP2K.LABOR_DISTRIB_MASTER_DETAIL B, 
ORCL_RIVENDELL..BP2K.ACT_DIVISION C
WHERE A.ID = B.ID
AND B.FISCAL_YEAR = :P_SFY
AND B.DIVISION_CODE = C.DIVISION_CODE
AND C.DIV_SFY = :P_SFY
AND B.CURRENT_MONTH >= CASE WHEN :P_FROM_MONTH is null THEN 2 
ELSE :P_FROM_MONTH
END
AND B.CURRENT_MONTH <= CASE WHEN :P_TO_MONTH is null THEN 13 
ELSE :P_TO_MONTH
END
AND B.SAAS_ACTIVITY >= CASE WHEN :P__FROM_OFFICE  is null THEN '4701' 
ELSE :P_FROM_OFFICE
END
AND B.SAAS_ACTIVITY <= CASE WHEN :P__TO_OFFICE  is null THEN '4705' 
ELSE :P_TO_OFFICE
END
AND B.DIVISION_CODE >= CASE WHEN :P__FROM_DIV  is null THEN '0011' 
ELSE :P_FROM_DIV
END
AND B.DIVISION_CODE <= CASE WHEN :P__TO_DIV  is null THEN '9999' 
ELSE :P_TO_DIV
END
AND B.DIVISION_CODE <> '1050'
ORDER BY B.DIVISION_CODE, A.LAST_NAME, B.CURRENT_MONTH

但是，如果删除联接以获取DIV_DESCRIPTION，并且只使用DIV_CODE，则查询运行良好：

SELECT B.SAAS_ACTIVITY, A.LAST_NAME, A.FIRST_NAME, A.MIDDLE_NAME,  
B.DIVISION_CODE, B.GRANT_YEAR, B.ACTIVITY_CODE, B.ACT_ACTIVITY_CODE,
B.COST_CENTER, B.HOURS_BEG, B.LEAVE_ADJ_HOURS, B.CURRENT_MONTH,
B.HOURS_BEG + B.LEAVE_ADJ_HOURS TOTAL_HOURS, B.PERCENT_WORK, 
B.DIV_MONTHLY_SALARY, B.DIVISION_CODE,
B.DIV_SALARY_PLUS_FRINGE - B.DIV_MONTHLY_SALARY FRINGE, 
B.DIV_SALARY_PLUS_FRINGE
FROM ORCL_RIVENDELL..BP2K.EMT_EMPLOYEE A, 
ORCL_RIVENDELL..BP2K.LABOR_DISTRIB_MASTER_DETAIL B
WHERE A.ID = B.ID
AND B.FISCAL_YEAR = :P_SFY
AND B.CURRENT_MONTH >= CASE WHEN :P_FROM_MONTH is null THEN 2 
ELSE :P_FROM_MONTH
END
AND B.CURRENT_MONTH <= CASE WHEN :P_TO_MONTH is null THEN 13 
ELSE :P_TO_MONTH
END
AND B.SAAS_ACTIVITY >= CASE WHEN :P__FROM_OFFICE  is null THEN '4701' 
ELSE :P_FROM_OFFICE
END
AND B.SAAS_ACTIVITY <= CASE WHEN :P__TO_OFFICE  is null THEN '4705' 
ELSE :P_TO_OFFICE
END
AND B.DIVISION_CODE >= CASE WHEN :P__FROM_DIV  is null THEN '0011' 
ELSE :P_FROM_DIV
END
AND B.DIVISION_CODE <= CASE WHEN :P__TO_DIV  is null THEN '9999' 
ELSE :P_TO_DIV
END
AND B.DIVISION_CODE <> '1050'
ORDER BY B.DIVISION_CODE, A.LAST_NAME, B.CURRENT_MONTH

获取DIV_DESCRIPTION并不是绝对关键的，但我想了解为什么会发生错误(我使用 Oracle SQL 比 TSQL 多得多)。 该列在ACT_DIVISION表和LABOR_DISTRIB_MASTER_DETAIL表中均定义为 VARCHAR2(12)。这是数据类型不匹配的事情，还是别的什么？

谢谢 哈利

Answer 1

我建议让Oracle通过OPENQUERY传递查询来为您完成所有繁重的工作，这对您来说要容易得多(因为您知道Oracle语法)并且更快，因为Oracle将使用自己的索引在自己的表上完成自己的工作，并且仅将结果集传回SQL Server。

请注意，由于查询包含在' '中，因此您必须将其中的单个'加倍。

执行此操作时，OLEDB 驱动程序应为您处理所有不稳定的数据类型映射。

语法：

SELECT *
FROM OPENQUERY([ORCL_RIVENDELL],'SELECT B.SAAS_ACTIVITY, A.LAST_NAME, A.FIRST_NAME, A.MIDDLE_NAME,  
B.DIVISION_CODE, B.GRANT_YEAR, B.ACTIVITY_CODE, B.ACT_ACTIVITY_CODE,
B.COST_CENTER, B.HOURS_BEG, B.LEAVE_ADJ_HOURS, B.CURRENT_MONTH,
B.HOURS_BEG + B.LEAVE_ADJ_HOURS TOTAL_HOURS, B.PERCENT_WORK, 
B.DIV_MONTHLY_SALARY, C.DIV_DESCRIPTION,
B.DIV_SALARY_PLUS_FRINGE - B.DIV_MONTHLY_SALARY FRINGE, 
B.DIV_SALARY_PLUS_FRINGE
FROM BP2K.EMT_EMPLOYEE A, 
BP2K.LABOR_DISTRIB_MASTER_DETAIL B, 
BP2K.ACT_DIVISION C
WHERE A.ID = B.ID
AND B.FISCAL_YEAR = :P_SFY
AND B.DIVISION_CODE = C.DIVISION_CODE
AND C.DIV_SFY = :P_SFY
AND B.CURRENT_MONTH >= CASE WHEN :P_FROM_MONTH is null THEN 2 
ELSE :P_FROM_MONTH
END
AND B.CURRENT_MONTH <= CASE WHEN :P_TO_MONTH is null THEN 13 
ELSE :P_TO_MONTH
END
AND B.SAAS_ACTIVITY >= CASE WHEN :P__FROM_OFFICE  is null THEN ''4701'' 
ELSE :P_FROM_OFFICE
END
AND B.SAAS_ACTIVITY <= CASE WHEN :P__TO_OFFICE  is null THEN ''4705'' 
ELSE :P_TO_OFFICE
END
AND B.DIVISION_CODE >= CASE WHEN :P__FROM_DIV  is null THEN ''0011'' 
ELSE :P_FROM_DIV
END
AND B.DIVISION_CODE <= CASE WHEN :P__TO_DIV  is null THEN ''9999'' 
ELSE :P_TO_DIV
END
AND B.DIVISION_CODE <> ''1050''
ORDER BY B.DIVISION_CODE, A.LAST_NAME, B.CURRENT_MONTH')

如果需要传递参数，语法如下：

假设您有一个名为 @myDate 的参数需要传递。

DECLARE @myDate DATETIME=GETDATE()
DECLARE @SQL NVARCHAR(MAX)
SET @SQL = 'SELECT *
FROM OPENQUERY([ORCL_RIVENDELL],''SELECT *
FROM  BP2K.SOME_TABLE
WHERE ACTIVITYDATE = '''''+CONVERT(VARCHAR,@myDate,120)+''''' '')'
PRINT @SQL
--EXEC SP_EXECUTESQL @SQL

综上所述，使用动态SQL需要非常小心，如果您不控制这些参数的输入，则很危险。 你以这种方式向SQL注入攻击敞开了大门。 有更好/更安全的方法可以将参数传递到sp_executesql但在使用OPENQUERY时不起作用。 如果可以使用ISDATE()或ISNUMERIC()更好的方式验证输入，则在它们未通过验证时不要执行。 但你最安全的选择是永远不允许除了你之外的任何人传递这些参数。