MSCRM : Delete Reports from database

Today we needed to delete more 100 reports from our solution. This operation is boring and long to do directly on CRM. My mate Flavio found an alternative way to clean reports from Database. It is an unsupport solution but for our needs run in right way.

The queries are simple:

DECLARE @Id nvarchar(max)
DECLARE MY_CURSOR CURSOR
LOCAL STATIC READ_ONLY FORWARD_ONLY
FOR
SELECT DISTINCT ReportId From ReportBase WHERE name like('Sub Accounts %')

OPEN MY_CURSOR
FETCH NEXT FROM MY_CURSOR INTO @Id
WHILE @@FETCH_STATUS = 0
BEGIN
--Do something with Id here
PRINT @Id
DELETE FROM dbo.ReportCategoryBase WHERE ReportId = @Id
DELETE FROM dbo.ReportEntityBase WHERE ReportId = @Id
DELETE FROM dbo.ReportLinkBase WHERE ReportId = @Id
DELETE FROM dbo.ReportLinkBase Where LinkedReportId = @Id
DELETE FROM dbo.ReportVisibilityBase WHERE ReportId = @Id
DELETE FROM dbo.Dependencybase WHERE DependencyId
in(SELECT DependencyId FROM dbo.Dependency WHERE RequiredComponentObjectId = @Id)
DELETE FROM dbo.Dependencybase WHERE DependencyId
in(SELECT DependencyId FROM dbo.Dependency WHERE DependentComponentObjectId = @Id)
DELETE FROM dbo.SolutionComponentBase WHERE ObjectId = @Id
DELETE FROM dbo.DependencyNodeBase WHERE ObjectId = @Id
DELETE FROM dbo.ReportBase WHERE ReportId = @Id;

FETCH NEXT FROM MY_CURSOR INTO @Id
END
CLOSE MY_CURSOR
DEALLOCATE MY_CURSOR

That’s all.

Lascia un commento

Inserisci i tuoi dati qui sotto o clicca su un'icona per effettuare l'accesso:

Logo WordPress.com

Stai commentando usando il tuo account WordPress.com. Chiudi sessione / Modifica )

Foto Twitter

Stai commentando usando il tuo account Twitter. Chiudi sessione / Modifica )

Foto di Facebook

Stai commentando usando il tuo account Facebook. Chiudi sessione / Modifica )

Google+ photo

Stai commentando usando il tuo account Google+. Chiudi sessione / Modifica )

Connessione a %s...