Access Oracle without Linked Server

[smedvid]

Is it possible to access an Oracle Data Source without using SQL Server Linked Servers? I am using SQL Server 2008... converting something from SQL Server 2000 where I used a Linked Server.

I need to pull a small amount of data; and seems their is red tape that prevents me from using linked servers. The recommend process is to download data from Oracle into a text file; then write a DataStage ETL process (Not allowed to use SSIS) and use Tidal to schedule a job run; based on a text file located in a directory.

Any help is appreciated! A simple little query to pull data appears to be turning into a nightmare project; that will be time consuming and costly... UGH!!!!!

Thanks in Advance!


Steve Medvid
IT Consultant & Web Master

http://www.saveourfarm.com

Chester County, PA Residents
Please Show Your Support...

 

[gmmastros]

I would suggest you use the [!]OpenRowset[/!] function within your SQL Server code. This should allow you to connect to an Oracle database, but it does require that you have the correct Oracle drivers on the SQL Server machine.



-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[smedvid]

Apppears DBA Team has locked those features...
They really want to make life more complicated... Just does not seem logical or efficient...

Msg 15281, Level 16, State 1, Line 2
SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off as part of the security configuration for this server.

Steve Medvid
IT Consultant & Web Master

http://www.saveourfarm.com

Chester County, PA Residents
Please Show Your Support...

 

[gmmastros]

I could be wrong, but I think SQL2005 does that by default. Perhaps you should check with your DBA team to see if it can be turned on.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[fredericofonseca]

but.. if you are using Datastage why not connect it directly to oracle as part of the process?

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886