Remote table-valued function calls are not allowed
Steps to reproduce:
create function dbo.TestFunc() returns @t table (id int) as
insert @t values (1)
insert @t values (2)
insert @t values (3)
select * from tempdb.dbo.TestFunc()
select * from [sharepoint].tempdb.dbo.TestFunc()
-- [sharepoint] - local machine
And get error
Msg 4122, Level 16, State 1, Line 1
Remote table-valued function calls are not allowed.
Upvotes: 100<=-=Dec 7 2007 10:41AM=-=>
Yes, this would be great. You can call views and stored procs from another server or another database; you should be able to call table-valued functions also.
I hope this is fixed in SQL Server 2008.<=-=Dec 21 2007 2:15PM=-=>
Thanks for your feedback regarding remote table-valued function calls. We are currently investigating the effort needed to implement this functionality. Since we are wrapping up our efforts on the next release of SQL Server (2008) it is unlikley that a fix will be available by then.
This is one of those “loose ends” that frustrates the SQL 2005 users. I hope that the MS SQL 2008 team(s) will take care…<=-=Jul 3 2008 11:46AM=-=>
Hope this is fixed soon. Big issue for linking two sql servers.<=-=Sep 23 2008 8:12AM=-=>
Oh, gods, yes please!
Isn’t this part of the Ansi standard? shouldn’t compliance to standards ALWAYS be a priority?<=-=Feb 18 2010 1:45PM=-=>
The workaround does not address the bug, which is executing a local TVF using its fully qualified name (four part naming scheme). The engine should be smart enough to know that the execution context is local and ignore the server name. The workaround for the bug, AS REPORTED, would be to use the three part name (database.owner.object).
The real issue here is executing a REMOTE TVF, i.e. [sharepoint] = remote server. It is not fixed in sql server 2008. I agree with DWalker59a, if you can call remote views and SP, you should be able to call a remote TVF.<=-=Nov 18 2010 6:30PM=-=>
Yep, this needs to be added to SQL Server ASAP. One reason in particular: the ACE driver does not support the insert opendatasource exec stored_proc syntax, which would be a nice clean way to export from SQL Server to… well, anything. We were going to use functions instead (insert opendatasource select from UDF), but this becomes far less elegant if we need to have the export schema on every server.<=-=Sep 12 2012 3:16AM=-=>
Do Microsoft intend to change/fix this at all?
2008 has been and gone, 2008 R2 has been and gone, now 2012 is here, no doubt the team are too busy working on 2014, or 2012 R2 or whatever it will be called….<=-=Mar 21 2014 4:32AM=-=>
SQL2014 has now been released and still has not been addressed. Can this please be added to the next release of SQL Server ?<=-=Jul 30 2014 2:24PM=-=>
More time then not this would be valuable edition as passing around xml or other blocks is particularly painful and resource intensive operation. For example inserting into a remote table the .Net client preforms the task row by row� lot of back and forth which is counter productive. Please 2014 R2 enhancement?<=-=Aug 4 2015 5:09AM=-=>
To encounter a missing feature in this point of the feature matrix is rather surprising and I would have expected that the support for remote table-valued function executions is a side effect immediately covered by supporting tables and views and does not really introduce any additional code – and for the side effects I wonder what they could be.
What is the reason that this important feature is explicitely missing?
All the workarounds are not acceptable, because they either introduce strong coupling or require side-effecting not available to functions.
One must be able to implement a simple proxy pattern through a db link.
Will this problem ever be fixed? 10 years have passed and many versions have appeared ….!
I like that this used to have 100 upvotes, but when they migrated it over, they cleared the upvotes....here goes another 10 years before this is fixed. lol