Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Synonym Help Expand / Collapse
Author
Message
Posted Thursday, July 31, 2014 2:12 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, November 14, 2014 12:17 PM
Points: 24, Visits: 109
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.
Post #1598474
Posted Thursday, July 31, 2014 2:35 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, November 14, 2014 12:17 PM
Points: 24, Visits: 109
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?
Post #1598488
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse