[VBAPROGRAMMER]

Hi :
I am not sure if this question falls under SQL Server or Oracle, please forgive me if I posted in the wrong section.I need some advise on the proper and efficient approach for migrating Oracle 7.3/ Solaris 5.5 (Unix based ) to SQL Server 2000/WIndows Server 2k and 2k3.
The database has 72 user tables and almost 1 million records.

The approach I was thinking is to export db schema in txt file and then work from there. Creating manual tables and importing data eitehr by import export wizard in Enterprise manager or export the data from Oracle to .dmp file and work with .dmp file .

I have two questions:

1- Is this appraoch correct or suggest the most efficient approach please
2-Any way to connect SQL Server straight to UNIX box and import all the data?
3-When I export .dmp file , it shows funny chars when I open in Windows environment ...

Any advise will be much much appreciated on the Proper and best approach to take here.
Regards,
VBAPROGRAMMER

[MCrowley]

well, first, I would encourage using SQL 2005, since SQL 2000 is about at the end of its support lifecycle. I would start as you have by getting a script of all the tables. You will need to edit this script to adjust for datatype differences (Oracle's DATE is similar to MSSQL's DATETIME, VARCHAR2 is similar to NVARCHAR et al.).

Once you have the schema squared away, the data will be easy. Just use MSSQL's import/export wizards, and everything should go swimmingly. Just remember to NOT allow this wizard to create the tables for you. For some of the larger tables, you may need to set a batch commit size (10,000 rows or so), so the larger tables will not overflow the transaction logs. Apply indexes as needed.

once that is done, then you can worry about triggers,procedures, and functions. THAT is where you will be spending the bulk of your time. As far as I know, there is no easy way to do that, and it will probably end up as a complete rewrite.

[VBAPROGRAMMER]

Thank you so much for the advise. This will really help me in this project.