June 26, 2006 at 9:27 am
All
I have a Link Server setup named SecondarySQL. I can query the customer table as follows:
select
top 10 * from SecondarySQL.Billing.dbo.customer
Result >>>> (10 row(s) affected)
Ok, now that I have that working, I do the following on the secondary server
create
SYNONYM cust FOR customer;
and I test the synomym as follows...
select
top 10 * from Cust
Result >>>> (10 row(s) affected)
But when I try:
select top
10 * from SecondarySQL.Billing.dbo.cust
Result >>>> Msg 7357, Level 16, State 2, Line 1
Cannot process the object ""Billing"."dbo"."cust"". The OLE DB provider "SQLNCLI" for linked server "SecondarySQL" indicates that either the object has no columns or the current user does not have permissions on that object.
NOTE: I am using sa authorization on both servers
So the question is.... Can a synonym be used with a link server in 2005?
Thanks in Advance
Eric
June 26, 2006 at 1:55 pm
Yes, synonyms can be used with linked server, but the need to be defined on the calling (I'm guessng you'd call it 'Primary') server...
create SYNONYM cust FOR SecondarySQL.Billing.dbo.customer
June 26, 2006 at 2:34 pm
Thanks
That does work for problem number 1.
Problem number two is trying to use information_schema.columns across the link server?
Eric
June 27, 2006 at 2:27 pm
Eric:
What are you trying to do with the INFORMATION_SCHEMA.COLUMNS?
It is a view, so you should have no issue with doing a select
SELECT * FROM SecondarySQL.Billing.INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Customers'
June 28, 2006 at 10:20 am
Information_schema views are private to a database instead you can use sys.columns view
June 28, 2006 at 1:11 pm
Acutally I was able to define a view on the information schema, then access it using a synonym on the remote server
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply