SQL Server 2000 Migrate to Oracle

>C Stabri wrote:
>
>> I am trying to get some tables out of SQL Server 2000 and into
Oracle
>> 9i. However I am encountering problems.
>>
>> 1. Many of the tables and column names in SQL server are > than 30
>> Characters which meansa any auto inport I do fails.
>> Is there a way to get around this.


Hi C Stabri,
on my web site http://www.peternolan.com/downloads.htm#bm006

you will find a suite of free Data Warehousing Utilities. The source
code for most of them are also published here.

One of them is the 'Data Transfer Utility' (DTU). I wrote the DTU for
doing the kinds of stuff you are referring to.....pushing data around
different databases as easily as possible because I can't afford an
ETL tool to use as part of my consulting business...

The data transfer utility will move data from one ODBC source to
another ODBC target. The freeby executables are windows only but you
can write to a unix target via ODBC if that is what you need to do, or
create a delimited file that you can transfer to unix.

For your particular problem you can use one of two approaches.

The DTU moves data based on column name or column position. If you
want to move by column name you are going to have to put views over
the top of your SQL Server tables to present views that 'look like'
the views you want to load in Oracle. Or, you can unload the data from
SQL Server as delimited files and move it to the Oracle tables based
on column number only.

You can migrate the data in a self defining internal format the DTU
uses or in some form of delimited file. But if you use delimited files
as the target you have to write the load statement for Oracle
yourself. It is also possible to take a delimited file and convert it
to the self describing internal format and then run it as a set of
inserts, thus avoiding having to write the load statements.

If you are doing major volumes let me know as the 'alpha' version is
around 2.5 times faster than the 'production' version so you might
want the newer 'alpha' version.

A word on data types. Oracle and SQL Server data types are NOT the
same even when they have the same name....eg NUMERIC does not mean the
same in the two databases.

The DTU unloads data into C character strings and then passes them to
the target ODBC DSN specifying the data type of the target. If the
target database can convert the character string to the target data
type the insert will work. If it cannot, ODBC will give you a cast
error. This is the most common problem I have with this utility. To
solve cast errors you need to format the data coming out of the source
table into something that the target ODBC DSN can understand.

Lastly, there is a manual and a user group for this
software....'freebie' means I don't do a whole lot of support for
it...;-)

I've not seen anything else out there that is free, easy to use, with
source code supplied....so you might want to try DTU.

Best Regards

Peter Nolan

>C Stabri wrote:
>
>> I am trying to get some tables out of SQL Server 2000 and into
Oracle
>> 9i. However I am encountering problems.
>>
>> 1. Many of the tables and column names in SQL server are > than 30
>> Characters which meansa any auto inport I do fails.
>> Is there a way to get around this.


Hi C Stabri,
on my web site http://www.peternolan.com/downloads.htm#bm006

you will find a suite of free Data Warehousing Utilities. The source
code for most of them are also published here.

One of them is the 'Data Transfer Utility' (DTU). I wrote the DTU for
doing the kinds of stuff you are referring to.....pushing data around
different databases as easily as possible because I can't afford an
ETL tool to use as part of my consulting business...

The data transfer utility will move data from one ODBC source to
another ODBC target. The freeby executables are windows only but you
can write to a unix target via ODBC if that is what you need to do, or
create a delimited file that you can transfer to unix.

For your particular problem you can use one of two approaches.

The DTU moves data based on column name or column position. If you
want to move by column name you are going to have to put views over
the top of your SQL Server tables to present views that 'look like'
the views you want to load in Oracle. Or, you can unload the data from
SQL Server as delimited files and move it to the Oracle tables based
on column number only.

You can migrate the data in a self defining internal format the DTU
uses or in some form of delimited file. But if you use delimited files
as the target you have to write the load statement for Oracle
yourself. It is also possible to take a delimited file and convert it
to the self describing internal format and then run it as a set of
inserts, thus avoiding having to write the load statements.

If you are doing major volumes let me know as the 'alpha' version is
around 2.5 times faster than the 'production' version so you might
want the newer 'alpha' version.

A word on data types. Oracle and SQL Server data types are NOT the
same even when they have the same name....eg NUMERIC does not mean the
same in the two databases.

The DTU unloads data into C character strings and then passes them to
the target ODBC DSN specifying the data type of the target. If the
target database can convert the character string to the target data
type the insert will work. If it cannot, ODBC will give you a cast
error. This is the most common problem I have with this utility. To
solve cast errors you need to format the data coming out of the source
table into something that the target ODBC DSN can understand.

Lastly, there is a manual and a user group for this
software....'freebie' means I don't do a whole lot of support for
it...;-)

I've not seen anything else out there that is free, easy to use, with
source code supplied....so you might want to try DTU.

Best Regards

Peter Nolan

>C Stabri wrote:
>
>> I am trying to get some tables out of SQL Server 2000 and into
Oracle
>> 9i. However I am encountering problems.
>>
>> 1. Many of the tables and column names in SQL server are > than 30
>> Characters which meansa any auto inport I do fails.
>> Is there a way to get around this.


Hi C Stabri,
on my web site http://www.peternolan.com/downloads.htm#bm006

you will find a suite of free Data Warehousing Utilities. The source
code for most of them are also published here.

One of them is the 'Data Transfer Utility' (DTU). I wrote the DTU for
doing the kinds of stuff you are referring to.....pushing data around
different databases as easily as possible because I can't afford an
ETL tool to use as part of my consulting business...

The data transfer utility will move data from one ODBC source to
another ODBC target. The freeby executables are windows only but you
can write to a unix target via ODBC if that is what you need to do, or
create a delimited file that you can transfer to unix.

For your particular problem you can use one of two approaches.

The DTU moves data based on column name or column position. If you
want to move by column name you are going to have to put views over
the top of your SQL Server tables to present views that 'look like'
the views you want to load in Oracle. Or, you can unload the data from
SQL Server as delimited files and move it to the Oracle tables based
on column number only.

You can migrate the data in a self defining internal format the DTU
uses or in some form of delimited file. But if you use delimited files
as the target you have to write the load statement for Oracle
yourself. It is also possible to take a delimited file and convert it
to the self describing internal format and then run it as a set of
inserts, thus avoiding having to write the load statements.

If you are doing major volumes let me know as the 'alpha' version is
around 2.5 times faster than the 'production' version so you might
want the newer 'alpha' version.

A word on data types. Oracle and SQL Server data types are NOT the
same even when they have the same name....eg NUMERIC does not mean the
same in the two databases.

The DTU unloads data into C character strings and then passes them to
the target ODBC DSN specifying the data type of the target. If the
target database can convert the character string to the target data
type the insert will work. If it cannot, ODBC will give you a cast
error. This is the most common problem I have with this utility. To
solve cast errors you need to format the data coming out of the source
table into something that the target ODBC DSN can understand.

Lastly, there is a manual and a user group for this
software....'freebie' means I don't do a whole lot of support for
it...;-)

I've not seen anything else out there that is free, easy to use, with
source code supplied....so you might want to try DTU.

Best Regards

Peter Nolan

>C Stabri wrote:
>
>> I am trying to get some tables out of SQL Server 2000 and into
Oracle
>> 9i. However I am encountering problems.
>>
>> 1. Many of the tables and column names in SQL server are > than 30
>> Characters which meansa any auto inport I do fails.
>> Is there a way to get around this.


Hi C Stabri,
on my web site http://www.peternolan.com/downloads.htm#bm006

you will find a suite of free Data Warehousing Utilities. The source
code for most of them are also published here.

One of them is the 'Data Transfer Utility' (DTU). I wrote the DTU for
doing the kinds of stuff you are referring to.....pushing data around
different databases as easily as possible because I can't afford an
ETL tool to use as part of my consulting business...

The data transfer utility will move data from one ODBC source to
another ODBC target. The freeby executables are windows only but you
can write to a unix target via ODBC if that is what you need to do, or
create a delimited file that you can transfer to unix.

For your particular problem you can use one of two approaches.

The DTU moves data based on column name or column position. If you
want to move by column name you are going to have to put views over
the top of your SQL Server tables to present views that 'look like'
the views you want to load in Oracle. Or, you can unload the data from
SQL Server as delimited files and move it to the Oracle tables based
on column number only.

You can migrate the data in a self defining internal format the DTU
uses or in some form of delimited file. But if you use delimited files
as the target you have to write the load statement for Oracle
yourself. It is also possible to take a delimited file and convert it
to the self describing internal format and then run it as a set of
inserts, thus avoiding having to write the load statements.

If you are doing major volumes let me know as the 'alpha' version is
around 2.5 times faster than the 'production' version so you might
want the newer 'alpha' version.

A word on data types. Oracle and SQL Server data types are NOT the
same even when they have the same name....eg NUMERIC does not mean the
same in the two databases.

The DTU unloads data into C character strings and then passes them to
the target ODBC DSN specifying the data type of the target. If the
target database can convert the character string to the target data
type the insert will work. If it cannot, ODBC will give you a cast
error. This is the most common problem I have with this utility. To
solve cast errors you need to format the data coming out of the source
table into something that the target ODBC DSN can understand.

Lastly, there is a manual and a user group for this
software....'freebie' means I don't do a whole lot of support for
it...;-)

I've not seen anything else out there that is free, easy to use, with
source code supplied....so you might want to try DTU.

Best Regards

Peter Nolan