Delete log and rebuild index of AsyncOperationBase and WorkflowLogBase table in Dynamics CRM using SQL Job


USE [msdb]
GO
/****** Object:  Job [Async-CleanUP]    Script Date: 05/27/2010 12:36:09 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object:  JobCategory [[Uncategorized (Local)]]]    Script Date: 05/27/2010 12:36:09 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]’ AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N’JOB’, @type=N’LOCAL’, @name=N'[Uncategorized (Local)]’
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N’Async-CleanUP’,
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N’No description available.’,
@category_name=N'[Uncategorized (Local)]’,
@owner_login_name=N’domain\username’, @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [Async-CleanUp-TSQL]    Script Date: 05/27/2010 12:36:09 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N’Async-CleanUp-TSQL’,
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N’TSQL’,
@command=N’IF EXISTS (SELECT name from sys.indexes
WHERE name = N”CRM_AsyncOperation_CleanupCompleted”)
DROP Index AsyncOperationBase.CRM_AsyncOperation_CleanupCompleted
GO
CREATE NONCLUSTERED INDEX CRM_AsyncOperation_CleanupCompleted
ON [dbo].[AsyncOperationBase] ([StatusCode],[StateCode],[OperationType])
GO
declare @DeleteRowCount int
Select @DeleteRowCount = 2000
declare @DeletedAsyncRowsTable table (AsyncOperationId uniqueidentifier not null primary key)
declare @continue int, @rowCount int
select @continue = 1
while (@continue = 1)
begin
begin tran
insert into @DeletedAsyncRowsTable(AsyncOperationId)
Select top (@DeleteRowCount) AsyncOperationId
from AsyncOperationBase
where OperationType in (1, 9, 12, 25, 27, 10) AND StateCode = 3 AND StatusCode in (30, 32)

Select @rowCount = 0
Select @rowCount = count(*) from @DeletedAsyncRowsTable
select @continue = case when @rowCount <= 0 then 0 else 1 end

if (@continue = 1)
begin
delete WorkflowLogBase from WorkflowLogBase W, @DeletedAsyncRowsTable d
where W.AsyncOperationId = d.AsyncOperationId

delete BulkDeleteFailureBase From BulkDeleteFailureBase B, @DeletedAsyncRowsTable d
where B.AsyncOperationId = d.AsyncOperationId

delete AsyncOperationBase From AsyncOperationBase A, @DeletedAsyncRowsTable d
where A.AsyncOperationId = d.AsyncOperationId

delete @DeletedAsyncRowsTable
end

commit
end
–Drop the Index on AsyncOperationBase
DROP INDEX AsyncOperationBase.CRM_AsyncOperation_CleanupCompleted

–rebuild index
— Rebuild Indexes & Update Statistics on AsyncOperationBase Table
ALTER INDEX ALL ON AsyncOperationBase REBUILD WITH (FILLFACTOR = 80, ONLINE = OFF,SORT_IN_TEMPDB = ON, STATISTICS_NORECOMPUTE = OFF)
GO
— Rebuild Indexes & Update Statistics on WorkflowLogBase Table
ALTER INDEX ALL ON WorkflowLogBase REBUILD WITH (FILLFACTOR = 80, ONLINE = OFF,SORT_IN_TEMPDB = ON, STATISTICS_NORECOMPUTE = OFF)
GO
‘,
@database_name=N'<Organization Name>_MSCRM’,
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N’Async-Cleanup-Schedule’,
@enabled=1,
@freq_type=8,
@freq_interval=18,
@freq_subday_type=1,
@freq_subday_interval=0,
@freq_relative_interval=0,
@freq_recurrence_factor=1,
@active_start_date=20091116,
@active_end_date=99991231,
@active_start_time=0,
@active_end_time=235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)’
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

Update database statistics everyday


USE [msdb]
GO
/****** Object:  Job [Data_Update_statistics_everyday]    Script Date: 05/27/2010 12:32:03 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object:  JobCategory [Database Maintenance]    Script Date: 05/27/2010 12:32:03 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N’Database Maintenance’ AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N’JOB’, @type=N’LOCAL’, @name=N’Database Maintenance’
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N’Data_Update_statistics_everyday’,
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N’Data_Update_statistics_everyday’,
@category_name=N’Database Maintenance’,
@owner_login_name=N’domain\username’, @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [Data_Update_statistics_everyday]    Script Date: 05/27/2010 12:32:03 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N’Data_Update_statistics_everyday’,
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N’TSQL’,
@command=N’EXEC Sp_msforeachtable ”Update Statistics ? ”
‘,
@database_name=N'<Organization Name>_MSCRM’,
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N’update_stats_12_AM’,
@enabled=1,
@freq_type=4,
@freq_interval=1,
@freq_subday_type=1,
@freq_subday_interval=0,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20100412,
@active_end_date=99991231,
@active_start_time=40000,
@active_end_time=235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)’
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

Rebuild Index of a database table


DECLARE @Database VARCHAR(255)
DECLARE @Table VARCHAR(255)
DECLARE @cmd NVARCHAR(500)

DECLARE @fillfactor INT
SET @fillfactor = 90

CREATE Table #databaseName (dbid int identity(1,1), DbName Varchar(255))

Insert into #databaseName
SELECT name
FROM   MASTER.dbo.sysdatabases
—  WHERE  name NOT IN ( ‘master’, ‘model’, ‘msdb’, ‘tempdb’, ‘distrbution’ )
WHERE  name IN ( ‘OD’)
ORDER  BY 1

CREATE Table #TableName (Tableid int identity(1,1), TableName Varchar(1000))

declare @DbCnt int
declare @TblCnt int
declare @i int
declare @j int

select @i = 1
select @j = 1

select @DbCnt = Max(dbid) from #databaseName

WHILE @i <= @Dbcnt
BEGIN
SELECT @cmd = ‘Insert Into #TableName SELECT table_catalog + ”.” + table_schema + ”.” + table_name as tableName
FROM ‘ + DbName + ‘.INFORMATION_SCHEMA.TABLES WHERE table_type = ”BASE TABLE”’
from #databaseName where dbid = @i

— create table cursor
Print @cmd
EXEC (@cmd)

select @tblCnt = Max(Tableid) from #tableName

WHILE @j <= @tblcnt
BEGIN
— SQL 2000 command
–DBCC DBREINDEX(@Table,’ ‘,@fillfactor)
— SQL 2005 command
SELECT @cmd = ‘ALTER INDEX ALL ON ‘ + TableName + ‘ REBUILD WITH (FILLFACTOR = ‘ + Convert(VARCHAR(3), @fillfactor) + ‘)’
From #tableName where Tableid = @j

print @cmd
EXEC (@cmd)

Set @j =@j + 1
END

Set @i =@i + 1

END

drop table #databaseName
drop table #tableName

Populate lookup value on selection of another lookup


I had client requirement that on the selection of City (lookup), Region and Sub Region should be populated automatically which is read only field. To achieve this we mapped the city with region (lookup) and sub region (lookup) on the city entity. After writing below code I was getting following error.

There was an error with this field’s customized event.

Field:new_cityid

Event:onchange

Error:’ new_subregionid.value ‘ is null or not an object

I solved the issue with help of this link and answer posted by Adi Katz .

“The keyValues are only available when you use the lookup dialog. The form assistant does not contain the columns that are retrieved by the lookup and this is why the items array is empty.”

For details please visit below link.

http://social.microsoft.com/Forums/en-US/crmdevelopment/thread/ad9b5b22-da6b-4f20-9fd4-b7e46d9a7556

After this solution I added Sub Region field to lookup dialog (lookup view) and this solve my issue.

Put below code on OnChange of City Lookup field.

/*****Auto Populate SubRegion on Selection of City field******************/

if (crmForm.all.new_cityid.DataValue==null)

{
crmForm.all.new_subregionid.DataValue = null;
}

else

{

var lookup_guid;

var lookup_name;

var lookup_type;

var lookup_typename;

var lookupItem = new Array;

lookupItem = crmForm.all.new_cityid.DataValue;

lookup_guid=lookupItem[0].id;

lookup_name=lookupItem[0].name;

lookup_type=lookupItem[0].type;

lookup_typename=lookupItem[0].typename;

var subRegionName;

if(crmForm.all.new_cityid.items != null)

{
var lookupValues = crmForm.all.new_cityid.items[0].keyValues;
subRegionName = lookupValues.new_subregionid.value? lookupValues.new_subregionid.value : null;

}
else
{
subRegionName = null;

}

var subregionidvalue = GetAttributeValueFromID(lookup_typename, lookup_guid, ‘new_subregionid’);

if(subregionidvalue != null)

{
var subregionlookupData = new Array();

//Create an Object add to the array.

var subregionlookupItem = new Object();

//Set the id, typename, and name properties to the object.

subregionlookupItem.id = subregionidvalue;

regionlookupItem.typename = ‘new_subregion’;

regionlookupItem.name = subRegionName ;

// Add the object to the array.

subregionlookupData[0] = subregionlookupItem;

// Set the value of the lookup field to the value of the array.

crmForm.all.new_subregionid.DataValue = subregionlookupData;

crmForm.all.new_subregionid.ForceSubmit = true;

}

function GetAttributeValueFromID(sEntityName, sGUID, sAttributeName)

{

var sXml = “”;

var oXmlHttp = new ActiveXObject(“Msxml2.XMLHTTP.6.0”);

var serverurl = “”;

//set up the SOAP message

sXml += “<?xml version=\”1.0\” encoding=\”utf-8\” ?>”;

sXml += “<soap:Envelope xmlns:soap=\”http://schemas.xmlsoap.org/soap/envelope/\””;

sXml += ” xmlns:xsi=\”http://www.w3.org/2001/XMLSchema-instance\””;

sXml += ” xmlns:xsd=\”http://www.w3.org/2001/XMLSchema\”>”;

sXml += “<soap:Body>”;

sXml += “<entityName xmlns=\”http://schemas.microsoft.com/crm/2006/WebServices\”>” + sEntityName +

“</entityName>”;

sXml += “<id xmlns=\”http://schemas.microsoft.com/crm/2006/WebServices\”>” + sGUID + “</id>”;

sXml += “<columnSet xmlns=\”http://schemas.microsoft.com/crm/2006/WebServices\””;

sXml += ” xmlns:q=\”http://schemas.microsoft.com/crm/2006/Query\””;

sXml += ” xsi:type=\”q:ColumnSet\”><q:Attributes><q:Attribute>” + sAttributeName +

“</q:Attribute></q:Attributes></columnSet>”;

sXml += “</soap:Body>”;

sXml += “</soap:Envelope>”;

// send the message to the CRM Web service

oXmlHttp.open(“POST”, serverurl + “/MsCrmServices/2006/CrmService.asmx”,false);

oXmlHttp.setRequestHeader(“SOAPAction”,”http://schemas.microsoft.com/crm/2006/WebServices/Retrieve&#8221;);

oXmlHttp.setRequestHeader(“Content-Type”, “text/xml;charset=utf-8”);

oXmlHttp.setRequestHeader(“Content-Length”, sXml.length);

oXmlHttp.send(sXml);

// retrieve response and find attribute value

// retrieve the given attribute name in any XML namespace

var result = oXmlHttp.responseXML.selectSingleNode(“//*[local-name()=\”” +  sAttributeName +”\”]”);

if (result == null)

{

return “”;

}

else

{

return result.text;

}

}

}