Replication from SQL Server 2000 to Oracle 8.1.7: Invalid SQL

Replication from SQL Server 2000 to Oracle 8.1.7: Invalid SQL

Post by Vince » Sat, 22 Feb 2003 13:52:15



I'm trying to push a subscription from SQL Server 2000 SP3 to Oracle
8.1.7

It looks like when SQL Server is sending the DDL to the ODBC or OLEDB,
it is throwing in "CLUSTERED" which is not valid to ORACLE.
"NONCLUSTERED" isn't valid either in ORACLE.  All the text says that
replication from SQL Server to Oracle is a piece of cake.  Please let
me know what I'm doing wrong.  It was trying to pass on all of the
indexes also until I figured out how to turn them off in the Article
tab.

I tried OLEDB and Microsoft ODBC for Oracle.  The Oracle ODBC doesn't
work.  It turns datetime into char(23).

Thanks for any help,

Vincent

ALTER TABLE [AXGLTRANSACTION] ADD
        CONSTRAINT [PK_axGLTransaction] PRIMARY KEY  CLUSTERED
        (
                [GLTransactionID]
        )
GO

[2/20/2003 8:29:05 PM]experio_ms_oracle: ALTER TABLE AXGLTRANSACTION
ADD
        CONSTRAINT PK_axGLTransaction PRIMARY KEY  CLUSTERED
        (
                GLTransactionID
        )

 
 
 

Replication from SQL Server 2000 to Oracle 8.1.7: Invalid SQL

Post by Raj Gil » Sun, 23 Feb 2003 02:31:15


Thats interesting. This problem shouldn't occur with Microsoft ODBC driver
for Oracle. Make sure you are using the latest version of this driver. See
link below for more info:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/repl...
lhet_0jxv.asp

rgill

Quote:> I'm trying to push a subscription from SQL Server 2000 SP3 to Oracle
> 8.1.7

> It looks like when SQL Server is sending the DDL to the ODBC or OLEDB,
> it is throwing in "CLUSTERED" which is not valid to ORACLE.
> "NONCLUSTERED" isn't valid either in ORACLE.  All the text says that
> replication from SQL Server to Oracle is a piece of cake.  Please let
> me know what I'm doing wrong.  It was trying to pass on all of the
> indexes also until I figured out how to turn them off in the Article
> tab.

> I tried OLEDB and Microsoft ODBC for Oracle.  The Oracle ODBC doesn't
> work.  It turns datetime into char(23).

> Thanks for any help,

> Vincent

> ALTER TABLE [AXGLTRANSACTION] ADD
> CONSTRAINT [PK_axGLTransaction] PRIMARY KEY  CLUSTERED
> (
> [GLTransactionID]
> )
> GO

> [2/20/2003 8:29:05 PM]experio_ms_oracle: ALTER TABLE AXGLTRANSACTION
> ADD
> CONSTRAINT PK_axGLTransaction PRIMARY KEY  CLUSTERED
> (
> GLTransactionID
> )


 
 
 

Replication from SQL Server 2000 to Oracle 8.1.7: Invalid SQL

Post by Vincent Chi » Sun, 23 Feb 2003 05:11:41


I installed SP3 for SQL Server.  That should have given me the latest
ODBC and OLEDB drivers.

I've tried this on two different SQL Servers pushing over the same
table.  There must be a config trick that I don't get.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

 
 
 

Replication from SQL Server 2000 to Oracle 8.1.7: Invalid SQL

Post by Vince » Wed, 26 Feb 2003 06:26:25


Here's more info.

There is something about the table that is the source of the problem.
If I run the CREATE script into another table name, recreate all the
indexes, and populate it with the same data, the new table will
replicate just fine.  Does anyone know of any hidden attributes that
could be the cause.  There are no triggers.

Thanks

Vincent


Quote:> I installed SP3 for SQL Server.  That should have given me the latest
> ODBC and OLEDB drivers.

> I've tried this on two different SQL Servers pushing over the same
> table.  There must be a config trick that I don't get.

> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

 
 
 

1. SQL Server 7.0 vs SQL Server 2000: invalid column width

The following code
select '' into tmp
produces a table of the following layout in
SQL Server 7.0:
a varchar(30) null

However the same code in in SQL Server 2000 fails with the following error:
Server: Msg 2732, Level 16, State 1, Line 1
Column 'a' has invalid width: 0.

So my problem is as follows:
We are going to upgrade a Server that runs hundreds of different production scripts, written by dozens of programmers. And I know at least some of them have employed this type of code. Avoiding the failure of these scripts is of paramount importance to me and my remaining at my current employer ;-)
Any input on this help would be greatly appreciated.
The fact that SQL Serer just defaults to a column width of 30 makes me think that there could be something like a default column width, which could be set in SQL Server 2000. Did anyone ever come across a solution for this?

Thanks in advance,
Dorian

**********************************************************************

Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources...

2. US-NY: ORACLE DBA >>>>>URGENT

3. Replication between SQL Server 2000 and SQL Server 7.0

4. ALTERing A Table At Runtime

5. SQL Server 2000, Oracle and VMS data replication.

6. Oracle Portal portlet provider problem

7. SQL Server 2000 replication into Oracle 8.1.7 errors

8. VFP5 Latching Command Button Control ???

9. Replication SQL CE and SQL Server 2000

10. Replication between a Pervasive SQL database and SQL Server 2000

11. merge replication performance for SQL Server 2000 and MSDE 2000

12. Access 2000/Sql Server 7.0/2000 Replication

13. Access 2000 and SQL Server 2000 Replication