Synonyms of Table-valued function over Linked Server

Last Post 01 Jul 2007 12:35 AM by eetnavi. 3 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
eetnavi
New Member
New Member

--
21 May 2007 05:29 AM
I am trying to create a Synonym of a Table-valued function residing on a linked server. I've tried everything it seems and it just won't work - I keep getting this error:

quote:

The OLE DB provider "SQLNCLI" for linked server "Server01" does not contain the table ""MyTable"."dbo"."GetOrder"". The table either does not exist or the current user does not have permissions on that table.


The function takes a single int value as input, and does nothing fancy but join a couple of tables.

The only explenation I have found for this error, is that the user I'm trying with doesn't have sufficient credentials, but now I have tried to create the linked server with the "sa" user, logging in and executing the SQL with the "sa" user and generally just granted db_owner rights all around, and NOTHING works!

Does anybody have any insight to this - is it just impossible to create synonyms of Functions over linked server?

Btw. executing sproc's etc. over the linked server (without synonyms) is working fine, but we'd really like to utilize this new feature in our setup.
SQLUSA
New Member
New Member

--
21 May 2007 03:15 PM
Could this be the explanation:

BOL: Four-part names for function base objects are not supported.

Kalman Toth, Data Warehouse Architect
SQL Server 2005 Training - http://www.sqlusa.com
eetnavi
New Member
New Member

--
30 Jun 2007 10:39 AM
Hi, the reason could very well be this. I am trying to create a synonym of a Scalar function, which resides on a linked server.

If four-part names isn't supported, how on earth am I supposed to do this then? In every article I've read about synonyms, functions (both scalar and table-valued), have been listed as being supported by synonyms. It simply HAS to be possible to synonym a function on another server!

Does anybody know if theres a point I'm missing here?
eetnavi
New Member
New Member

--
01 Jul 2007 12:35 AM
Well, it would explain it if SQL server doesn't support trans-server-calls like stored procedures and tables etc. does.

Do you know if this is something we can expect SQL server to support in the future, og perhaps of a way to bypass this limitation? I was thinking something like using a CLR to log on to the other server, and execute this function?

Thanks for your prompt reply anyways.
You are not authorized to post a reply.

Acceptable Use Policy