1,185
Hi
i have created new company through GP utility ,now wen i open GP utility its giving me error on Upgrade These Companies window as blank its taking much longer time and not allowing me to go ahead also .how to make that run smoothly the GP utility .
*This post is locked for comments
You may want to download the ClearCompany.SQL script. This will clear up any companies in the DYNAMICS database that physically do not exist in SQL. It is always best to use GP to delete a company. Tools->Utilities->System->Delete Company.
/* ClearCompanys.sql - Script that will clear out all entrys 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 <> ''''
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 <> ''''
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
Thanks, Omar!
I had been searching all over for this answer. There's nothing on this in the Dynamics GP Knowledgebase. This IS the answer to syed rafeeq's question. Too bad he did not mark it as answered and did not even thank you.
Hello Syed,
Delete the records related to already deleted databases from db_update table.
i have deleted from MSSQL directly ,from there i dropped the database.how can i resolve this issues brother.
Hello Syed,
How did you delete the companies ?, what i am thinking about is that those companies were deleted but still there are some records for them in Dynamics database , which leading you to stuck in that screen.
Hi Syed,
If you get to the utilities window that details certain companies that need to be upgraded, you can untick all of these companies and click next, you will then get to the window where you can create a new company.
Hi Omar
yes i have deleted some unused company
Sorry Syed for the late reply
Did you delete any created company before ?
Hi Syed, just an observation. If all you did was create a new company, why are you being asked to upgrade?
Best regards,
#1
André Arnaud de Cal...
292,516
Super User 2025 Season 1
#2
Martin Dráb
231,409
Most Valuable Professional
#3
nmaenpaa
101,156
Share
Report
All responses (
Answers (
