如何使用Linked Server連線Oracle與SQL Server

How   to   use   SQL   Analyzer   with   the   Oracle   Provider   for   OLE   DB   and   SQL   Server   2000  
   
  PURPOSE    
  -------    
  The   purpose   of   this   document   is   to   show   the   steps   needed     to   use   the   Microsoft   SQL   Analyzer   query   tool,   which   comes   with   Microsoft   SQL   Server,   with   the   Oracle   Provider   for   OLE   DB.   This   will   enable   the   user   to   use   the   4   part   query   naming   convention   in   SQL   Analyzer.        
  SCOPE   &   APPLICATION    
  -------------------    
  This   note   is   intended   for   all   audiences   that   have   some   knowledge   of   SQL   Analyzer,   Oracle   Provider   for   OLE   and   Microsoft   SQL   Server.     How   to   use   Microsoft   SQL   Analyzer   with   the   Oracle   Provider   for   OLE   DB    
  ------------------------------------------------------------------------     Configuration:    
  ==============    
  Microsoft   SQL   Server   2000   SP   1   or   2   Microsoft   Windows   2000   Server   SP   2   Oracle   Provider   for   OLE   DB   8.1.7.3.0   Oracle   Client   8.1.7.3.0   Oracle   Database   8.1.7.3.0       Instructions   to   Setup   the   Linked   Server   Connection:   ===================================================    
  1.   Open   SQL   Server   Enterprise   Manager    
  2.   Click   on   the   +   to   expand   the   tree   for   the   database   server.         You   will   need   to   expand   it   three   times.           Note:   If   the   SQL   Server   does   not   appear   here,   you           will   need   to   right   click   and   go   through   the   Wizard           for   SQL   Server   Registration.         -   The   first   level   of   the   tree   is   called   Microsoft   Sql   Server,             The   second   level   of   the   tree   is   called   SQL   Server   Group,             and   the   third   level   of   the   tree   is   your   SQL   Server   Database.             For   these   instructions   we   will   call   our   SQL   Server   Database,             'Scott'.    
  3.   Under   the   Scott   Database,   click   on   the   +   to   expand   the   tree   control           for   'Security'    
  4.   Under   Security,   right   click   on   Linked   Servers   and   select           "New   Linked   Server"  
  5.   In   the   Linked   Server   Properties   do   the   following:  
        a.   Type   in   a   Linked   Server   Name,   for   this   example   we   will   call   it         'Tiger'  
        b.   Under   Server   Type   select   Other   data   source      
            -   For   the   provider   name   select   Oracle   Provider   for   OLE   DB.  
        c.   In   the   Data   Source   field,   type   in   the   Oracle   Service   name   (SQL   Net   Alias)  
        d.   Leave   the   Product   Name   and   Provider   String   blank.          
        e.   Click   on   the   Provider   Options   button.     -   Check   the   check   boxes   for   "Dynamic    
              Parameters"   and   "Allow   InProcess"          
        f.   Click   Apply   &   then   click   OK.   -   The   Provider   Options   dialog   closes.  
  6.   Go   to   the   "Security"   tab   in   the   Linked   Server   Properties.          
        a.   Select   the   radio   button   "Be   made   using   this   security   context"   at   the   bottom   of   the   box.          
        b.   Type   in   the   User   ID   (your   Oracle   login),   ie.   Scott   in   the     "Remote   login"   field   and   your   Password,   ie.   Tiger   in   the                 "With   Password"   field.    
  7.   Go   to   the   "Server   Options"   tab   in   the   Linked   Server   Properties.        
        a.   While   leaving   the   default   selections   selected,   also   check   the   checkbox   for   "Collation   Compatible".            
        b.   Click   OK   This   will   create   your   Linked   Server   named   Tiger.    
  Test   the   Linked   Connection    
  ==================================    
  To   test   the   Linked   Server,   open   up   Microsoft   Query   Analyzer     (It   is   found   under   the   Microsoft   SQL   Server   menu   option).    
  1.   Select   your   SQL   Server   in   the   Connect   to   SQL   Server   box.            
          a.   Since   we   have   the   SQL   Server   installed   on   the   same   machine,   we   select   (local).          
          b.   Select   the   appropriate   Connect   using   option.   This   is   determined   when   you              
                Installed/Created   your   SQL   Server   database.   For   our   example   here   we   use              
                Windows   Authentication.   And   click   on   the   Ok   button.    
  2.   In   the   Query   screen   you   can   type   a   query.   The   query   will   follow     this   format:   SELECT   *   FROM   ...          
  IE.   SELECT   *   FROM   TIGER..SCOTT.EMP  
  3.   If   you   have   the   default   scott/tiger   schema   installed   in   your   Oracle   database   you   can   use   the   following   query:     SELECT   *   FROM   TIGER..SCOTT.EMP  
  4.   Type   it   into   the   Query   box.    
  5.   Click   on   the   Green   Arrow   to   run   the   query.    
  6.   You   should   see   the   contents   of   the   Emp   table   in   the   Grid.