Friday, July 19, 2013

SQL Server: Linked Server is not configured for RPC error:

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.

No comments: