PRB: User-Defined Function Call in Four-Part Linked Server Query Fails with Error Message 170

Article translations Article translations
Article ID: 319138 - View products that this article applies to.
This article was previously published under Q319138
Expand all | Collapse all

On This Page


If you try to invoke a user-defined function (UDF) through a linked server by using a "four-part naming" convention, you may receive the following error message:
Server: Msg 170, Level 15, State 31, Line 1 Line 2: Incorrect syntax near '('.


User-defined function calls inside a four-part linked server query are not supported in SQL Server 2000. Error message 170 indicates that the syntax of a Transact-SQL statement is incorrect.


To work around this problem, use the Openquery function instead of the four-part naming convention. For example, instead of the following query
Select * from Linked_Server.northwind.dbo.square_value(10)
run a query with the Openquery function:
Select * from Openquery(Linked_Server,'select northwind.dbo.square_ value(10)')
If the user-defined function takes variable or scalar parameters, you can use the sp_executesql stored procedure to avoid this behavior. For example:
exec Linked_Server.northwind.dbo.sp_executesql N'SELECT northwind.dbo.square_value(@input)',N'@input int',@input=10


SQL Server 2000 Books Online; topic: "Openquery"


Article ID: 319138 - Last Review: October 3, 2003 - Revision: 3.2
  • Microsoft SQL Server 2000 Standard Edition
kbprb KB319138

Give Feedback


Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from