Workaround for calling table-valued function remotely in SQL Server over Linked Server needs some fancy in the coding. Something like this
EXEC SERVER_LINK.DATABASE_NAME..sp_executesql N'SELECT value FROM fn_testexecute(''value1'', value2, value3, value3, value4);'
Now what if the SQL fails due to below error:
Msg 7411, Level 16, State 1, Line 2
Server 'TEST_LINK' is not configured for RPC.
This is a configuration thing with the Linked Server option called "RPC OUT". This is necessary to execute a procedure calls to go out to the linked Server. Where as, "RPC" option allows stored procedure call from the linked Server.
For this case, I had to enable "RPC Out". I ran sp_helpserver and it reported RPC OUT isn’t enabled.
That's it. Problem solved.
EXEC SERVER_LINK.DATABASE_NAME..sp_executesql N'SELECT value FROM fn_testexecute(''value1'', value2, value3, value3, value4);'
Now what if the SQL fails due to below error:
Msg 7411, Level 16, State 1, Line 2
Server 'TEST_LINK' is not configured for RPC.
This is a configuration thing with the Linked Server option called "RPC OUT". This is necessary to execute a procedure calls to go out to the linked Server. Where as, "RPC" option allows stored procedure call from the linked Server.
For this case, I had to enable "RPC Out". I ran sp_helpserver and it reported RPC OUT isn’t enabled.
That's it. Problem solved.
No comments:
Post a Comment