1

I've seen other posts talk about using OPENQUERY to call a function on a remote server, but I have not been able to get this to work for my simple function below, which returns a numeric value. (I cannot change this function, so I am stuck with its parameters and return value):

DECLARE @ProjectDepartment NUMERIC(12,0);
DECLARE @Department NUMERIC(12,0);

SET @Department = 104;

SET @ProjectDepartment = db.schema.GetSharing(@Department, 'DeliveryPlace');

I've tried:

SELECT
  @ProjectDepartment = @ProjectDepartment
FROM
  OPENQUERY(server,
  'SELECT @ProjectDepartment
    FROM db.schema.GetSharing(@Department, "DeliveryPlace")');

and

EXECUTE @ProjectDepartment = server.db.schema.sp_executesql
  N'SELECT db.schema.GetSharing(@Department, "DeliveryPlace")',
  N'@Department NUMERIC(12,0)',
  @Department = 104;

and

DECLARE @sql NVARCHAR(4000);

SET @sql = 'SELECT' +
CAST(@ProjectDepartment AS NVARCHAR(12)) +
'FROM OPENQUERY(server,
''SELECT ' + CAST(@ProjectDepartment AS NVARCHAR(12)) +
' = db.schema.GetSharing(' + CAST(@Department AS NVARCHAR(12)) + ', "DeliveryPlace")'')';

EXEC(@sql);

I'm working on SQL Server Management Studio 17.9.1.

ADDED:

I added this to a test routine:

Use db
GO

DECLARE @ProjectDepartment  NUMERIC(12,0);
DECLARE @Department         NUMERIC(12,0);

SET @Department = 104;

EXEC @ProjectDepartment = server.db.schema.sp_executesql
N'SELECT GetSharing(@Department, "DeliveryPlace")',
N'@Department NUMERIC(12,0)',
@Department=@Department;

I got this error:

Could not find stored procedure 'db.schema.sp_executesql'.

When I use this code in my actual function, I still get this error:

Remote function calls are not allowed within a function.  
Robert
  • 11
  • 3

0 Answers0