Month: June 2012

Error: Remote table-valued function calls are not allowed

Fix: use “WITH (NOLOCK)” instead of just “(NOLOCK)

Explanation:  When issuing a select that has uses four part naming to address the table and the table has a (nolock) hint, the t-sql will fail to execute with the error “Remote table-valued function calls are not allowed.”

If you execute the query with 3 or 2 part naming it runs without error.

To get the query to work using 4 part naming you have to put the “with” keyword before the (nolock).

http://connect.microsoft.com/SQLServer/feedback/details/126162/remote-table-valued-function-calls-are-not-allowed

Error: Server ‘SERVERNAME’ is not configured for RPC
Code:

EXEC [LINKEDSERVERNAME].[DATABASENAME].dbo.[STOREDPROCNAME]

Problem: Attempting to execute a stored procedure across a SQL Server Linked Server

Fix: Enable “rpc out”

EXEC master.dbo.sp_serveroption 
     @server=N'LINKEDSERVERNAME'
   , @optname=N'rpc out'
   , @optvalue=N'true'

GO