Morning from Canada,
I am hoping I can get some quick information from anyone out there that has a good working knowledge of
Oracle and SQL Server. I will describe my project and road block below.
I am migrating a very large Oracle v.8.1.5 database over to Microsoft SQL Server 2000.
I have utilized DTS "Data Transfer Services" for the majority of the data port using a 3rd party migration
product called Scribe. ps.. the Scribe product has nothing to do with my issues, just advised your for
information purposes only.
In my migration I have a table called "longdescription" which stored pages of free text information
in a ldtext field regarding extended information assosicated with Purchase Orders, Work Order, etc.
Just so you understand the logic, if I am looking at a Purchase Order I can click on a button and view
the long description information visually. There is a primary key that provides the link from PO's to the
long description information and properly displays the information..
The source database is Oracle as I mentioned above and the field type that the infomration is stored in
under Oracle is a type "LONG". The 3rd party Scribe software using DTS shows the source and target field
types in both Oracle and SQL Server as a Long Varchar, but this is not the case.
As I mentioned, in Oracle the field type is "LONG" and the target field type in SQL Server is "TEXT".
I have been in contact with the Oracle folks and the field type "LONG" is no longer supported and they
advised me this field type should not have been used if I want to perform any SQL commands at all with
this field type.
Ok that being said I established an ODBC connection to Oracle and SQL Server and can bring up the
longdesciption table from both databases in a VFP database container with the two connections established.
Visual FoxPro sees these both the Oracle "LONG" and the SQL Server "TEXT" as memo fields.
I can extract the full information out of the Oracle "LONG" field with the STRTOFILE() function
and place it in a txt file. STRTOFILE(LDTEXT,"LDtext.txt")
Now I am wondering how do I place this txt information into the SQL Server 2000 field type "TEXT"?
If I can do this manually in the VFP command window I can then write a VFP program to step through
the Oracle source table and extract the info. and then locate or seek on the matching record in
SQL Server and write in the data. Both Oracle and SQL Server long description tables have a matching primary
key so the matching will work. The DTS migrated all the keys, but failed to migrate the text information.
Any ideas on how to proceed? would really be appreciated.
Is there a better way that dumping the information out to a txt file?
Out of time, out of gas mentally folks.
Pete "the IceMan", from the Great White North of Canada.