DTS using Oracle OLE Provider - 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 2000 Forums

Import/Export (DTS) and Replication (2000)

DTS using Oracle OLE Provider

Author

Topic

GPR02
Starting Member

1 Post

Posted - 2007-05-01 : 18:11:13

Here's what I have: SQL 2000 - Setting up a DTS package, linking to Oracle 9i via Oracle Provider for OLE DB. (The oracle client is installed on my machine as well as the SQL Server box).

Everything works fine - I can get data from Oracle no problem. My issue is the syntax. I understand that with Oracle 9i, I should be able to use ANSI "left join" syntax, as well as Case statements. No matter what I do, I can't get that syntax to work in my DTS package. Everything defaults to the old join syntax (with the (+)) and the case statement just chokes and dies. If I change it to "left join", it yells at me.

If I run the same SQL statement through the Oracle SQL Plus utility, it works just fine. I'm using the Oracle SQL Developer as well, and the newer syntax works ok there too. I just can't seem to get the newer stuff to work in DTS.

Why such a big deal? I've got some nasty SQL statements from a different source I need to move into my DTS and it uses a ton of case statements (and left joins). I'd rather not have to re-write it all from scratch.

Any hints would be most appreciated.

Thanks,
G.

okrocker
Starting Member

3 Posts

Posted - 2007-05-03 : 10:17:57

I have a ton of DTS packages in SQL2k with Oracle 8 & 9 connections. The trick is not to think of them as Oracle...I use Transact-SQL only. Building them, I couldn't resist trying to get better performance thinking the Oracle native code would work better. Think again! Everytime I managed to get Oracle SQL to work...I'd run it through the profiler only to find out that it Transact-SQL worked better...good luck!

dal_hit
Starting Member

2 Posts

Posted - 2007-05-31 : 14:19:04

hi GPR02,

i have questions regarding transferring data from Oracle 9i to SQL SERVER 2000 by DTS package.

let me explain you situation.

1- Oracle DBA is taking export of EMP table data & saving as EMP_DATA.DMP

2- I am trying to import data from this dump file into SQl SERVER 2000 with help of DTS

Now when i am trying to import i am getting following problems
-1- which source type should i choose - a text file or a Microsoft OLEDB provider for ORACLE

If i choose text file as source then DTS does not recognize file formate.

If i choose OLEDB provider for Oracle, then it asks for ORACLE Login details. Now as i am not directly connected with PC running Oracle Server, i cannot login. DBa issending DMP file by means of E-Mail attachment.

I would appreciate any help/link/material in this regards

Thank
Dal Hit

quote:
Originally posted by GPR02

Here's what I have: SQL 2000 - Setting up a DTS package, linking to Oracle 9i via Oracle Provider for OLE DB. (The oracle client is installed on my machine as well as the SQL Server box).

Everything works fine - I can get data from Oracle no problem. My issue is the syntax. I understand that with Oracle 9i, I should be able to use ANSI "left join" syntax, as well as Case statements. No matter what I do, I can't get that syntax to work in my DTS package. Everything defaults to the old join syntax (with the (+)) and the case statement just chokes and dies. If I change it to "left join", it yells at me.

If I run the same SQL statement through the Oracle SQL Plus utility, it works just fine. I'm using the Oracle SQL Developer as well, and the newer syntax works ok there too. I just can't seem to get the newer stuff to work in DTS.

Why such a big deal? I've got some nasty SQL statements from a different source I need to move into my DTS and it uses a ton of case statements (and left joins). I'd rather not have to re-write it all from scratch.

Any hints would be most appreciated.

Thanks,
G.

dal_hit
Starting Member

2 Posts

Posted - 2007-05-31 : 14:19:57

hi GPR02,

you can reply me at dal_hit@yahoo.com

thanks
dal hit

quote:
Originally posted by GPR02

Here's what I have: SQL 2000 - Setting up a DTS package, linking to Oracle 9i via Oracle Provider for OLE DB. (The oracle client is installed on my machine as well as the SQL Server box).

Everything works fine - I can get data from Oracle no problem. My issue is the syntax. I understand that with Oracle 9i, I should be able to use ANSI "left join" syntax, as well as Case statements. No matter what I do, I can't get that syntax to work in my DTS package. Everything defaults to the old join syntax (with the (+)) and the case statement just chokes and dies. If I change it to "left join", it yells at me.

If I run the same SQL statement through the Oracle SQL Plus utility, it works just fine. I'm using the Oracle SQL Developer as well, and the newer syntax works ok there too. I just can't seem to get the newer stuff to work in DTS.

Why such a big deal? I've got some nasty SQL statements from a different source I need to move into my DTS and it uses a ton of case statements (and left joins). I'd rather not have to re-write it all from scratch.

Any hints would be most appreciated.

Thanks,
G.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources