How to create SQL Views to gather data from an ORACLE database?

• rva1945

  SSC Veteran

  Points: 206

  More actions

  November 22, 2007 at 8:54 am

  #179401

  Is it possible? We're running SQL 2000, and 2005 soon.

  Thanks

• Jeff Moden

  SSC Guru

  Points: 1004592

  More actions

  November 22, 2007 at 9:37 pm

  #753514

  Haven't tried it in a view before... you must either create a Linked Server or use OPENROWSET or OPENDATASOURCE.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• Old_D

  SSC Veteran

  Points: 253

  More actions

  November 23, 2007 at 8:28 am

  #753649

  First create a linked server to Oracle server. Then select *

  into MyTableName

  from openquery(YourLinkedServerName, 'place query text here') FYI Oracle does not seem to understand " quotes so I always use ''two single ticks.

• Vijaya Kadiyala

  SSCrazy Eights

  Points: 9149

  More actions

  February 24, 2008 at 2:16 pm

  #781448

  Hi,

  You can achieve this by having linked server in SQL Server.

  Check out the below link

  http://www.sqlmag.com/Article/ArticleID/22264/sql_server_22264.html

  Thanks -- Vj

  Thanks -- Vijaya Kadiyala
  www.dotnetvj.com
  SQL Server Articles For Beginers