On-Premise multi-organization SQL job

I have a client with 20+ organizations on their CRM server and I just ran into an issue where we really needed to issue un update SQL command against all of the CRM’s currently installed on the server. In this case we needed to update the Case’s subjectid if it was not filled in and there is a kb article attached to the case. The KB Article has the subjectid we need to update into the case entity. I must also add this is CRM 4.0 On-Premise. I playing around I created the following script that reads the MSCRM_CONFIG DB for the organization databases and then dynamically executes an update to the databases

-- =============================================
-- Author:        Bill Owens    
-- Create date: 6/29/2011
-- Description:    Update all of the Case Subjects with the KB Article's Subject
-- =============================================
SET NOCOUNT ON;
DECLARE @DB as Varchar(100)
DECLARE @SQL AS nVarChar(4000)
DECLARE Org_Cursor CURSOR FOR
    SELECT     DatabaseName
    FROM         mscrm_config.dbo.Organization
    WHERE     (IsDeleted = 0)
OPEN Org_Cursor;
FETCH NEXT FROM Org_Cursor into @DB;
WHILE @@FETCH_STATUS = 0
   BEGIN
        SET @SQL = 'Select count(*) as cnt from ' + @DB +'.dbo.AccountBase'
        set @SQL = 'UPDATE IncidentBase SET SubjectId = KbArticleBase.SubjectId FROM '+@DB +'.dbo.IncidentBase INNER JOIN '+@DB +'.dbo.KbArticleBase ON IncidentBase.KbArticleId = KbArticleBase.KbArticleId WHERE('+@DB +'.dbo.IncidentBase.SubjectId IS NULL) AND (NOT ('+@DB +'.dbo.IncidentBase.KbArticleId IS NULL))'
        EXEC sp_executesql @SQL
      FETCH NEXT FROM Org_Cursor into @DB;
   END;
CLOSE Org_Cursor;
DEALLOCATE Org_Cursor;

. FYI, cross scripting needs to be turned on on the SQL server for this to work.

Steven Jennings
Steven has more than five years of Microsoft Dynamics CRM implementation expertise. As CRM Team Lead at Affiliated, his main focus is in sales, marketing, and service areas. He has a strong background designing and deploying integrated solutions, he also has over eight years of .NET development expertise. Steven is proficient in all phases of project development, from concept through realization and assists in the management of a team of analysts and developers responsible for the organization's applications development and analysis functions. He evaluates existing applications to determine technical changes, schedules projects and resources, and monitors project timelines. For more information or to contact Steven, feel free to email him here.
No comments yet.

Leave a Reply

Capcha * Time limit is exhausted. Please reload the CAPTCHA.

Follow

Follow this blog

Email address