Invalid 'Remote table-valued function calls are not allowed' message error

Assuming that [] is a linked server created on a SQL Server 2005 computer and pointing to a SQL Server 2000 computer, executing a simple query like this:

  FROM [].SALES_DB.dbo.ORDERS (nolock)

will fail with the following error message:

  Server: Msg 4122, Level 16, State 1, Line 1
  Remote table-valued function calls are not allowed.

This error message is not correct, since ORDERS is a table and not a function.

This query will run correctly only if the WITH keyword is used before the (nolock) hint:

  FROM [].SALES_DB.dbo.ORDERS WITH (nolock)

This problem is also discussed here.

1 comment:

  1. Awesome - that was my problem, many thanks!