Operation failed due to a SQL integrity violation


Today i was suppose to write a callout which would be sending email to owner’s manager for our opportunity entity.

I thought before trying out with an callout(which is really hard to debug) i should try it first in a windows application. Finally after some time i was able to get it working.

So than i decided to put the same code in the callout as well. But to my surprise i found that the same code was not working in the callout.

This was the error i was getting as

ex.detail.innerxml // for (SoapException ex)

<description>Operation failed due to a SQL integrity violation.</description>

After searching on the internet for the same i was able to find the cause for the error in my code

For my windows application this piece of code was sufficient

CrmService service=new CrmService();

But when it came to callout it had to be

CrmService service=new CrmService();
service.CallerIdValue = new CallerId();
service.CallerIdValue.CallerGuid =userContext.UserId;

This helped me to solve the error.

Check this link as well



Using Dhtml inside CRM


We can make use of Dhtml inside our CRM onLoad, onSave and onChange javascripts.

Using Dhtml, number of possible ways of customizing CRM increases.

But the only thing with them is that they are unsupported.

Just for fun try putting this code in form load of your contact form and see what it does.

window.status=”Hello CrmUser!”;
function G_GlowField()
var p=window.createPopup()
var pbody=p.document.body
pbody.style.border=”solid black 1px”
pbody.innerHTML=”Mandatory Field”

function G_RevertField()

Or Better check this link

Using the attachEvent method to show users context sensitive help


Link Entity, Query Expression and FetchXml Wizard

Let say we need to make use of CrmService to give us the name of the opportunity where owner’s fullname=’someName’;

Name is an attribute of Opportunity Entity.
FullName is an attribute of SystemUser Entity.

So we need to make use of LinkEntity class over here to link from opportunity entity to systemuser entity.

Now let’s use queryExpression for the same.


But plzzzz don’t write it yourself, use FetchXmlBuilder to write it for you.
Plzz understand it and download it from this link

(The best site for all the CrmDevelopers)

This is how the queryExpression code will look like


QueryExpression query = new QueryExpression();

query.EntityName = “opportunity”;

ColumnSet columns = new ColumnSet();
columns.Attributes = new string[] { “name” };
query.ColumnSet = columns;

LinkEntity linkEntity1 = new LinkEntity();
linkEntity1.JoinOperator = JoinOperator.Natural;
linkEntity1.LinkFromEntityName = “opportunity”;
linkEntity1.LinkFromAttributeName = “owninguser”;
linkEntity1.LinkToEntityName = “systemuser”;
linkEntity1.LinkToAttributeName = “systemuserid”;

linkEntity1.LinkCriteria = new FilterExpression();
linkEntity1.LinkCriteria.FilterOperator = LogicalOperator.And;

ConditionExpression condition1 = new ConditionExpression();
condition1.AttributeName = “fullname”;
condition1.Operator = ConditionOperator.Equal;
condition1.Values = new object[] { “Nishant Rana” };

linkEntity1.LinkCriteria.Conditions = new ConditionExpression[] { condition1 };

query.LinkEntities = new LinkEntity[] { linkEntity1 };

Now say we have a requirement where we need

The name of all the opportunity along with the fullname of the owner.

Let’s try doing it with a queryExpression class and using the FetchXmlBuilder.

This is the code which we’ll get


LinkEntity linkEntity1 = new LinkEntity();

linkEntity1.JoinOperator = JoinOperator.Natural;

linkEntity1.LinkFromEntityName = “opportunity”;

linkEntity1.LinkFromAttributeName = “owninguser”;

linkEntity1.LinkToEntityName = “systemuser”;

linkEntity1.LinkToAttributeName = “systemuserid”;

//You have specified columns for this link-entity. This is not supported in query expressions


This means it’s not possible using queryExpression

So in this case we need to make use of FetchXml to fetch use the fullname of the owning user.

The fetch xml looks like this

string fetchXml = @”
<fetch mapping=””logical”” count=””50″”>
<entity name=””opportunity””>
<attribute name=””name”” />
<link-entity name=””systemuser”” from=””systemuserid”” to=””owninguser””>
<attribute name=””fullname”” />

// Finally the code

CrmService service = new CrmService();
service.Credentials = System.Net.CredentialCache.DefaultCredentials;
string fetchXml = @”
<fetch mapping=””logical”” count=””50″”>
<entity name=””opportunity””>
<attribute name=””name”” />
<link-entity name=””systemuser”” from=””systemuserid”” to=””owninguser””>
<attribute name=””fullname”” />
String result = service.Fetch(fetchXml);


Now the only problem is we need to parse the xml on our own.

We can use something like this

if this is the result we are receiving

<resultset morerecords=”0″><result><new_id>GP0677</new_id></result></resultset>

to fetch the new_id we can do this

XmlDocument doc = new XmlDocument();
XmlNodeList xnodlist=doc.GetElementsByTagName(“
XmlNode xnodRoot=xnodlist.Item(0);
string val=xnodRoot.InnerText;





Using ALLColumns() in Dynamics CRM


To retireve the values for all the attributes of an entity we can make use of ALLColumns ,

CrmService service=new CrmService();

service.Credentials = System.Net.CredentialCache.DefaultCredentials;

QueryExpression query = new QueryExpression();

query.EntityName = EntityName.new_entityname.ToString();

query.ColumnSet=new AllColumns();

BusinessEntityCollection entities= service.RetrieveMultiple(query);

for(int i=0;i<entities.BusinessEntities.Length;i++)


new_entityname nEntity=(new_entityname) entities.BusinessEntities[i];

Response.Write(“<b>”+nEntity.new_id+” -></b>”);

Response.Write(“<i>” + nEntity.createdby.name + ” –></i>”);



Or by using QueryByAttribute  

It can be used when our query is very simple i.e. multiple equals conditions that are combined using AND.

But remember it doesn’t support OR
QueryByAttribute myQuery=new QueryByAttribute();
myQuery.ColumnSet=new AllColumns();

    // Find all the opportunities with topic name test1 and city as Redmond
myQuery.Attributes=new string[]{“name”,”address1_city”};
myQuery.Values=new string[]{“Test1″,”Redmond”};
BusinessEntityCollection retrieved=service.RetrieveMultiple(myQuery);


Query for finding Team and it’s member in MS DYNAMICS CRM


We can use this query to retrieve different teams and the users belonging to that team within Microsoft Dyanmics CRM

Select FT.name TeamName,FSU.Fullname UserName, FSU.Systemuserid from
dbo.FilteredSystemUser FSU
inner Join
dbo.FilteredTeamMembership FTM  
on FSU.Systemuserid = FTM.Systemuserid
Inner join  dbo.FilteredTeam FT
on FT.TeamID = FTM.TeamID


Hiding or disabling elements in Microsoft Dynamics CRM 3.0

Aim : To disable the close opportunity menu item from the Actions Menu for Opportunity form.

Put the following code in the Form_Load script of the Opportunity form

document.getElementById(‘_MIcomplete’).Style=”display:none” ;

Put the id of the menu item there
To get the id name for any particular menu item

  • step 1- open the opportunity form in crm.

  • step 2- press ctrl+N to open the same form in a new window.

  • step 3- right click go to view source and find the id of the required element.

Same thing we can do for buttons added through isv.config as well as other elements

ISV_New_1_199_Billing.style.display = “none” ;


Calling Asp.NET web service from form onLoad in CRM

This is the code we can make use for calling ASP.NET webservice in form onLoad javascript event handler of our Entity in Microsoft CRM.

This example calls the simple Hello World service and assigns the value returned (i.e. “Hello World”) to a field in the entity form

xmlHttp=new ActiveXObject(“Microsoft.XMLHTTP”);

xmlHttp.open(‘post’, ‘http://localhost/WebService1/Service1.asmx/HelloWorld’, false);


var xmlDoc=xmlHttp.responseXML;

var responseElement=xmlDoc.getElementsByTagName(“string”)[0];

var exch=responseElement.firstChild.nodeValue;

crmForm.all.address1_name.DataValue=exch ;

return false;

Check this link for more information



Refresh CRM form from third-party applications


Code to put in the javascript of our custom page

function window.onunload()

But the thing with the code is that it only works when we deploy the page in the same server where our Crm is deployed.

When i had deployed the page in my machine, the page wasn’t refreshing but when i deployed the same page where the crm is deployed everything was working perfectly fine.

I think in the previous case because of the cross-domain issue i guess there were problems.

One more thing,  to assign values to attributes in our Crm Form from our isv page we can make use of familiar syntax

window.opener.document.crmForm.all.new_totalbilling.DataValue=parseInt(//value );

parseInt – if the crm attribute is of numeric type



Finding shared records in CRM

To find all the records(say lead)  shared with any particular user we can make use of following query

It will return us all records(lead) shared with the user directly( i.e. through Action–>Sharing and user)

select  fl.subject

PrincipalObjectAccess poa , FilteredLead fl, FilteredSystemUser fsu
poa.ObjectTypeCode = 4
and poa.ObjectId = fl.leadid
and poa.PrincipalId = fsu.systemuserid
and fsu.domainname=SYSTEM_USER

// fsu.fullname=’name of user’

And to get the records shared with the user indirectly (i.e. through Action–>Sharing and Team (user belongs to that team)

select  fl.subject
FilteredLead fl,  PrincipalObjectAccess poa,  FilteredTeam ft
poa.ObjectTypeCode = 4 

and poa.ObjectId =fl.leadid
and poa.PrincipalId = ft.teamid and 

fl.owneridname not in (select fullname from filteredsystemuser
where domainname=SYSTEM_USER)
ft.teamid  in (select ft.teamid from filteredteammembership fm
, filteredsystemuser fsu, filteredteam ft
where fm.systemuserid=fsu.systemuserid and
and fsu.domainname=SYSTEM_USER


Making value in one lookup depended on value of another lookup.

Making value in one lookup depended on value of another lookup in Microsoft CRM. Say we have created 2 custom entities Categories and Skill. Skill entity has category lookup field which relates it to that category.(Added a relationship between them)Than created few records for both the entities. After that we have added that entities to the third entity where they’ll appear as LookUp.

Now we want the value of Skill lookup to be dependent value selected on Resource(Category) LookUp.


Resource can have following values


Now if Datawarehousing is selected than

we want to show Analysis and SSAS in the Skill lookup


To accomplish this we will write the following code in the OnChange event of Resource Lookup

// Making the skill id lookup null whenver a new values is selected for reesource


crmForm.all.new_skillid.disabled = false;

// getting the value of selected resource

var ar=new Array();


var rID = ar[0].id;

// Changing the display of skillid lookup so that only skill belonging to particular rID should appear


// in the additionalparams for the skill id attribute we will specify the fetch xml query // e.g. select * from new_categoryskill (name of our custom skill entity) where new_categoryofskillid = rid

crmForm.all.new_skillid.additionalparams = “fetchXml=<fetch mapping=’logical’ ><entity name=’new_categoryskill’><all-attributes/><filter type=’and’><condition attribute=’new_categoryofskillid’ operator=’eq’ value='” + rID + “‘/></filter></entity></fetch>”;

To get the fetchXml query what we can do over here is


We will go to our skill entity form and run an advancedfind query against it (giving the appropriate condition for which we want the fetch xml)

Now pressing CTRL+N open it in a new window Now in URl replace everything with the following line


Press Ok to the dialog that appears and it opens a prompt window from where we can copy our fetch xml and modify it accordingly.