The data will still reside in both places -- DTS or SSIS just does the
data conversion for you and makes it painless. That way you can make
the changes to the reports you need as well as maintain the data without
having to exist in an all or nothing world. And you can set up DTS (not
sure about SSIS) to run at regular intervals and refresh the data.

Paul Blosser
System Developer
Abanco Int'l, LLC.
847-227-2301x2421

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

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




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