How to clear Dynamics GP companies which are not available in SQL server?

Use the below SQL query to clear all the Dynamics GP companies which are no longer available in SQL server and showing on Dynamics GP company drop down.

Run the below query against master database and replace “DYNAMCS” from this script with your system database name if you are using system database name other than DYNAMICS.

set nocount on
use master

declare @dbNames char(5), @GPSystem char(15), @Statement char(3400)
create table ##GPSystem (GPSystem char(15))

declare FindGPSystem cursor for
select name from sysdatabases where len(name) <= 5 and name not in (‘model’,’msdb’) order by name
open FindGPSystem
fetch next from FindGPSystem into @dbNames
while (@@fetch_status <> -1) begin
set @Statement = ‘if exists (select 1 from ‘ + rtrim(@dbNames) + ‘..sysobjects
where type = ”U” and name = ”SY00100”)
insert into ##GPSystem (GPSystem)
select DBNAME from ‘ + rtrim(@dbNames) + ‘..SY00100’
exec (@Statement)
fetch next from FindGPSystem into @dbNames
end
close FindGPSystem
deallocate FindGPSystem

if not exists (select 1 from ##GPSystem)
insert into ##GPSystem (GPSystem) select ‘DYNAMICS’ — Use your Sytem database Name instead of DYNAMICS if you are using other than DYNAMICS
delete from ##GPSystem where GPSystem not in (select name from sysdatabases)

declare CleanGPSystem cursor for
select GPSystem from ##GPSystem order by GPSystem
open CleanGPSystem
fetch next from CleanGPSystem into @GPSystem
while (@@fetch_status <> -1) begin
set @Statement = ‘use ‘ + rtrim(@GPSystem) + ‘
declare @statement char(150)
delete SY01500 where INTERID not in (select name from master..sysdatabases)

declare CMPANYID_Cleanup CURSOR for
select ”delete ” + o.name + ” where CMPANYID not in (0,-32767)
and CMPANYID not in (select CMPANYID from SY01500)”
from sysobjects o join syscolumns c on o.id = c.id and o.type = ”U”
where c.name = ”CMPANYID” and o.name <> ”SY01500”
order by o.name
open CMPANYID_Cleanup
fetch next from CMPANYID_Cleanup into @statement
while (@@fetch_status <> -1) begin
exec (@statement)
fetch next from CMPANYID_Cleanup into @statement
end
close CMPANYID_Cleanup
deallocate CMPANYID_Cleanup

declare companyID_Cleanup1 CURSOR for
select ”delete ” + rtrim(o.name) + ” where companyID not in (0,-32767)
and companyID not in (select CMPANYID from SY01500)”
from sysobjects o join syscolumns c on o.id = c.id and o.type = ”U”
where c.name = ”companyID” and o.name <> ”SY01500” and o.name <> ”syDeployedReports”
order by o.name
open companyID_Cleanup1
fetch next from companyID_Cleanup1 into @statement
while (@@fetch_status <> -1) begin
exec (@statement)
fetch next from companyID_Cleanup1 into @statement
end
close companyID_Cleanup1
deallocate companyID_Cleanup1

declare companyID_Cleanup2 CURSOR for
select ”delete ” + rtrim(o.name) + ” where companyID <> ””’ + rtrim(@GPSystem) + ””’
and companyID <>”””” and companyID not in (select INTERID from SY01500)”
from sysobjects o join syscolumns c on o.id = c.id and o.type = ”U”
where c.name = ”companyID” and o.name <> ”SY01500” and o.name = ”syDeployedReports”
order by o.name
open companyID_Cleanup2
fetch next from companyID_Cleanup2 into @statement
while (@@fetch_status <> -1) begin
exec (@statement)
fetch next from companyID_Cleanup2 into @statement
end
close companyID_Cleanup2
deallocate companyID_Cleanup2

declare db_name_Cleanup CURSOR for
select ”delete ” + rtrim(o.name) + ” where db_name <> ””’ + rtrim(@GPSystem) + ””’
and db_name <> ”””” and db_name not in (select INTERID from SY01500)”
from sysobjects o join syscolumns c on o.id = c.id and o.type = ”U”
where c.name = ”db_name”
order by o.name
open db_name_Cleanup
fetch next from db_name_Cleanup into @statement
while (@@fetch_status <> -1) begin
exec (@statement)
fetch next from db_name_Cleanup into @statement
end
close db_name_Cleanup
deallocate db_name_Cleanup

declare dbname_Cleanup CURSOR for
select ”delete ” + rtrim(o.name) + ” where DBNAME <> ””’ + rtrim(@GPSystem) + ””’
and DBNAME <> ”””” and DBNAME not in (select INTERID from SY01500)”
from sysobjects o join syscolumns c on o.id = c.id and o.type = ”U”
where c.name = ”DBNAME” and o.name not in (”SLB10100”,”ERB10100”,”NLB10100”)
order by o.name
open dbname_Cleanup
fetch next from dbname_Cleanup into @statement
while (@@fetch_status <> -1) begin
exec (@statement)
fetch next from dbname_Cleanup into @statement
end
close dbname_Cleanup
deallocate dbname_Cleanup

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)’
exec (@Statement)
fetch next from CleanGPSystem into @GPSystem
end
close CleanGPSystem
deallocate CleanGPSystem

drop table ##GPSystem
set nocount off
go

Hope this helps!!!

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s