Although I don't know much about MSS it seems that would be the way to go.
But the client is migrating to 10g and has directed no development activity
on that server. Basically there a Crystal Reports which will be recoded to
hit Oracle data but in the meantime I need to copy tables over there so that
reports continue to work. When server is no longer needed the data transfer
will be disabled.


----Original Message Follows----
From: "Chozhangath Mohan "
<oracledba-ezmlmshield-x49270081.[Email address protected]
To: "LazyDBA Discussion" <[Email address protected]
Subject: RE: Copying data to SQLS Server from 10g
Date: Tue, 2 Jan 2007 16:05:13 -0600

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





---------------------------------------------------------------------
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