DTS using multiple Datasources

• Ninja's_RGR'us

  SSC Guru

  Points: 294069

  More actions

  November 4, 2004 at 7:22 pm

  #529040

  I think I got you mixed up. OPENROWSET can be used in two ways :

  in the first exemple I showed you I used all the information required to setup a linked server (servername, username, pass and catalog...), however once you have the linked server setup you can skip this operation and only use the linked server name as a reference.

  I found this exemple in the books online (for oracle :-)) :

  (you can find the whole document under "OPENQUERY")

  Examples

  This example creates a linked server named OracleSvr against an Oracle database using the Microsoft OLE DB Provider for Oracle. Then this example uses a pass-through query against this linked server.

  Note This example assumes that an Oracle database alias called ORCLDB has been created.

  EXEC sp_addlinkedserver 'OracleSvr',

  'Oracle 7.3',

  'MSDAORA',

  'ORCLDB'

  GO

  SELECT *

  FROM OPENQUERY(OracleSvr, 'SELECT name, id FROM joe.titles')

  GO

  I think this is the simplest way for you to do the task at hand.

• kfschaefer

  SSC Eights!

  Points: 838

  More actions

  November 5, 2004 at 9:20 am

  #529121

  Thanks for all your effort, however, I found the fix I needed.

  The solution is that the Server/Field names proper syntax should be:

  OracleServer..Schema.fieldname

  Thanks again.

• Ninja's_RGR'us

  SSC Guru

  Points: 294069

  More actions

  November 5, 2004 at 9:34 am

  #529122

  Good.. I just wish I had had an Oracle server to do some tests.. would have saved us a lot of typing :-).