Wednesday, September 26, 2007

Calling functions using Linked Server on SQL 2005 (Openquery)

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

No comments: