OPENQUERY and Remote Call functions __ functions

OPENQUERY (Transact-SQL)

Executes the specified pass-through query on the specified linked server. This server is an OLE DB data source. OPENQUERY can be referenced in the FROM clause of a query as if it were a table name. OPENQUERY can also be referenced as the target table for an INSERT, UPDATE, or DELETE statement. However, this depends on the functionality of the OLE DB access interface. Although the query may return multiple result sets, OPENQUERY only returns the first one.

Transact-SQL syntax contract syntax

  
OPENQUERY (linked_server, ' query ')  

Parameters

Linked_server
An identifier that represents the name of the linked server.

' Query '
The query string executed in the linked server. The maximum length of this string is 8 KB. Notes

OPENQUERY A variable that does not accept its arguments.

OPENQUERY cannot be used to perform extended stored procedures on linked servers. However, by using four-part names, you can perform extended stored procedures on linked servers. For example:

EXEC SeattleSales.master.dbo.xp_msver  

Any call to OpenDataSource, OPENQUERY, or OPENROWSET in the FROM clause is separate from any calls to those functions that are used as the update target, even if the arguments provided for the two calls are the same. Specifically, filters or join conditions that apply to the results of any of these calls do not affect the results of other invocations. Permissions

Any user can perform OPENQUERY. The permissions that are used to connect to the remote server are obtained from the settings defined for the linked server. Example A. Perform an UPDATE delivery query

The following example uses the UPDATE delivery query for the linked server that is created in example A.

UPDATE OPENQUERY (oraclesvr, ' SELECT name from joe.titles WHERE id = ')   
SET name = ' Adifferentname ';  

B. Performing an INSERT delivery query

The following example uses the INSERT delivery query for the linked server that is created in example A.

INSERT OPENQUERY (oraclesvr, ' SELECT name from Joe.titles ')  
VALUES (' Newtitle ');  

C. Execute DELETE Delivery Query

The following example uses the delete pass-through query to delete the inserted rows in example C.

DELETE OPENQUERY (oraclesvr, ' SELECT name from joe.titles WHERE name = ' Newtitle ');  

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

SQL SERVER calls remote functions through stored procedures and OPENQUERY
CREATE FUNCTION Func_test
(
@UserId INT
)
RETURNS TABLE
As
Return
(
SELECT * FROM [192.168.0.252]. [User].dbo.func_getdata (@UserId)
)
Go

The report "is not allowed to use remote table-valued function calls." "The error

The internet says you can use OPENQUERY.

But this thing can't pass the parameters directly,

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

Error
Message 102, Level 15, State 1, Process func_test, line 9th
There are grammatical errors near ' + '.

No matter how you tune this string, it's not going to work. There is no mistake in grammar, but it can't be put on the openquery.

Later, instead of using a function, the stored procedure is invoked to resolve:

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