SQL Server 2005 to Oracle 10g - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SSIS and Import/Export (2005)

SQL Server 2005 to Oracle 10g

Author

Topic

Nith
Starting Member

17 Posts

Posted - 2010-05-20 : 02:03:05

Dear buddies,

I need to export a lot of tables and data to Oracle 10g. I am using SQL Server 2005 and Oracle 10g.

I right clicked on my database -> Tasks -> Export Data -> Furnished with SQL Server's details and for Oracle, chose Microsoft OLD DB Provider for Oracle and I am receiving error: Test Connection failed because of an error in initializing provider. Unspecified error.

When I was browsing checking for details, came across this site:
http://support.microsoft.com/kb/244661

Then I downloaded Oracle Provider for OLEDB.

Not sure how to configure it yet.

But is my assumption that the driver provided by Microsfot won't work for Oracle 10g, correct?

Please advice me.

Nith

Kristen
Test

22859 Posts

Posted - 2010-05-20 : 03:34:18

We had to install all the ORAnn client drivers and configure the whole lot before we could connect SQL to Oracle. The recent ones I've been involved with other people have set up, so I don;t know if that is still the case, but it certainly used to be pretty painful (i.e. better done by a person used to setting up Oracle drivers)

I've just checked a client's server that was set up yesterday with a linked server to Oracle. The drivers are there, all 750MB of them!, in a folder

C:\Oracle\product\11.2.0\client_1

Kristen
Test

22859 Posts

Posted - 2010-05-20 : 03:38:25

Only other one I could find easily is much older, that was:

C:\oracle\ora81

and a more modest 300MB

Nith
Starting Member

17 Posts

Posted - 2010-05-20 : 06:26:00

Dear Kristen,

First of all thank you for your timely reply.

I managed to use SQL Server's export tool to move the data (maximum 100 at a time) to Oracle without much hassle but looking for a way it can be redone just by using SQL (thats what my boss wants).

I had to download Oracle rpovider OLEDB from oracle. Without that, the tool was not working with Microsoft provider OLEDB for oracle.

Can you please guide me on SSIS sql server? Would it help to convert the entire thing to SQL script which can be used in Oracle.

If I use the scripting options, they just create the script for me in SQL Server, which I can't reuse in Oracle to create all the tables again.

Any suggestion on this matter?

Nith

Kristen
Test

22859 Posts

Posted - 2010-05-20 : 09:21:58

Dunno about SSIS.

However, you could set up a LINKED SERVER then you can do the lot in SQL


INSERT INTO OracleLinkedServer.OracleInstance.OracleTable
SELECT *
FROM MyTable

although SSIS will "batch" the transfer, which would be better it you have a lot of rows to transfer

Nith
Starting Member

17 Posts

Posted - 2010-05-20 : 21:16:57

Dear Kristen,

Thanks once again. Any tutorials or reference on how I can create the Linked server?

Thanks in advance.

Regards,

Nith

Nith
Starting Member

17 Posts

Posted - 2010-05-20 : 22:17:44

Dear Kristen,

To the best of my understanding after going through a few sites, Linked server looks more like a link to access the tables in Oracle and I couldn't understand how I can move my data from SQL Server to Oracle.

Please guide me.

Thank You.

Nith

Kristen
Test

22859 Posts

Posted - 2010-05-21 : 01:27:30

"I couldn't understand how I can move my data from SQL Server to Oracle."

My example above is an insert into Oracle database.

But as I said I think SSIS would be better because of its ability to batch the inserts (unless you are only inserting a small number of rows at a time - up to 100,000 rows is probably fine, maybe more, depends on you hardware etc.

Nith
Starting Member

17 Posts

Posted - 2010-05-23 : 22:30:39

Thanks a lot Kristen.

I am using Oracle 10g and tried to use create the linked server in this way:

EXEC sp_addlinkedserver
'OracleLinkedServer', 'Oracle',
'OraOLEDB.Oracle', 'testmig'

EXEC sp_addlinkedsrvlogin
'OracleLinkedServer ', false,
'sa', 'dbo',
'dbo'

SELECT * FROM
OracleLinkedServer...dual;

Instead of 'OraOLEDB.Oracle', I also tried to use 'MSDAORA', msdaorar, ORAOLEDB and also tried Microsoft's ODBC driver.

I received this error: Msg 7403, Level 16, State 1, Line 1
The OLE DB provider "OraOLEDB.Oracle" has not been registered.

I followed the steps given in this site:
http://www.orafaq.com/forum/t/38330/0/
to register the driver but in vail.

Please guide me.

Thanks.

Nith

Kristen
Test

22859 Posts

Posted - 2010-05-24 : 05:18:12

Looks right to me. I haven't checked the article, but here's my "sample" Make Linked Server code - which looks the same as yours I reckon??


EXEC sp_addlinkedserver 
	@server = 'MyRemoteServer'	-- local name of the linked server to create.
					-- If data_source is not specified, server is the actual name of the instance
,	@srvproduct = 'SQL Server'	-- product name of the OLE DB data source to add as a linked server
					-- If "SQL Server", provider_name, data_source, location, provider_string, and catalog do not need to be specified.
-- Execute ONLY to here IF you are connecting two SQL servers
	,@provider = 'SQLOLEDB'		-- unique programmatic identifier of the OLE DB provider (PROGID) 
	,@datasrc = 'MyRemoteServer'	-- name of the data source as interpreted by the OLE DB provider (DBPROP_INIT_DATASOURCE property)

-- Create Linked Server Login
EXEC sp_addlinkedsrvlogin @rmtsrvname = 'MyRemoteServer' 
,	@useself = 'false'		-- true=Connect using current UserID/Password, false=use rmtuser/rmtpassword below
,	@locallogin = 'MyUserID'	-- NULL=All local logins use this remote login account, otherwise local login UserName being set up (repeat for each one required)
-- Execute ONLY to here IF @UseSelf='TRUE' (above)
,	@rmtuser = 'MyUserID'	-- UserName on Remote corresponding to this @LocalLogin.   
,	@rmtpassword = 'MyPassword'	-- Ditto password

none of that points to anything that might give the message "The OLE DB provider "OraOLEDB.Oracle" has not been registered." though - but, sorry to say

,
I've always found it to be trail and error and lots of smoke-and-mirrors

Nith
Starting Member

17 Posts

Posted - 2010-05-24 : 22:22:34

Hi Kristen,

Thank You Very Much for your prompt reply.

When you use SQLOLEDB, it doesn't raise the problem, only when I use OraOLEDB.Oracle, OraOLEDB, MSDAORA, MSDASQL or msdaorar, I receive that error.

Having a Linked Server within Microsoft products shouldn't be a problem I suppose.

Any suggestion if there is an alternative to move tables and data from SQL Server to Oracle 10g?

Nith

Kristen
Test

22859 Posts

Posted - 2010-05-25 : 02:43:39

Export and Reimport?

SSIS can export a table to some form of delimited file. Probably there is something similar on Oracle that can do the bulk import?

Your can do it programatically, either in SQL or from the command line, e.g. using BCP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources