asked on Dynamics CRM Plugin Query TotalRecordCount returning -1
I am working on a plugin and have written a function to pull back any incidents that have the ticket number passed into the function. A fairly simple function but for some reason I am receiving back a -1 value from the property TotalRecordCount on the EntityCollection. My understanding is that this means it is not finding any records at all but I know that there is a record with the ticket number being passed in. I must be missing something anyone have any thoughts?
internal EntityReference fetchCaseByCaseNumber(string caseNumber, IOrganizationService service, ITracingService tracingService)
{
Entity incident;
//tracingService.Trace("Creating QueryByAttribute");
//QueryByAttribute querybyattribute = new QueryByAttribute("incident");
//querybyattribute.ColumnSet = new ColumnSet("incidentid", "ticketnumber");
//querybyattribute.Attributes.AddRange("ticketnumber");
//querybyattribute.Values.AddRange(caseNumber);
//EntityCollection incidents = service.RetrieveMultiple(querybyattribute);
tracingService.Trace("Creating Query...");
QueryExpression query = new QueryExpression
{
EntityName = "incident",
//PageInfo = new PagingInfo() {count },
ColumnSet = new ColumnSet("incidentid", "ticketnumber"),
Criteria = new FilterExpression
{
Conditions =
{
new ConditionExpression
{
AttributeName = "ticketnumber",
Operator = ConditionOperator.Equal,
Values = {caseNumber}
}
}
}
};
tracingService.Trace("Running Query");
EntityCollection incidents = service.RetrieveMultiple(query);
tracingService.Trace("fetching total count");
int count = incidents.TotalRecordCount;
// incident = incidents.Entities.First();
if (count == 1)
{
incident = incidents.Entities.First();
}
else
{
tracingService.Trace(String.Format("{0} incidents found with ticketnumber",incidents.TotalRecordCount.ToString()));
throw new InvalidPluginExecutionException("Duplicate ticket number error");
}
EntityReference caseReference = new EntityReference("incident",incident.Id);
return caseReference;
}
Microsoft DynamicsC#
Last Comment
Feridun Kadir
ASKER
Feridan I was able to get the TotalRecordCount to show per your recommendation but I am still not getting any records back. TotalRecordCount = 0
I am passing in the string value 000003 and I am getting zero records returned. Even though this SQL pulls exactly one record as expected
Here is the Updated Plugin Code as well.
I am passing in the string value 000003 and I am getting zero records returned. Even though this SQL pulls exactly one record as expected
SELECT TicketNumber, *
FROM IncidentBase
WHERE TicketNumber = '000003'
Here is the Updated Plugin Code as well.
internal EntityReference fetchCaseByCaseNumber(string caseNumber, IOrganizationService service, ITracingService tracingService)
{
Entity incident;
tracingService.Trace("Creating Query...");
QueryExpression query = new QueryExpression
{
EntityName = "incident",
PageInfo = new PagingInfo() { ReturnTotalRecordCount = true },
ColumnSet = new ColumnSet("incidentid", "ticketnumber"),
Criteria = new FilterExpression
{
Conditions =
{
new ConditionExpression
{
AttributeName = "ticketnumber",
Operator = ConditionOperator.Equal,
Values = {caseNumber}
}
}
}
};
tracingService.Trace("Running Query");
EntityCollection incidents = service.RetrieveMultiple(query);
tracingService.Trace("fetching total count for incidents with ticket number: {0}", caseNumber);
if (incidents.TotalRecordCount == 1)
{
incident = incidents.Entities.First();
}
else
{
tracingService.Trace(String.Format("{0} incidents found with ticketnumber",incidents.TotalRecordCount.ToString()));
throw new InvalidPluginExecutionException("Duplicate ticket number error");
}
EntityReference caseReference = new EntityReference("incident",incident.Id);
return caseReference;
}
ASKER
Does this method work to turn on tracing within Microsoft Dynamics CRM 2016?
https://support.microsoft.
I am not seeing any of those registry keys in the registry, should I just create them?
Will this tracing give me more information on the SQL or Fetch XML that is being run against the server?
https://support.microsoft.
I am not seeing any of those registry keys in the registry, should I just create them?
Will this tracing give me more information on the SQL or Fetch XML that is being run against the server?
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
SOLUTION
Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Hi pbu-ts,
Tracing will give you SQL and not the Fetch (at least in this case). I have one more suggestion, why don't you use FetchXml to run this query?
Use http://www.xrmtoolbox.com/
And one more thing, are you sure the user (In whose context the plugin is running has permissions to retrieve this data? The biggest disadvantage w.r.t. Dynamics CRM is if you try to query data bypassing its filtered views, you will end up bypassing security restrictions applied by Security Roles.
Regards,
Chinmay.
Tracing will give you SQL and not the Fetch (at least in this case). I have one more suggestion, why don't you use FetchXml to run this query?
Use http://www.xrmtoolbox.com/
And one more thing, are you sure the user (In whose context the plugin is running has permissions to retrieve this data? The biggest disadvantage w.r.t. Dynamics CRM is if you try to query data bypassing its filtered views, you will end up bypassing security restrictions applied by Security Roles.
Regards,
Chinmay.
A couple of things, is your SQL code literally what you ran? I ask because the string you test for (000003) is not a valid case number in CRM.
Another thing to point out is that querying the base tables bypasses security, whereas your code will be running in the context of a CRM user so could it be that the user account under which the code is being tested doesn't have rights to query cases? A bit of a long shot, but I thought I'd check.
With regard to the registry values, you should add them if they are not there. Another approach might be to use the SQL Profile to record the queries hitting the CRM database. You'll need to use some filtering otherwise you'll be swamped with data.
Another thing to point out is that querying the base tables bypasses security, whereas your code will be running in the context of a CRM user so could it be that the user account under which the code is being tested doesn't have rights to query cases? A bit of a long shot, but I thought I'd check.
With regard to the registry values, you should add them if they are not there. Another approach might be to use the SQL Profile to record the queries hitting the CRM database. You'll need to use some filtering otherwise you'll be swamped with data.
ASKER CERTIFIED SOLUTION
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
First, I enabled tracing on the server and so far I am looking at the trace logs in C:\Program Files\Microsoft Dynamics CRM\Trace and it is not showing me any SQL or much more then what I see in the GUI tracelog. I am just getting the exception I throw wrote in line 33. Am I looking in the right place for trace logs? Any thoughts on how to see more?
Second, the SQL I gave is the literal SQL. I have another pre-create plugin that creates custom incident numbers that are a incrementing 6 digit integer.
Third, I changed the user to run under a user with full access and there was no change.
As to the code I can see that it runs in the built in trace logs in the console(new plugin trace logs in 2016). It processes all the way through line 32 and throws the exception in line 33. I even see the record count in the trace log as 0 from line 32 and the ticket number as 000003 from line 25.
Any thoughts?
Second, the SQL I gave is the literal SQL. I have another pre-create plugin that creates custom incident numbers that are a incrementing 6 digit integer.
Third, I changed the user to run under a user with full access and there was no change.
As to the code I can see that it runs in the built in trace logs in the console(new plugin trace logs in 2016). It processes all the way through line 32 and throws the exception in line 33. I even see the record count in the trace log as 0 from line 32 and the ticket number as 000003 from line 25.
Any thoughts?
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
So per the suggestion I tried to turn on on premise trace using the powershell on https://technet.microsoft.com/en-us/library/hh699694.aspx
I have never used powershell so forgive my ignorance. I ran the following commands and got an error.
Add-PSSnapin Microsoft.Crm.PowerShell
Get-CRMSetting TraceSettings
"Get-CRMSetting : The underlying connection was closed: An unexpected error ocurred on a send."
Microsoft talks about needed to register the powershell commandlets in the article. Do I have to register the XRM Toolkit as discussed here to be able to do anything with CRM using powershell?
I have never used powershell so forgive my ignorance. I ran the following commands and got an error.
Add-PSSnapin Microsoft.Crm.PowerShell
Get-CRMSetting TraceSettings
"Get-CRMSetting : The underlying connection was closed: An unexpected error ocurred on a send."
Microsoft talks about needed to register the powershell commandlets in the article. Do I have to register the XRM Toolkit as discussed here to be able to do anything with CRM using powershell?
Hi pbu-ts,
XRMTooling is not required for your scenario. Try : https://technet.microsoft.
What happens when you just import the snap-in? Do you get any error then?
Regards,
Chinmay.
XRMTooling is not required for your scenario. Try : https://technet.microsoft.
What happens when you just import the snap-in? Do you get any error then?
Regards,
Chinmay.
ASKER
Chinmay,
Good to know. I do not get an error when I run "Add-PSSnapin Microsoft.Crm.PowerShell" In fact if I run a command like "Get-Help *Crm*" before adding the snappin it comes back blank but when I add the snapping and rerun the command. I get a list of Crm related commandlets so it seems like it is successfully adding the snappin. Do I need to set up any sort of connection or anything before running the powershell commandlets?
Good to know. I do not get an error when I run "Add-PSSnapin Microsoft.Crm.PowerShell" In fact if I run a command like "Get-Help *Crm*" before adding the snappin it comes back blank but when I add the snapping and rerun the command. I get a list of Crm related commandlets so it seems like it is successfully adding the snappin. Do I need to set up any sort of connection or anything before running the powershell commandlets?
Your help has saved me hundreds of hours of internet surfing.
fblack61
ASKER
I found the answer to this question. The case number that is being passed into this function had a space in it. Thank you to everyone that helped out.
Wow, that was simple in the end!
Glad you got there.
Glad you got there.
You need to set ReturnTotalRecordCount property to true as per this MSDN article: https://msdn.microsoft.com/en-us/library/gg334688.aspx