Running Remote Function with Parameters in Linked Server

Question

I have a SQL function that adds a number of business days into a specific date. The function is running ok in the local database server. However I'm having problem running the function from linked server.

Here's the parameters of my functions:

CREATE FUNCTION [dbo].[AddBizDays] (@Day SMALLINT, @StartDate DATE) RETURNS SMALLDATETIME

I'm following this suggestion: SQL Server: How to call a user-defined function (UDF) on linked server?

This is what I'm trying to call:

EXEC [Server1].DB1.dbo.sp_executesql 
N'SELECT [dbo].[AddBizDays](@Day, @StartDate)',
'N@Day smallint, @StartDate date',
'@Day = 1', 
'@StartDate = ''2024-10-01'''

OR

EXEC [Server1].DB1.dbo.sp_executesql N'SELECT [dbo].[AddBizDays](@Day, 
@StartDate)','N@Day smallint, @StartDate date',
'@Day = 1, @StartDate = ''2024-10-01'''

I keep getting an error with supplying the parameters Procedure expects parameter '@params' of type 'ntext/nchar/nvarchar'.

Answer 1

I believe Remote function reference is not allowed from a linked server, instead create a stored procedure in DB1 that executes the function. You can then call the procedure from the linked server. Alternatively, create the function in a DB on the linked server.