none
(Sumber: milist SQL Server) linked server RRS feed

  • Pertanyaan

  • rekan-rekan,

    ada link step by step membuat linked server sql server 2008 R2 ke oracle ga? please info nya

    saya sudah berhasil ikutin step2 di suatu blog tetapi query muncul error :

     

    Msg 7356, Level 16, State 1, Line 1
    The OLE DB provider "OraOLEDB.Oracle" for linked server "XXX" supplied inconsistent metadata for a column. The column "colX" (compile-time ordinal 1) of object "select * from X.tableX" was reported to have a "DBTYPE" of 130 at compile time and 129 at run time.

     


    Agnes Sannie [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Kamis, 10 November 2011 08.29
    Moderator

Jawaban

  • Coba dicek mbak jumlah record saat query menggunakan linked server, SSIS OLE DB, SSIS Attunity dan TOAD for ORACLE. Saya pernah mengalami kondisi dimana jumlah record yg dihasilkan ketiga cara tersebut berbeda. Yang paling valid saat menggunakan attunity karena jumlah record sama dengan saat query menggunakan TOAD dengan query yg sama. 

     

    SSIS Attunity itu konektor untuk SSIS. Ada yang ke oracle dan teradata.

    Coba cek link berikut  Microsoft Connectors for Oracle and Teradata by Attunity

     

    Dijawab oleh: Cahyo Listyanto


    Agnes Sannie [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Kamis, 10 November 2011 08.35
    Moderator

Semua Balasan

  • pakenya sql berapa?
    Kalo pake 2008 bisa pake synonym, tapi linked servernya tetap harus di filter kayanya table-tablenya.

    Mungkin pakai cara tricky sedikit, buatkan user di oracle yg hanya bisa akses ke schema atau table-table yg dimaksud, kemudian authorisasi linked servernya pakai user itu :)

    Dijawab oleh: Deni Kusnadi


    Agnes Sannie [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Kamis, 10 November 2011 08.32
    Moderator
  •  

    dear mas deny,

    pake sql 2008 R2 mas den. synonym itu step by step nya gm mas den?

    justru saya sulit minta bikinin user ke DBA nya. saya pikir td bisa setting table2 mana saja yg di load berdasarkan userlogin saya. misal user nya Dilla, user ini di oracle di grant loading 100 table, tp saya hanya perlu 10 table dari skema ini.


    Agnes Sannie [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Kamis, 10 November 2011 08.33
    Moderator
  • Hmmm susah juga ya kalo dba-nya kurang kooperatif :)

    Synonym sebenarnya sudah lama ada di oracle, tetapi di sql baru ada di 2008 :)

    Object synonym di database hanya akan membuat metadata dari object-object yang ada di database lain atau server lain supaya memudahkan kita saja, step-step nya tinggal buat pakai script/function create synonym saja, misal

    Create synonym dbo.TableA for DB_A.dbo.TableA

    Maka di database aktif akan terbentuk synonym dgn nama dbo.TableA, dimana kita bisa melakukan query ke synonym tsb instead of query ke table A yg ada di database A secara syntax TSQL

    Jadi kira-kira select * from dbo.TableA, kalo ngga pake synonym jadinya select * from DB_A.dbo.TableA

    Dijawab oleh: Deni Kusnadi


    Agnes Sannie [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Kamis, 10 November 2011 08.33
    Moderator
  • dear mas deny,

    solved mas, akhirnya aku pake SSIS, di sini ternyata jalan walopun ada warning tentang type data..mudah2 an ga ada data yg terpotong


    Agnes Sannie [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Kamis, 10 November 2011 08.34
    Moderator
  • Coba dicek mbak jumlah record saat query menggunakan linked server, SSIS OLE DB, SSIS Attunity dan TOAD for ORACLE. Saya pernah mengalami kondisi dimana jumlah record yg dihasilkan ketiga cara tersebut berbeda. Yang paling valid saat menggunakan attunity karena jumlah record sama dengan saat query menggunakan TOAD dengan query yg sama. 

     

    SSIS Attunity itu konektor untuk SSIS. Ada yang ke oracle dan teradata.

    Coba cek link berikut  Microsoft Connectors for Oracle and Teradata by Attunity

     

    Dijawab oleh: Cahyo Listyanto


    Agnes Sannie [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Kamis, 10 November 2011 08.35
    Moderator