65
Dear all,
Can some one give me the out line and process to be followed in the conversion from 9.0 to GP 10.0
Links to documents will be great
Thanks a lot and have a great day
*This post is locked for comments
In addition to ....
there is a GP10 installer you can download that inlcudes SP2 already use this one, do not use the first GP10 cd releases.
Here's a compiled useful sql scripts during my upgrade in the past. thanks to Elsa and Ethel and others who have contributed.
=======================
SQL Version
=======================
WITH Version(ver)
AS
(
SELECT SUBSTRING
(
@@VERSION,
CHARINDEX(' - ', @@VERSION)+3,
32
)
)
SELECT Build = LEFT(ver, CHARINDEX(' ', ver))
FROM Version
=======================
sp_help sort
=======================
Latin1-General, case-insensitive, accent-sensitive, kanatype-insensitive,
width-insensitive for Unicode Data, SQL Server Sort Order 52 on Code Page 1252
for non-Unicode Data
=======================
select * from SY01500
=======================
CMPANYID CMPNYNAM INTERID
-------- ----------------------------------------------------------------- -------
10 CSA Test Company TEST
2 CSA Old (Do not use) CSA
3 CSA of WA Inc (Formerly CPSU) CPSU
4 Civil Service Holdings Pty Ltd CSH
5 Civil Service Insurance Agency CSI
6 CSA Services (WA) Pty Ltd CFS
9 CPSU 1 January 2005 CPSUW
=======================
Search all db
=======================
--SQL Script to find all tables which contain a particular Column
select distinct rtrim(objs.name)
from syscolumns cols
inner join sysobjects objs on (cols.id = objs.id)
inner join sysindexes indx on (cols.id = indx.id)
where (cols.name = 'CMPANYID') and (objs.xtype = 'U') and (indx.rowcnt <> 0)
=======================
Tables where company ID is stored
=======================
--Tables where to delete company ID
--delete from SY01500
where INTERID in ('CSI','CFS')
--delete SY02000 where CMPANYID in ('4','5')
--delete SY02100 where CMPANYID in ('4','5')
--delete SY40500 where CMPANYID in ('4','5')
--delete SY60100 where CMPANYID in ('4','5')
=======================
Restore of DB
=======================
D:\MSSQL GP10 DATA\GPSCSHDat.mdf
D:\MSSQL GP10 DATA\GPSCSHLog.ldf
=======================
Directory for backups:
=======================
D:\GP Backups\Upgraded_160508_CFS
=======================
Change db owners
=======================
use dynamics
sp_changedbowner 'DYNSA'
use cfs
sp_changedbowner 'DYNSA'
use cpsu
sp_changedbowner 'DYNSA'
use cpsuw
sp_changedbowner 'DYNSA'
use csa
sp_changedbowner 'DYNSA'
use csh
sp_changedbowner 'DYNSA'
use csi
sp_changedbowner 'DYNSA'
use test
sp_changedbowner 'DYNSA'
=======================
change system password
=======================
use dynamics
update sy02400 set password = 0x00202020202020202020202020202020
=======================
delete users
=======================
select * from sy01400 where userid not in ('sa','TEST','DYNSA')
--delete from sy01400 where userid not in ('sa','TEST','DYNSA')
select * from ACTIVITY where userid not in ('sa','TEST','DYNSA')
select * from ASIEXP81 where userid not in ('sa','TEST','DYNSA')
select * from ASIEXP85 where userid not in ('sa','TEST','DYNSA')
select * from ASIEXP86 where userid not in ('sa','TEST','DYNSA')
select * from ASIEXP98 where userid not in ('sa','TEST','DYNSA')
--delete from ASIEXP98 where userid not in ('sa','TEST','DYNSA')
select * from STN41100 where userid not in ('sa','TEST','DYNSA')
select * from STN41300 where userid not in ('sa','TEST','DYNSA')
select * from SY01400 where userid not in ('sa','TEST','DYNSA')
--delete from SY01400 where userid not in ('sa','TEST','DYNSA')
select * from SY01402 where userid not in ('sa','TEST','DYNSA')
--delete from SY01402 where userid not in ('sa','TEST','DYNSA')
select * from SY01403 where userid not in ('sa','TEST','DYNSA')
--delete from SY01403 where userid not in ('sa','TEST','DYNSA')
select * from SY01600 where userid not in ('sa','TEST','DYNSA')
--delete from SY01600 where userid not in ('sa','TEST','DYNSA')
select * from SY02000 where userid not in ('sa','TEST','DYNSA')
--delete from SY02000 where userid not in ('sa','TEST','DYNSA')
select * from SY02100 where userid not in ('sa','TEST','DYNSA')
select * from SY02700 where userid not in ('sa','TEST','DYNSA')
select * from SY07105 where userid not in ('sa','TEST','DYNSA')
select * from SY07110 where userid not in ('sa','TEST','DYNSA')
select * from SY07121 where userid not in ('sa','TEST','DYNSA')
--delete from SY07121 where userid not in ('sa','TEST','DYNSA')
select * from SY07125 where userid not in ('sa','TEST','DYNSA')
--delete from SY07125 where userid not in ('sa','TEST','DYNSA')
select * from SY07130 where userid not in ('sa','TEST','DYNSA')
--delete from SY07130 where userid not in ('sa','TEST','DYNSA')
select * from SY07200 where userid not in ('sa','TEST','DYNSA')
--delete from SY07200 where userid not in ('sa','TEST','DYNSA')
select * from SY07210 where userid not in ('sa','TEST','DYNSA')
--delete from SY07210 where userid not in ('sa','TEST','DYNSA')
select * from SY60100 where userid not in ('sa','TEST','DYNSA')
--delete from SY60100 where userid not in ('sa','TEST','DYNSA')
select * from WDC41101 where userid not in ('sa','TEST','DYNSA')
select * from WDC51100 where userid not in ('sa','TEST','DYNSA')
--delete from WDC51100 where userid not in ('sa','TEST','DYNSA')
=======================
a get/change error in table 'duCompanyVersions' cannot access SQL table
=======================
Used a new GP application for upgrading from v8 to v10
=======================
If company to be upgraded is locked
=======================
use dynamics
delete from dulck
=======================
Upgrade these companies? in GP Utilities
=======================
use dynamics
delete from db_upgrade
where db_name in
('TCPSU',
'TCSA',
'TWO')
--compare to SY01500
=======================
restart upgrade
=======================
use DYNAMICS
declare
@companyDBName as char(5),
@version as numeric(3,2),
@verMajor as int,
@verMinor as int,
@verOldMajor as int,
@verOldMinor as int,
@verBuild as int,
@companyID as smallint
set nocount on
set @companyDBName = '<Company_Database_Name>'
set @version = 8
set @verBuild = 0
if (@version = 8.0 or @version = 8)
begin
set @verMajor = 8
set @verMinor = 0
set @verBuild = 0
set @verOldMajor = 8
set @verOldMinor = 0
end
set @companyID = (select CMPANYID from SY01500 where INTERID = @companyDBName)
delete DB_Upgrade where PRODID <>0 and db_name = @companyDBName
delete DU000020 where PRODID <>0 and companyID = @companyID
update DB_Upgrade set db_verMajor = @verMajor, db_verMinor = @verMinor, db_verOldMajor = @verOldMajor, db_verOldMinor = @verOldMinor, db_verBuild=@verBuild, db_verOldBuild=@verBuild, db_status = 0
where PRODID = 0 and db_name = @companyDBName
update DU000020 set versionMajor = @verMajor, versionMinor = @verMinor, versionBuild = @verBuild where companyID = @companyID and PRODID = 0
delete DU000030 where companyID = @companyID
delete duLCK
set nocount off
=======================
Run after upgrade
=======================
SELECT b.fileOSName, a.fileNumber, a.Status, a.errornum, a.errordes, c.CMPANYID, c.INTERID
FROM DYNAMICS.dbo.DU000030 a
JOIN
DYNAMICS.dbo.DU000010 b
ON a.fileNumber = b.fileNumber
JOIN
DYNAMICS.dbo.SY01500 c
ON a.companyID = c.CMPANYID
WHERE a.Status <> 0 or a.errornum <> 0
=======================
Run before upgrade (ClearCompanies.sql)
=======================
/* ClearCompanys.sql - Script that will clear out all entries in the DYNAMICS
database referencing databases that no longer exist on the SQL Server.
Requirements:
Company database you wish to have cleaned out of the tables in the DYNAMICS
database must be removed from the SQL server before running this script.
Ensure that all your databases have been restored or they will be erased
from the DYNAMICS database.
*/
set nocount on
/* Remove all references in the company master (SY01500) for databases that
Do not exist on the SQL Server */
delete DYNAMICS..SY01500 where INTERID not in
(select name from master..sysdatabases)
/* Clear out all tables in DYNAMICS database that have a CMPANYID field
that no longer matches any Company ID's in the SY01500 */
USE DYNAMICS
declare @CMPANYID char(150)
declare CMPANYID_Cleanup CURSOR for
select 'delete ' + o.name + ' where CMPANYID not in (0,-32767)'
+ ' and CMPANYID not in (select CMPANYID from DYNAMICS..SY01500)'
from sysobjects o, syscolumns c
where o.id = c.id
and o.type = 'U'
and c.name = 'CMPANYID'
and o.name <> 'SY01500' order by o.name
OPEN CMPANYID_Cleanup
FETCH NEXT from CMPANYID_Cleanup into @CMPANYID
while (@@FETCH_STATUS <>-1)
begin
exec (@CMPANYID)
FETCH NEXT from CMPANYID_Cleanup into @CMPANYID
end
DEALLOCATE CMPANYID_Cleanup
go
/* Clear out all tables in DYNAMICS database that have a companyID field
that no longer matches any Company ID's in the SY01500 */
USE DYNAMICS
declare @companyID char(150)
declare companyID_Cleanup CURSOR for
select 'delete ' + o.name + ' where companyID not in (0,-32767)'
+ ' and companyID not in (select CMPANYID from DYNAMICS..SY01500)'
from sysobjects o, syscolumns c
where o.id = c.id
and o.type = 'U'
and c.name = 'companyID'
and o.name <> 'SY01500'
set nocount on
OPEN companyID_Cleanup
FETCH NEXT from companyID_Cleanup into @companyID
while (@@FETCH_STATUS <>-1)
begin
exec (@companyID)
FETCH NEXT from companyID_Cleanup into @companyID
end
DEALLOCATE companyID_Cleanup
go
/* Clear out all tables in DYNAMICS database that have a db_name field
that no longer matches any company names (INTERID) in the SY01500 */
USE DYNAMICS
declare @db_name char(150)
declare db_name_Cleanup CURSOR for
select 'delete ' + o.name + ' where db_name <> ''DYNAMICS'''
+ ' and db_name not in (select INTERID from DYNAMICS..SY01500)'
from sysobjects o, syscolumns c
where o.id = c.id
and o.type = 'U'
and c.name = 'db_name'
set nocount on
OPEN db_name_Cleanup
FETCH NEXT from db_name_Cleanup into @db_name
while (@@FETCH_STATUS <>-1)
begin
exec (@db_name)
FETCH NEXT from db_name_Cleanup into @db_name
end
DEALLOCATE db_name_Cleanup
GO
set nocount on
/* Clear out all tables in DYNAMICS database that have a dbname field
that no longer matches any company names (INTERID) in the SY01500 */
USE DYNAMICS
declare @dbname char(150)
declare dbname_Cleanup CURSOR for
select 'delete ' + o.name + ' where DBNAME <> ''DYNAMICS'''
+ ' and DBNAME not in (select INTERID from DYNAMICS..SY01500)'
from sysobjects o, syscolumns c
where o.id = c.id
and o.type = 'U'
and c.name = 'DBNAME'
set nocount on
OPEN dbname_Cleanup
FETCH NEXT from dbname_Cleanup into @dbname
while (@@FETCH_STATUS <>-1)
begin
exec (@dbname)
FETCH NEXT from dbname_Cleanup into @dbname
end
DEALLOCATE dbname_Cleanup
GO
set nocount on
/* Remove all stranded references from the other Business Alerts table that
no longer exist in the SY40500 */
delete SY40502 where BARULEID NOT IN (SELECT BARULEID FROM SY40500)
delete SY40503 where BARULEID NOT IN (SELECT BARULEID FROM SY40500)
delete SY40504 where BARULEID NOT IN (SELECT BARULEID FROM SY40500)
delete SY40505 where BARULEID NOT IN (SELECT BARULEID FROM SY40500)
delete SY40506 where BARULEID NOT IN (SELECT BARULEID FROM SY40500)
GO
select PONUMBER, DEX_ROW_ID from POP10110 where PONUMBER not in (select PONUMBER from POP10100)
select PONUMBER, DEX_ROW_ID from POP30110 where PONUMBER not in (select PONUMBER from POP30100)
DBCC CHECKDB
DBCC CHECKCATALOG
select @@servername
dbcc updateusage(TWO)
sp_helpdb
sp_changedbowner 'DYNSA'
select name
from sysobjects
where name like '% %'
Hi Jimmy,
In addition to what was posted already, I urge you to _not_ use the GP security conversion tools that Microsoft provides to ease the transition from the prior versions of GP 10.0. This tools creates a real mess in the security tables and then it will take you forever to figure out who has access to what. GP 10.0 uses an entire new security model based on roles and tasks. Resources are assigned to tasks and tasks are assigned to roles. Roles are then assigned to users (it's like AD, but it's not :-) ).
Do your best to assess the current security needs before you upgrade, put it down on a paper and look with your users what their needs are in the system. Microsoft ships with GP 10.0 about 40-45 pre-defined roles and hundreds of tasks, suited for the most common actiivities in the system. Be careful, some additionnal modules or 3rd parties don't have a pre-defined roles / tasks set, and you'll need to create your own.
Take a look to the execellent series that David Musgrave has posted on his blog.
Wish you good luck with your migration from GP 9 to 10 :-)
Happy Halloween,
Béat
GP 10 Sr. System Admin
Forensic Technology
In addition to the docs provided above, having just been through it, I'd also recommend:
Do all the clean-up that you can prior to converting:
Additionally:
Here is the official documentation, but requires Partnersource access:
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
#1
André Arnaud de Cal...
291,280
Super User 2024 Season 2
#2
Martin Dráb
230,235
Most Valuable Professional
#3
nmaenpaa
101,156
Share
Report
All responses (
Answers (