How to remove a company that is no longer being used in Microsoft Dynamics GP

This article explains how to remove a company that is no longer being used in Microsoft Dynamics GP. This is taken from Microsoft kb article http://support.microsoft.com/kb/849249

To remove a company that is no longer being used in Microsoft Dynamics GP, follow these steps:

  1. Follow the appropriate step:
  • If you are using Microsoft Dynamics GP 10.0 or later, click Tools on the Microsoft Dynamics GP menu, point to Utilities, point to System, and then click Delete Company.
  • If you are using Microsoft Dynamics GP 9.0, point to Utilities on the Tools menu, point to System, and then click Delete Company.
  • If you are using Microsoft Business Solutions – Great Plains 8.0, click Setup on the Tools menu, point to System, and then click Company.
  • If you are using a version that is earlier than Microsoft Business Solutions – Great Plains 8.0, click System on the Setup menu, and then click Company.
  1. Click the Company Name lookup.
  2. Click the company that you want to delete, click Select, and then click Delete.
  3. Verify that the company database was removed. To do this, follow the steps for the database that you are using with Microsoft Dynamics GP.

    Microsoft SQL Server 2005 / Microsoft SQL Server 2008 /Microsoft SQL Server 2008 R2 / Microsoft SQL Server 2012

    1. Start SQL Server Management Studio.
    2. Expand the instance of SQL Server for Microsoft Dynamics GP, and then expand Databases.
    3. Verify that the company database was removed. If the company database is listed, right-click the company database, and then click Delete.

Microsoft SQL Server 2000

  1. Start Enterprise Manager.
    1. Expand the instance of SQL Server for Microsoft Dynamics GP, and then expand Databases.
    2. Verify that the company database was removed. If the company database is listed, right-click the company database, and then click Delete.

Microsoft SQL Server Desktop Engine (MSDE)

  1. Start Support Administrator Console.
    1. Run the following command to verify that the company database still exists.

SELECT name FROM MASTER..SYSDATABASES

  1. If the company database is listed, run the following command to delete the database.

DROP DATABASE XXX

Note Replace XXX with the database name of the company that you want to delete.

  1. If the database has been manually removed or removed by the DROP script mentioned in step 4-c, then there may be some references from the deleted database still within the DYNAMICS/system database. These references need to be removed. To do this, find below the ClearCompanies.sql script attached along with this.

    Microsoft SQL Server 2005 / Microsoft SQL Server 2008 / Microsoft SQL Server 2008 R2 / Microsoft SQL Server 2012

Start SQL Server Management Studio.

  1. Click New Query from the menu bar to start a new query window.
  2. Paste the contents of the ClearCompanies.sql scripts into the empty query window.
  3. Click Execute or press F5 to run the script.

    Note ClearCompanies.sql is a script that is automatically setup to run against the appropriate databases.

Microsoft SQL Server 2000

  1. Start Query Analyzer.
    1. Click Query and then click New Query to open a blank query window.
    2. Paste the contents of ClearCompanies.sql into this empty query window.
    3. Click on the ‘Execute’ button or press F5 to run the script.

      Note ClearCompanies.sql is a script that is automatically setup to run against the appropriate databases.

  2. Log into Microsoft Dynamics GP to verify the company no longer exists.

CLEARCOMPANIES.SQL

/* 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