Friday, February 10, 2012

Calling user defined function from other server

I have UDF in a database on SQL2000 server. Is it possible to call this UDF from other server (SQL2005)? I did setup a linked server to SQL2000

Call to the following function returns an error:

Msg 207, Level 16, State 1, Line 1

Invalid column name 'srv2000'.

select [srv2000].db_test.dbo.F_TEST()

You cannot call remote UDFs in SQL Server right now. So you will have to use pass-through query using OPENQUERY to call the UDF or EXECUTE AT in SQL Server 2005.

-- SQL Server 2000/2005

select i from openquery([srv2000], 'select db_test.dbo.F_TEST() as i')

-- SQL Server 2005

exec('select db_test.dbo.F_TEST() as i') at [srv2000]

-- Or

declare @.i int
exec('select ? = db_test.dbo.F_TEST() ', @.i output) at [srv2000]
select @.i

|||Thanks for help!

No comments:

Post a Comment