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.