May 7, 2008 at 10:33 pm
I am trying to download and transfer data which is stored in an Oracle view on another machine to the database which is stored in SQL Server. What are the initial steps that need to be followed.
What type of scripts need to be written to automate such a process. Urgent help will be appreciated. Thanks
May 8, 2008 at 3:42 pm
You will want to install the Oracle Client on the SQL Server.
You will then want the Oracle DBA to create a login id and grant it access to the view.
Then verify the Oracle Client can communicate and query the view using the login id.
You may need to reboot the SQL Server at this point.
In SQL Server, create a linked server using the Oracle OLE DB driver (Product 'Oracle') or the Microsoft OLE DB (Product 'MSDORA') driver adding the Security context of the Oracle login.
Using the OPENQUERY syntax (linked server, 'sql statement') or a select statement using the linked servername..instance.owner.view create a query that could be used in job or stored procedure.
Hope that helps.
-jerimy stanley
MCDBA
May 9, 2008 at 6:08 am
Here is a live example taken from my computer which has both SS2000 and Oracle 9 installed.
In Oracle
1. logon with system and create a user called user1 with password user1 giving him resource privileges.
2. logon with user1 using your favorite front end and run
create table t1(
firstname varchar(15),
lastname varchar(25));
insert into t1 values('Pinco','Pallino');
In Query Analyzer run the following:
sp_addlinkedserver ORALINK,Oracle,MSDAORA,ORCL
go
sp_addlinkedsrvlogin ORALINK,false,null,USER1,USER1
go
Here ORCL refers to the Oracle Client tnsnames.ora definition pointing to an Oracle instance. You should leave the rest as is.
In Query Analyzer you can now do:
select * from ORALINK..USER1.T1
go
Or you can physically transfer the table:
select *
into t1
from ORALINK..USER1.T1
go
Beware:
- The table name T1 and user name USER1 need to be written in upper case on the SQL Server side of things.
- transferring columns of type blob to image will not work with this - at least in my experience - had to use some VB script to do that part
May 11, 2008 at 5:19 am
Thanks, but I have an error message when I try to execute a query. Any idea what this is???
This is the error message
OLE DB provider "MSDAORA" for linked server "oralink" returned message "Oracle client and networking components were not found. These components are supplied by Oracle Corporation and are part of the Oracle Version 7.3.3 or later client software installation.
Provider is unable to function until these components are installed.".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "MSDAORA" for linked server "oralink".
May 11, 2008 at 9:01 am
As Jerimy said, you need to install the Oracle Client on the computer running your SQL Server instance. To test the presence of the Oracle client and connectivity to an Oracle database run up a command window and type
TNSPING name
If it says that 'TNSPING' is not a recognized command then you don't have an Oracle Client installed.
For 'name' you should specify an alias defined in tnsnames.ora. The last line from this should return
OK (xxx msec)
Do you get this response?
May 12, 2008 at 2:21 am
Hi,
Thanks Michael and Jeremy for the responses. The problem is not yet solved.
The Oracle Client is installed on my system and I have also created a dsn named 'Oracle.dsn', I can connect using the dsn.
I also did a TNSPING and it gives me a response OK.
My Oracle Database is 10g and SQL Server is 2005. Is there anything else that needs to be done .
I need to write an Query in SQL Server so that I can select the view in Oracle. Thanks
May 12, 2008 at 3:07 am
I tried it on SQL Server 2005 and Oracle 10g (10.2.0.1) and I had to change nothing with respect to what I described previously.
I'm not too clear what you mean by 'Oracle dsn'. Do you mean ODBC data source? OK, this lets you make a connection test. What did you put in for TNS service name?
Can you publish the block in your tnsnames.ora file that pertains to your oracle connection.
Can you connect with this piece of VB script using the command 'cscript test.vbs'? Remember to change the DATA SOURCE value to the name used in your tnsnames.ora file.
Option Explicit
Dim strConn
strConn = "PROVIDER=ORAOLEDB.ORACLE;DATA SOURCE=ORCL;USER ID=USER1;PASSWORD=USER1"
Set objConn = CreateObject("ADODB.Connection")
objConn.Open strConn
WScript.Echo "Connection established"
May 12, 2008 at 4:16 am
Hi Michael,
The problem is solved and I am able to query the Oracle view. Thanks a ton to you.
When creating the linked server the fourth parameter, which is the hostname was wrong. I had mentioned the host name as the server where Oracle database is installed.Now I have changed that to the alias which is in TNSNAMES.ora.
Might need your help again.
Thanks again 🙂
May 12, 2008 at 8:16 am
I found this article useful:
I had some columns with incompatible data types, so I ended up using SSIS to query oracle and convert some columns, then import into SQL Server.
This article also helped me deal with BLOBS.
http://www.dba-oracle.com/t_convert_blob_varchar_datatype.htm
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply