Converting Axapta database from Oracle to MSSQL2005

Hi,

I want to move an Axapta database located on an Oracle databaseserver to a
Microsoft SQL 2005 server.

I would like to avoid writing an individual dataflow transformation for each
table and field in the database.
An example of a transformation could be: TXT == "\x0002" ? "" : TXT

Is it possible to script the creation of these dataflows ??
Maybe a link for examples/hints etc. ??


Thanks,
Kent

Microsoft has a set of tools for migrating Oracle to SQL Server 2005.
http://www.microsoft.com/downloads/d...displaylang=en

There are also some vendors with tools that can script out schemas and SQL
insert scripts, which can then be deployed on SQL Server.

"Kent" wrote:

> Hi,
>
> I want to move an Axapta database located on an Oracle databaseserver to a
> Microsoft SQL 2005 server.
>
> I would like to avoid writing an individual dataflow transformation for each
> table and field in the database.
> An example of a transformation could be: TXT == "\x0002" ? "" : TXT
>
> Is it possible to script the creation of these dataflows ??
> Maybe a link for examples/hints etc. ??
>
>
> Thanks,
> Kent
>

There is some amount of hand holding that needs to be done before the SSMA
could work on Ax database. For example, all indexes on Oracle are created
using NLS_LOWER. This is not supported on SQL Server and the tool will
complain.

Also, there are some tables such as SQLSystemVariables that contain platform
specific syntax that the AOS uses. You cannot convert tables such as this
from Oracle to SQL Server.


--
This posting is provided "AS IS" with no warranties, and confers no rights
"Eric Russell" wrote in message
news:448EAC5B-6979-40FA-855F-152FAA43ED0C-at-microsoft.com...
>
> Microsoft has a set of tools for migrating Oracle to SQL Server 2005.
> http://www.microsoft.com/downloads/d...displaylang=en
>
> There are also some vendors with tools that can script out schemas and SQL
> insert scripts, which can then be deployed on SQL Server.
>
> "Kent" wrote:
>
>> Hi,
>>
>> I want to move an Axapta database located on an Oracle databaseserver to
>> a
>> Microsoft SQL 2005 server.
>>
>> I would like to avoid writing an individual dataflow transformation for
>> each
>> table and field in the database.
>> An example of a transformation could be: TXT == "\x0002" ? "" : TXT
>>
>> Is it possible to script the creation of these dataflows ??
>> Maybe a link for examples/hints etc. ??
>>
>>
>> Thanks,
>> Kent
>>

Dear Gandhinath,

What would therefore be the best way to convert from Oracle to SQL? Or is it
too hard to do?

"Gandhinath Swaminathan [MSFT]" wrote:

> There is some amount of hand holding that needs to be done before the SSMA
> could work on Ax database. For example, all indexes on Oracle are created
> using NLS_LOWER. This is not supported on SQL Server and the tool will
> complain.
>
> Also, there are some tables such as SQLSystemVariables that contain platform
> specific syntax that the AOS uses. You cannot convert tables such as this
> from Oracle to SQL Server.
>
>
> --
> This posting is provided "AS IS" with no warranties, and confers no rights
> "Eric Russell" wrote in message
> news:448EAC5B-6979-40FA-855F-152FAA43ED0C-at-microsoft.com...
> >
> > Microsoft has a set of tools for migrating Oracle to SQL Server 2005.
> > http://www.microsoft.com/downloads/d...displaylang=en
> >
> > There are also some vendors with tools that can script out schemas and SQL
> > insert scripts, which can then be deployed on SQL Server.
> >
> > "Kent" wrote:
> >
> >> Hi,
> >>
> >> I want to move an Axapta database located on an Oracle databaseserver to
> >> a
> >> Microsoft SQL 2005 server.
> >>
> >> I would like to avoid writing an individual dataflow transformation for
> >> each
> >> table and field in the database.
> >> An example of a transformation could be: TXT == "\x0002" ? "" : TXT
> >>
> >> Is it possible to script the creation of these dataflows ??
> >> Maybe a link for examples/hints etc. ??
> >>
> >>
> >> Thanks,
> >> Kent
> >>
>
>
>

Just for your information.



I used for converting Database from Oracle to MS SQL standard export-import
technology.



That was in 3.0. I duplicated and partly rewritten standard import algorithm
not to make RecId conversion and not to make transaction ID conversion. Just
imported data with the same RecId and transaction ID into new company. After
that I run RecId recalculation, which was also modified for better
performance.



Such a way is acceptable if you transfer the whole company, not only adding
part of data to existing company.



I reached import speed at above 1000 records per second. It is about 3.6
million records per hour. Or about 1 Gb per hour if assume average record
size to be 400 bites.



But not changed RecId and transaction IDs allows to spread tables into
different definition groups and parallel export and import. For example, for
10 Axapta clients 10 Gb per hour is not so bad.



As I can see in 4.0 something like this may be used for conversion.



Several times I used this technique to transfer data for testing from the
customer. This kind of import is much faster than standard.