HELP! Create view in SQL Server 2000 to show data from ODBC datasource

[ije]

Hello,

I am in need of assistance from the community here!!

Can someone show me how to create a view ( if it is possible ) accessing data from another source, via an ODBC connection.

I am trying to create a view which looks up data from an Oracle RDB system.

Can someone PLEASE help - this is important to us here!!

ije

 

[osjohnm]

Hi

You will have to setup a linked server to the Oracle server.
Look in BOL on how to setup a linked server.
You will need to exec sp_addlinkedserver to set this up or you can do it through enterprise manager.

USE master
GO
EXEC sp_addlinkedserver
@server = 'OracleTest,
@srvproduct = 'Oracle',
@provider = 'MSDAORA',
@datasrc = 'MyServer'
GO

If you use sp_addlinked server then you need to run sp_addlinkedsrvlogin to setup the user that has rights to the oracle server.

sp_addlinkedsrvlogin [ @rmtsrvname = ] 'OracleTest'
[ , [ @useself = ] 'useself' ]
[ , [ @locallogin = ] 'locallogin' ]
[ , [ @rmtuser = ] 'rmtuser' ]
[ , [ @rmtpassword = ] 'rmtpassword' ]

Then you can create a view which queries the linked server.
You will need to use the openquery statement since this is oracle:

CREATE VIEW OracView
AS
SELECT *
FROM OPENQUERY(OracleTest,
'SELECT Column1,
Column2
FROM table1')
GO

If you use EM then under security you will find linked servers, right click to add one and then just enter the details that it asks for.

 

[ije]

thanks - i did it via the Enterprise Manager - and it all just worked. Too easy when you know how!!

Thanks heaps for the assistance!!