Community
Showing results for 
Search instead for 
Do you mean 
Reply

Major problem with upgrading database from Premium 2010 workgroup to premium v17 - cannot add products to opportunity

New Member
Posts: 3
Country: USA

Major problem with upgrading database from Premium 2010 workgroup to premium v17 - cannot add products to opportunity

[ Edited ]

Hi,

 

I discovered what could potentially be a flaw in the code that processes the ACT database upgrade when we upgraded our database from 2010 premium to premium v17.

 

After the upgrade we discovered couldn't add products to an opportunity.   We tried the same with the demo and it worked fine which led us to a database schema difference as being the potential problem.

 

So I dug a little deeper to compare the database schemas of a new (clean) empty v17 database to our upgraded database via the following sql script:

 

-- get a list of all tables from DIAMOND database and clean copy of ACTV17 table schema

use diamond

go

 

select tabname = x.name,colname = y.name,coltype = z.name,z.length,z.xprec,z.xscale

into #diamond

from sysobjects x

inner join syscolumns y on x.id = y.id

inner join systypes z on y.xtype = z.xtype

where x.XTYPE = 'U'

go

 

use cleandbase

go

 

select tabname = x.name,colname = y.name,coltype = z.name,z.length,z.xprec,z.xscale

into #cleandbase

from sysobjects x

inner join syscolumns y on x.id = y.id

inner join systypes z on y.xtype = z.xtype

where x.XTYPE = 'U'

go

 

 

--- compare both to see where DIAMOND may be off

--- excluding tables and columns that are third party add-in or custom to Diamond

 

select *

from #diamond

where tabname not in(

                                                                select tabname

                                                                from #diamond

                                                                except

                                                                select tabname

                                                                from #cleandbase)

and (coltype <> 'sysname' and length <> 256) and colname not like 'CUST_%'                                                  

except

select * from #cleandbase

where (coltype <> 'sysname' and length <> 256)                                  

order by colname

 

Only the following showed up as differences because of underlying datatype which causes adding a product to fail:

 

DIAMOND (upgraded database)

 

tabname colname coltype length xprec xscale
TBL_PRODUCTSERVICE QUANTITY int 4 10 0
TBL_PRODUCTSERVICE UNITDISCOUNT int 4 10 0

 

CLEANDB (new v17 db schema)

 

tabname colname coltype length xprec xscale
TBL_PRODUCTSERVICE QUANTITY decimal 17 38 38
TBL_PRODUCTSERVICE UNITDISCOUNT decimal 17 38 38

 

SQL logs confirmed the error.

 

Wondering if anyone else has noticed this in the upgrade process and is coming across the same issue.  If so, it may be a problem with the underlying upgrade code in ACTv17.

 

Thanks,

 

-- Jim