Category Archives: SQL

CRM : Rescheduling the Update Contract States job

Update Contract States job is CRM 2011 (also for CRM 4.0) system job which runs daily and change the contract status based on contract start date and end date (i.e. change contract from Invoiced to active and from Active to expired).

One of customer’s CRM organization has this job running 10 PM at night and wanted to change this job on particular time (very early in the morning) to make sure contract status are up to date when users starts using CRM system.

There is job editor provided by MS to reschedule CRM system jobs but this does not include this specific job.

so following are steps we undertook with the help of CRM parter/MS support to reschedule this particular job in CRM system.

Step 1: run the following query to find the current Jobs in the relevant CRM organization. (note down the AsyncOperationid returned from this query as we will be using this in next step)

select AsyncOperationid,RecurrenceStartTime,postponeuntil,StatusCode, RecurrencePattern from AsyncOperation WHERE
Name = 'Update Contract States job'
and
StatusCode =10
and RecurrencePattern is not null

following quick details regarding important columns in this table.

RecurrencePattern is used to describe frequency and interval of the system jobs and please refer this article for more information on regarding this.

Postponeuntil is datetime field and indicate when this job will run next time. And it is UTC date meaning if this field contains “2012-10-23 09:08:00.000” and if your time zone is GMT + 1 then this job will run next time on “2012-10-23 10:08:00.000”

RecurrenceStartTime is also UTC datetime field which will be used to set next runtime of job, date part of this field is not important as long as it is set in the past.

Step 2: so using noted down AsyncOperationid in step 1 run following query to update recurrencestarttime, postponeuntil for this Update Contract States job .


update AsyncOperation set
RecurrenceStartTime = '2012-10-22 09:08:00.000',
postponeuntil = '2012-10-23 09:08:00.000'
WHERE AsyncOperationid = '5EB24ECB-60ED-4F59-801D-A6C19465C4D8'

So as per above update, this job will run at 9:08 AM (GMT time) in the morning every day.after job run if you verify the details then it would look as follows. So as you can see system will update postponeuntil (next run time) to next day (24th October) after this job run today (on 23th October).

****Important Notes regarding above change****
above change is unsupported so make sure you apply this change to Dev, Test environment before applying this to Live environment. Also make sure you take back up of database before doing this change.

Hope this helps..

Cheers,
MayankP:)

Advertisements

SQL Transaction: Small Tip.

While using CRM 4.0 many times we have to do unsupported changes directly to the database and there is risk involved in running the queries directly to change CRM database record or field.

I have small tip for this using SQL Transaction for this. Before running your Query run begin transaction Command then run your query and check the results.

If results are ok and as expected then run commit transaction Command.

Otherwise if result is not as expected then run rollback transaction Command and that will undo the changes.

see below image for explnation on this..

Hope this helps!!!

Cheers,
MayankP:)

The length of the ‘filteringattributes’ attribute exceeded the maximum allowed length of ‘100’

While developing one of the plug-in for CRM 4.0 we started getting following error from plug-in registration tool.

Problem/Error Description

Unhandled Exception: System.Web.Services.Protocols.SoapException: Server was unable to process request.
Detail:

A validation error occurred. The length of the ‘filteringattributes’ attribute of the ‘sdkmessageprocessingstep’ entity exceeded the maximum allowed length of ‘100’.
Platform

at System.Web.Services.Protocols.SoapHttpClientProtocol.ReadResponse(SoapClientMessage message, WebResponse response, Stream responseStream, Boolean asyncCall)
at System.Web.Services.Protocols.SoapHttpClientProtocol.Invoke(String methodName, Object[] parameters)
at PluginRegistrationTool.CrmSdk.CrmService.Create(BusinessEntity entity)
at PluginRegistrationTool.RegistrationHelper.RegisterStep(CrmOrganization org, CrmPluginStep step)
at PluginRegistrationTool.StepRegistrationForm.btnRegister_Click(Object sender, EventArgs e)

Solution Steps for this are mentioned as below..
Step 1: Run query on database to check current max length..

select Maxlength from MetadataSchema.Attribute
where name ='filteringattributes'

Step 2: Update this field to desired value

Update MetadataSchema.Attribute
SET Maxlength = 500
where name ='filteringattributes'

Step 3: Check the Length again to make sure it update from above queries.

Note: – Above customization is unsupported and you must take precautionary measure to make sure to avoid any problem with CRM system.

Hope this helps..

Regards,
MayankP:)

Plug-in type With Id = GUID Does Not Exist

We suddenly started getting following exception on one of our CRM environment.

Unhandled Exception: System.Web.Services.Protocols.SoapException:
Server was unable to process request. Detail:

0x80040217
plugintype With Id = 6ba57788-1975-df11-8b26-0050568d68ea Does Not Exist Platform

Cause:

The reason this error occurred on our environment is that we had plug in assembly registered on crm database as Database deployment and we then unregister the assembly and registered it again which caused problem because steps of the previous assembly was already there in the database (even though they are marked for deletion).

Resolution:

This is tricky problem and might need unsupported SQL delete of the records which are corrupted.

IMP. NOTE: This is unsupported and you should back up your SQL before doing any changes. If you got MS support then it is better to contact them for this tricky issue.

Basically you need to delete or check records regarding this plug in type and store all the rows you remove in excel file because you might need to refer it again to get the relevant Guid.
Firstly let me explain the list of table which stored plug in related details.

PluginTypeBase – all individual Plug In (classes) will be stored in this table.

PluginTypeBaseIds – This is GUID of the record from PluginTypeBase table (this table links individual class with other tables in CRM).

SdkMessageProcessingStepBase – This table stores all the steps related to particular class

SdkMessageProcessingStepBaseIds – This table stores GUID of the record from SdkMessageProcessingStepBase table.

SdkMessageProcessingStepImageBase – This tale stores image s details related to steps.

SdkMessageProcessingStepSecureConfigBase – This table stores any configuration related to this plug-In type.

Now let’s do select queries first to see all records are relevant to our plug–in type and then we need to just replace delete command with select to remove related records from database.

select * from PluginTypeBaseIds where PluginTypeId = GUID
(e.g. select * from PluginTypeBaseIds where PluginTypeId = 'FB270B8E-B48D-DF11-8D06-18A90547D5EE')

SELECT * FROM PluginTypeBase where PluginTypeId = GUID

SELECT * FROM [SdkMessageProcessingStepBase] where PluginTypeId = GUID

SELECT * FROM [SdkMessageProcessingStepBaseIds] where [SdkMessageProcessingStepId] in (Select
SdkMessageProcessingStepId FROM [SdkMessageProcessingStepBase]
where PluginTypeId = GUID)

SELECT * FROM [SdkMessageProcessingStepImageBase] where [SdkMessageProcessingStepId] in (Select
SdkMessageProcessingStepId FROM [SdkMessageProcessingStepBase]
(Select SdkMessageProcessingStepId FROM [SdkMessageProcessingStepBase]where PluginTypeId = GUID)

SELECT * FROM [SdkMessageProcessingStepSecureConfigBase]

Verify that above queries gives you only record related to your plug-in Type , so read the description column on each row and make sure this is related to your plug in type only, Now we need to start deleting records from bottom tables to top.

-> SdkMessageProcessingStepSecureConfigBase table first (only row related to your plug in type and if your plug in type does not have related record in this table ignore this one) and then

-> SdkMessageProcessingStepImageBase (only record related to your GUID) and then

-> SdkMessageProcessingStepBaseIds (you may need to do this after sdkMessageProcessingStepBase so store Guid
and then delete after you delete records from sdkMessageProcessingStepBase table) (only record related to your
GUID)

-> SdkMessageProcessingStepBase (only record related to your GUID)

-> PluginTypeBaseIds (only record related to your GUID)

-> PluginTypeBase (only record related to your GUID)

After all delete queries are run successfully restart CRM Service and Asynchronous Service..

Hope this helps, if need more information please put comments on this post and I will respond with more details ..

Cheers,
MayankP 🙂

Append string value in SQL using XML Path

One of my colleagues has showed me this cool trick…

So many times you need to write code where you needed to append strings values from a field in SQL Table and all the times people uses different approach…cursors, CTE, WHILE loop, doing it from the front end…etc. in SQL

Following is one very cool approach to achieve this in SQL using XML Path.

Hope this helps…