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.