SSMA Oracle to SQL Server Error, "Decimal's scale value must be between 0 and 28, inclusive"

Question

Hi All,

I am currently working on an Oracle 10g to SQL Server 2008 R2 migration, using v5.2 of the SQL Server Migration Assistant tool. Most of my tables convert correctly, except for one table which always generates this error when I perform a "Migrate Data" operation on it:

   Decimal's scale value must be between 0 and 28, inclusive.
   Parameter name: scale

Is there anything I can do to modify this table to work correctly with SSMA? Is there a setting in SSMA that will enable more detailed logging, for example which column/row the problem is occuring on?

There is another thread about this topic: http://social.msdn.microsoft.com/Forums/en-US/sqlgetstarted/thread/f7b7636d-7475-4848-8bc0-d1c10e36707e, but this does not contain a solution for SSMA.

Answer 1

I do think we have an overflow issue here due to exceeding allowed number of digits of a decimal value ( 28 ), thereby I do recommend to use a filter within this tool SSMA to filter out these overflow value and if not possible , you can use Merge Commands of SQL Server 2008 You might have a look at my blog for this regard …. http://www.sqlserver-performance-tuning.com/apps/blog/show/12927173-data-warehousing-workshop-3-4-

But for sure , you will have to get an aid of a linked server or Openrowset or even opendatasource  to link with Oracle data source but the last 2 options need to enable Ad Hoc Distributed Queries option …

Kindly work out it and let me know your feedback  

---

Shehap (DB Consultant/DB Architect) Think More deeply of DB Stress Stabilities

Answer 2

After some further investigation, it seems that this *may* have something to do with the version of SQL Server. On another machine, I can successfully perform data migration and the versions are:

   Success: SQL Server 10.0.5500 (SQL Server 2008)

   Failure:   SQL Server 10.50.1600 (SQL Server 2008 R2)

Can someone explain what changed between these versions to account for this error?

Answer 3

I can see both SQL Server 2008 or SQL Server 2008 R2 are equivalent for this regard as shown below, thereby the weird thing here is that It appears for the 2^nd one but such error disappear for the 1^st one although both of them can permit for 38 decimal digits even …..

http://msdn.microsoft.com/en-us/library/ms187746(v=sql.105).aspx

Therefore, I do predicate SSMA has some copmatability issue with SQL Server 2008 R2, that you might check on it with their support

Kindly let me know if any furhter help needed

---

Shehap (DB Consultant/DB Architect) Think More deeply of DB Stress Stabilities

Answer 4

Thanks for the suggestions. I wonder whether it is possible to perform the migration to a SQL Server 2008 DB, then export the migrated data to SQL Server 2008 R2; perhaps this two step approach would work?

Anybody else overcome this issue?