Post reply

LInked server

  • December 2, 2010 at 8:44 am

    #232073

    Hi all,

    We are currently implementing interface between Oracle & SQl server, planning to create dblink from Oracle to SQl server in the coding we reference the tables as tablename@dblinkname. Similarly we are planning on using linked server to access Oracle tables from SQl server, but we need to reference the Oracle tables the same way as tablename@linkedservername.

    Is it possible to reference the objects as tablename@linkname using linked server??

    Thanks so much

  • December 2, 2010 at 8:54 am

    #1257946

    yes, you can create a synonym that points to the linked table.

    a synonym must point ot an object...procedure,function, table view, etc.

    it cannot point to part of a name.

    sp_tables_ex MyLinkedserver

    DROP SYNONYM dbo.citydata@ProdServer

    CREATE SYNONYM dbo.citydata@ProdServer FOR MyLinkedserver.master.dbo.CITYDATA

    select * from citydata@ProdServer

    select * from dbo.citydata@ProdServer

    select * from dbo.[citydata@ProdServer] --better

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • December 2, 2010 at 9:01 am

    #1257954

    Thanks Lowell.

    CREATE SYNONYM dbo.citydata@ProdServer FOR MyLinkedserver.master.dbo.CITYDATA

    Is 'ProdServer' linkedserver name in the example?

  • December 2, 2010 at 9:14 am

    #1257958

    newbieuser (12/2/2010)

    Thanks Lowell.

    CREATE SYNONYM dbo.citydata@ProdServer FOR MyLinkedserver.master.dbo.CITYDATA

    Is 'ProdServer' linkedserver name in the example?

    a synonym is an alias...it can be anything, but obviously it needs to make sence to you;

    i just wanted to point out that even the linked server name is an alias..

    so for example,

    my real server is named "DBSQL2K8"...it's just a machine name.

    I made a linked server named "MyLinkedserver" to point at that server.

    then i made the synonym that kind of makes sense as to what the server name or function is;

    i could easily have made the synonym dbo.citydata@MyLinkedserver it it was more appropriate.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • January 17, 2011 at 12:56 pm

    #1274369

    Hi friends,

    We have created LInked server to oracle database and have synonyms created for few Oracle tables(example - tablename@link) in the SQL database to access from the application. For example,

    table1 is owned by Oracleuser1 in oradb

    Linkedserver named LINK is created to Oracleuser1@ORADB

    We query via Linked server as

    select * from LINK..ORACLEUSER1.TABLE1;

    we have created synonym for this table, so from the application we query as below:

    select * from table1@link;

    We would like to query tables owned by other Oracle schemas via the same linked server in SQLSERVER. For example,

    table2 is owned by Oracleuser2 in oradb. oracleuser1 reads table2 via synonym in the Oracle database. But we need SQLSERVER to read this table2 via the LINKed server LINK.. so from SQLSERVER I should be able to query table2 from the same link as

    select * from table2@link;

    Is there a way to do this? Thanks a lot

  • June 18, 2012 at 8:48 pm

    #1502562

    Hi friends,

    We have created LInked server to oracle database and have synonyms created for few Oracle tables(example - tablename@link) in the SQL database to access from the application. For example,

    table1 is owned by Oracleuser1 in oradb

    Linkedserver named LINK is created to Oracleuser1@ORADB

    We query via Linked server as

    select * from LINK..ORACLEUSER1.TABLE1;

    we have created synonym for this table, so from the application we query as below:

    select * from table1@link;

    We would like to query tables owned by other Oracle schemas via the same linked server in SQLSERVER. For example,

    table2 is owned by Oracleuser2 in oradb. oracleuser1 reads table2 via synonym in the Oracle database. But we need SQLSERVER to read this table2 via the LINKed server LINK.. so from SQLSERVER I should be able to query table2 from the same link as

    select * from table2@link;

    Is there a way to do this? Thanks a lot

  • June 19, 2012 at 3:50 am

    #1502666

    Assuming that permissions are not an issue when accessing the "ORACLEUSER2" schema, all you have to do is create another synonym:

    CREATE SYNONYM table2@link FOR LINK..ORACLEUSER2.TABLE2

  • June 19, 2012 at 6:12 am

    #1502764

    I get this error when I query

    select * from LINK..ORACLEUSER2.TABLE2;

    Msg 7314, Level 16, State 1, Line 1

    The OLE DB provider "ORAOLEDB.ORACLE" for linked server "LINK" does not contain the table ""ORACLEUSER2"."TABLE2"". The table either does not exist or the current user does not have permissions on that table.

    I think it is because the SQLSERVER link has been created to Oracleuser1..

    Within the Oracle database, there is an oracle db link(oralink) to oracleuser2 tables. So, when I'm connected as oracleuser1, I'm able to do a select as 'select * from table2@oralink'. We have created synonym in oracle as 'create synonym table2 as table2@oralink'. Oracleuser1 is able to query select * from table2 using the synonym via the oralink.

    From SQL Server, I'm not sure how to make this work.. I need to be able to select on ORACLEUSER2.table2 using the linked server... Please help me...

    Thanks again

  • June 19, 2012 at 11:59 am

    #1503030

    Anyone please?

  • June 20, 2012 at 3:03 am

    #1503276

    If you change the linked server "LINK" to connect to your Oracle DB as ORACLEUSER2 are you able to run the following query successfully?

    select * from LINK..ORACLEUSER2.TABLE2

    If so, then I would just create 2 linked servers for your Oracle environment (e.g. "LINK1" & "LINK2"). One would connect as ORACLEUSER1 and the other would connect as ORACLEUSER2. Then create your synonyms in SQL Server accordingly:

    CREATE SYNONYM table1@oralink FOR LINK1..ORACLEUSER1.TABLE1;

    CREATE SYNONYM table2@oralink FOR LINK2..ORACLEUSER2.TABLE2;

  • June 20, 2012 at 6:43 pm

    #1503727

    Thanks a ton. It works

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply