July 31, 2014 at 2:12 pm
I have a linked server called DOA_FANS on my SQL 2012 that connects to an Oracle server/database. One of the tables in Oracle is called FIS.TITLE.CONTRACTS I can use this and read the table: SELECT * FROM OPENQUERY(DOA_FANS,'Select * from FIS.TITLE_CONTRACTS'). I was asked to create a synonym called FTC so that the query could be shorter. I executed this command:
CREATE SYNONYM FTC
FOR DOA_FANS.FIS.TITLE_CONTRACTS
GO
and it said it executed successfully. However, when I try SELECT * FROM FTC I get a error msg 5313 that says Synonym 'FTC' refers to an invalid object.
I checked sys.synonyms and it is there. I have seen several posts online that this should work, but it isn't.
July 31, 2014 at 2:35 pm
I figured it out. When creating the synonym I needed two dots after linked server name:
CREATE SYNONYM FISVEND
FOR DOA_FANS..FIS.VENDORS_V
GO
However...running the query from openquery took 39 seconds to return 215,848 rows BUT running the query with synonym took 2 min 52 seconds. Any ideas why?
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply