OPENQUERY和遠程調用函數__函數

OPENQUERY (Transact-SQL)

在指定的連結的伺服器上執行指定的傳遞查詢。 該伺服器是 OLE DB 資料來源。 OPENQUERY 可以在查詢的 FROM 子句中引用，就好象它是一個表名。OPENQUERY 也可以作為 INSERT、UPDATE 或 DELETE 語句的目標表進行引用。 但這要取決於 OLE DB 提供者的功能。 儘管查詢可能返回多個結果集，但是 OPENQUERY 只返回第一個。

 Transact-SQL 文法約定 文法

  OPENQUERY ( linked_server ,'query' )  

參數

linked_server
表示連結的伺服器名稱的標識符。

' query '
在連結的伺服器中執行的查詢字串。 該字串的最大長度為 8 KB。 備忘

OPENQUERY 不接受其參數的變數。

OPENQUERY 不能用於對連結的伺服器執行擴充預存程序。 但是，通過使用四部分名稱，可以在連結的伺服器上執行擴充預存程序。 例如：

EXEC SeattleSales.master.dbo.xp_msver  

FROM 子句中對 OPENDATASOURCE、OPENQUERY 或 OPENROWSET 的任何調用與對用作更新目標的這些函數的任何調用都是分開獨立計算的，即使為兩個調用提供的參數相同也是如此。 具體而言，應用到上述任一調用的結果的篩選器或聯結條件不會影響其他調用的結果。 Permissions

任何使用者都可以執行 OPENQUERY。 用於串連到遠程伺服器的許可權是從為連結的伺服器定義的設定中擷取的。 樣本 A. 執行 UPDATE 傳遞查詢

以下樣本針對樣本 A 中建立的連結的伺服器使用 UPDATE 傳遞查詢。

UPDATE OPENQUERY (OracleSvr, 'SELECT name FROM joe.titles WHERE id = 101')   SET name = 'ADifferentName';  

B. 執行 INSERT 傳遞查詢

以下樣本針對樣本 A 中建立的連結的伺服器使用 INSERT 傳遞查詢。

INSERT OPENQUERY (OracleSvr, 'SELECT name FROM joe.titles')  VALUES ('NewTitle');  

C. 執行 DELETE 傳遞查詢

以下樣本使用 DELETE 傳遞查詢刪除樣本 C 中插入的行。

DELETE OPENQUERY (OracleSvr, 'SELECT name FROM joe.titles WHERE name = ''NewTitle''');  

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

SQL SERVER 通過預存程序和OPENQUERY調用遠程函數
CREATE FUNCTION func_Test
(    
    @UserId INT
)
RETURNS TABLE 
AS
RETURN 
(
    SELECT * FROM [192.168.0.252].[User].dbo.func_GetData(@UserId)
)
GO

會報“不允許使用遠端資料表值函數調用。”的錯誤

 

網上說可以使用OPENQUERY

 

但這個東西沒辦法直接傳參數，

CREATE FUNCTION func_Test
(    
    @UserId INT
)
RETURNS TABLE 
AS
RETURN 
(
    SELECT * FROM OPENQUERY([192.168.0.252],'SELECT * FROM [192.168.0.252].[User].dbo.func_GetData(' + CAST(@UserId AS VARCHAR(20)) + ')')
)
GO

報錯
訊息102，層級15，狀態1，過程func_Test，第9 行
'+' 附近有語法錯誤。

不管怎麼調這個字串，都不行。文法其實沒錯誤，但一放到OPENQUERY就不行。

後來不用函數，改用預存程序來調用才解決：

CREATE PROCEDURE [dbo].[prd_Test]
    @UserId INT
AS
BEGIN
    DECLARE @myUserId VARCHAR(20);
    SET @myUserId = CAST(@UserId AS VARCHAR(20));
    DECLARE @sql VARCHAR(1000);
    SET @sql = 'SELECT * FROM OPENQUERY([192.168.0.252],''SELECT * FROM [User].dbo.func_GetData(' + @myUserId + ')'')';
    EXEC(@sql);
END