HELP:replication from sql server to oracle

HELP:replication from sql server to oracle

Post by GengB » Wed, 12 Sep 2001 10:04:28



All,

I'm trying to replicate from sql server to oracle, and issues come up:
1: DATETIME data type is converted to short DATE , such as
    "mm/dd/yy h,m,s" will be "mm/dd/yy",  after replication;
2: Clustered primary key can't be replicate to oracle , I checked the error
    that's because the sql statement generated by sql server including CLUSTERED

    key word which oracle doesn't support;
3: Sql server only push the first column to oracle as primary key for
Non-clustered
    primary key in it. Such as PK(col1,col2,col3) will be PK(col1) in oracle
after replication.

Could any expert give me some suggestion? Thanks a lot.

Bob

  bgeng.vcf
< 1K Download
 
 
 

HELP:replication from sql server to oracle

Post by Jining H » Wed, 12 Sep 2001 23:05:58


How are you replicating your SQL data to Oracle?  It's hard to tell
where things go wrong without knowing which way you are doing this.
You should probably try DTS and make sure your transformation is done
correctly.

Oracle keeps the time up to seconds.  Try

   select to_char(datefield, 'MM/DD/YY HH:MI:SS') from mytable;

JH


> All,

> I'm trying to replicate from sql server to oracle, and issues come up:
> 1: DATETIME data type is converted to short DATE , such as
>     "mm/dd/yy h,m,s" will be "mm/dd/yy",  after replication;
> 2: Clustered primary key can't be replicate to oracle , I checked the error
>     that's because the sql statement generated by sql server including CLUSTERED

>     key word which oracle doesn't support;
> 3: Sql server only push the first column to oracle as primary key for
> Non-clustered
>     primary key in it. Such as PK(col1,col2,col3) will be PK(col1) in oracle
> after replication.

> Could any expert give me some suggestion? Thanks a lot.

> Bob

> --


 
 
 

HELP:replication from sql server to oracle

Post by GengB » Thu, 13 Sep 2001 19:26:50


SQL's Heterogenous Subscriber is my choice. And I tried to use DTS, but
it seems Heterogemous replication doesn't support it! Do you have any
idea about it? I know oracle keeps the time up to seconds, but after replication
the HH:MI:SS was truncated??


> How are you replicating your SQL data to Oracle?  It's hard to tell
> where things go wrong without knowing which way you are doing this.
> You should probably try DTS and make sure your transformation is done
> correctly.

> Oracle keeps the time up to seconds.  Try

>    select to_char(datefield, 'MM/DD/YY HH:MI:SS') from mytable;

> JH


> > All,

> > I'm trying to replicate from sql server to oracle, and issues come up:
> > 1: DATETIME data type is converted to short DATE , such as
> >     "mm/dd/yy h,m,s" will be "mm/dd/yy",  after replication;
> > 2: Clustered primary key can't be replicate to oracle , I checked the error
> >     that's because the sql statement generated by sql server including CLUSTERED

> >     key word which oracle doesn't support;
> > 3: Sql server only push the first column to oracle as primary key for
> > Non-clustered
> >     primary key in it. Such as PK(col1,col2,col3) will be PK(col1) in oracle
> > after replication.

> > Could any expert give me some suggestion? Thanks a lot.

> > Bob

> > --

  bgeng.vcf
< 1K Download
 
 
 

HELP:replication from sql server to oracle

Post by Jining H » Fri, 14 Sep 2001 04:33:26


I think all you problem can be solved if your Subscriber (oracle
database) already has a pre-defined table to receive the data.  This
will take care of issue 1 and 3.   As for the datetime field, make
sure your Oracle table is using date data type, then the HH:MI:SS will
be kept.

JH


> SQL's Heterogenous Subscriber is my choice. And I tried to use DTS, but
> it seems Heterogemous replication doesn't support it! Do you have any
> idea about it? I know oracle keeps the time up to seconds, but after replication
> the HH:MI:SS was truncated??


> > How are you replicating your SQL data to Oracle?  It's hard to tell
> > where things go wrong without knowing which way you are doing this.
> > You should probably try DTS and make sure your transformation is done
> > correctly.

> > Oracle keeps the time up to seconds.  Try

> >    select to_char(datefield, 'MM/DD/YY HH:MI:SS') from mytable;

> > JH


> > > All,

> > > I'm trying to replicate from sql server to oracle, and issues come up:
> > > 1: DATETIME data type is converted to short DATE , such as
> > >     "mm/dd/yy h,m,s" will be "mm/dd/yy",  after replication;
> > > 2: Clustered primary key can't be replicate to oracle , I checked the error
> > >     that's because the sql statement generated by sql server including CLUSTERED

> > >     key word which oracle doesn't support;
> > > 3: Sql server only push the first column to oracle as primary key for
> > > Non-clustered
> > >     primary key in it. Such as PK(col1,col2,col3) will be PK(col1) in oracle
> > > after replication.

> > > Could any expert give me some suggestion? Thanks a lot.

> > > Bob

> > > --

> --

 
 
 

HELP:replication from sql server to oracle

Post by GengB » Fri, 14 Sep 2001 11:37:30


I had tried pre-defining the table in oracle database, but the problem still
remained there for the first problem?!

Bob


> I think all you problem can be solved if your Subscriber (oracle
> database) already has a pre-defined table to receive the data.  This
> will take care of issue 1 and 3.   As for the datetime field, make
> sure your Oracle table is using date data type, then the HH:MI:SS will
> be kept.

> JH

  bgeng.vcf
< 1K Download
 
 
 

HELP:replication from sql server to oracle

Post by GengB » Fri, 14 Sep 2001 19:58:47


JH,

Thanks for you infomation.
And the issues had been solved, after I took Microsoft ODBC driver for oracle instead of
Oracle ODBC driver used for SQL Heterogeneous Replication.

Bob


> I think all you problem can be solved if your Subscriber (oracle
> database) already has a pre-defined table to receive the data.  This
> will take care of issue 1 and 3.   As for the datetime field, make
> sure your Oracle table is using date data type, then the HH:MI:SS will
> be kept.

> JH

  bgeng.vcf
< 1K Download
 
 
 

1. HELP:replication from sql server to Oracle

All,

I'm trying to replicate from sql server to oracle, and issues come up:
1: DATETIME data type is converted to short DATE , such as
    "mm/dd/yy h,m,s" will be "mm/dd/yy",  after replication;
2: Clustered primary key can't be replicate to oracle , I checked the error
    that's because the sql statement generated by sql server including CLUSTERED

    key word which oracle doesn't support;
3: Sql server only push the first column to oracle as primary key for
Non-clustered
    primary key in it. Such as PK(col1,col2,col3) will be PK(col1) in oracle
after replication.

Could any expert give me some suggestion? Thanks a lot.

Bob

  bgeng.vcf
< 1K Download

2. Working w/Oracle 7.3.4 LONG variables as VARCHAR2 variables in PL/SQL

3. HELP with replication for Oracle Lite to Oracle server

4. Diagrams

5. US-NC-Oracle DBA (Oracle replication Services, SQL Server, NT) DB Basics, Inc # 239S

6. Syabse from the Web

7. Replication from SQL Server 2000 to Oracle 8.1.7: Invalid SQL

8. Troubleshooting cored dumps

9. Oracle's Symmetric Replication VS Replication Server

10. Replication BT SQL SERVER in DMZ and SQL SERVER on LAN

11. Help: SQL Server Replication Problem

12. HELP - Reinstalling SQL Server & restoring merge replication

13. SQL Server 2000 Replication - Please Help