Russell,

I am not sure what's the problem here, but did you try using DTS (for
SQL 2000) or SSIS (SQL 2005) . That would be much simpler than this.

Once you have the linked server, you can declare 2 DB connections and
transfer records
back and forth.

Thanks
Mohan

-----Original Message-----
From: Russell Fleming
[mailto:oracledba-ezmlmshield-x37935954.[Email address protected]
Sent: Tuesday, January 02, 2007 3:47 PM
To: LazyDBA Discussion
Subject: Copying data to SQLS Server from 10g

Nothing but stumbling blocks trying to do this simple task. I have
identical
tables in Oracle and MSS. I need to copy the data over to the MSS from
the
Oracle table. Database link to MSS is created and working properly. I
have
tried the following...

This does not work due to transaction errors.

INSERT INTO [Email Address Removed] FROM oracle_table

And the following does not work due to this error.
Warning(73,17): PLW-07202: bind type would result in conversion away
from
column type

DataRow oracle_table%ROWTYPE ;
CURSOR C1 IS SELECT * FROM oracle_table ;
BEGIN
OPEN C1;
LOOP
FETCH C1 INTO DataRow.col1, DataRow.col2, ....

INSERT INTO [Email Address Removed] (col1, col2, ...)
VALUES (DataRow.col1, DataRow.col2, .... )

END LOOP;
END;

I've tried using TO_CHAR and TO_NUMBER conversions of my record where
appropriate and it still complains about datatypes. Why is this so hard?




---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To Subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html




Oracle LazyDBA home page