Microsoft KB Archive/319138

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

Article ID: 319138

Article Last Modified on 10/3/2003

-

APPLIES TO


 * Microsoft SQL Server 2000 Standard Edition

-



This article was previously published under Q319138



SYMPTOMS
If you try to invoke a user-defined function (UDF) through a linked server by using a &quot;four-part naming&quot; convention, you may receive the following error message:

Server: Msg 170, Level 15, State 31, Line 1 Line 2: Incorrect syntax near '('.



CAUSE
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.



WORKAROUND
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