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…