I Have Been Renewed as a Microsoft MVP for 2014!

I received  notification yesterday that I have been renewed for 2014 as a Microsoft MVP for Dynamics CRM.  This is pretty exciting.  It should be an awesome year.

-

Solid Reasons Not To Do Direct Database (T-SQL) Operations on Dynamics CRM Database From the Pros

I got an email this morning from an old college classmate of mine in kind of a exasperated tone with a one word subject "HELP!".  It turns out that she has fellow IT folks that want to replace some updates she is doing regularly using workflow that have worked fine for a long time with nightly T-SQL batch jobs pushing the data from their data warehouse back into CRM, even though it was already in CRM to begin with.

I decided to delve into this a bit to see what are the real reasons for why this shouldn't happen.  I knew some but I also asked some of my other MVP buddies to get their feedback.  Here is what I came up with.   Feel free to use these arguments if you need to explain to your IT department why manual database operations are a bad idea.

Jamie Miley 

• If you are using Auditing in CRM, it won't reflect any changes done by manual database update.

• Modified, by, modified on, etc... will also not be adjusted properly

• PrincipleObjectAccess table will not be updated based on manual updates and so permissions will not be properly set on any inserted, updated, deleted records.

Scott Sewell

•  Database updates to CRM, if you did updates to something that affects security (owner, business unit, etc...) no updates would occur to PrincipleObjectAccess table

• Updating Name fields will cause issues because lookups tend to cache names

Damian Sinay:

• Plugins and/or workflows won't fire

• Also caching is an issue, any cached data won't be invalidated by a database operation.

Gustaf Westerlund

• If you are not 110% sure what youa re doing you are risking the stability of the entire system.

Julie Yack

• It’s bad karma to do unsupported stuffs when a supported way will do it.

Carston Groth

• Relations might get lost if you´re only performing the action on one datebase table ignoring all related tables

Joel Lindstrom

• Biggest reason is that it will appear to work initially but problems will crop up later and you won’t be able to connect the dots to the real issue because the issue won’t be caught by the normal error reporting mechanisms.

Example:

Customer manually loaded contact records and later couldn’t reassign them. Turned out to be because in their manual load they didn’t populate businessunitid. The contacts worked, but couldn’t be reassigned later because that field wasn’t populated, but the error message generated didn’t explain what the problem was, because records created in a supported way always have that field populated

Different customer manually overwrote the createdby and modifiedby using unsupported T-SQL. Records initially appeared to work OK; however, when users attempted to forward the message in CRM, they got an error. Again, since this was a delayed error situation that showed up months later, it was very difficult to find the real reason for the error—all diagnostics did not show the real problem.

So to me, that is the biggest reason not to create records in an unsupported way. It is very difficult to verify that it is correct because standard system data validation does not fire, and if you miss anything, the real problem may not show up for months and will most likely be outside of the normal error reporting mechanism. You are on your own. Was it worth it?

In Conclusion

In the end, I think these are all great ideas regarding the issue.  I really just want to echo Gustaf above.  The fact is that it is considered unsupported for a reason.  Microsoft doesn't want to deal with it either.  All unsupported customizations are adding serious risk to your entire implementation as Microsoft would be entirely within their right to wipe their hands of the entire implementation when they can show that these types of things are being used.  The API and SDK are there for a reason, please use them. There are ways to do almost anything you could be trying to do that would cause you to go an unsupported route.  This is where a good partner can really steer you in the right direction.  You have already invested a lot of money on your Dynamics CRM implementation in software costs and in most cases consulting time to put it into place and get it customized properly, protect that investment!

- I hope this helps!

-

Format of Jscript SOAP Responses in Microsoft Dynamics CRM 2011 Organization Service has Changed in UR 12!

You used to be able to pass in your response.responseXML.xml into a function like the one below to parse out a response and pull individual attributes.

function parseResponse(responseXML, attributename) {

    debugger;
    xmlDoc=new ActiveXObject("Microsoft.XMLDOM");
    xmlDoc.async="false";
    xmlDoc.loadXML(responseXML);
    x=xmlDoc.getElementsByTagName("a:KeyValuePairOfstringanyType");
    for (i=0;i<x.length;i++)
   {
      if (x[i].childNodes[0].text == attributename)
      {
         //we decode the base 64 contents and alert the HTML of the Iframe
          alert(x[i].childNodes[1].text);
      }
      
   } 
}

PROBLEM:  With UR 12 and above you can still use this type of function but the responseXML property no longer exists on the response object.

SOLUTION: now instead of passing in your myresponse.responseXML.xml property of your response, now the property of your reponse is just in a  new response property, so the new syntax would just be myresponse.response

I hope this helps!!

-

Using RetrieveAttribute Request in CRM 2011 To Get the Label Value for an OptionSet

Here is a quick way to get a label for an OptionSet value in Microsoft Dynamics CRM 2011 using RetrieveAttributeRequest.

private string GetCRMOptionSetLabel(IOrganizationService service, string entityname, string optionsetname, int value)
{
            
    RetrieveAttributeRequest reqOptionSet = new RetrieveAttributeRequest();
    reqOptionSet.EntityLogicalName = entityname;
    reqOptionSet.LogicalName = optionsetname;
    RetrieveAttributeResponse resp = (RetrieveAttributeResponse)service.Execute(reqOptionSet);
    PicklistAttributeMetadata opdata = (PicklistAttributeMetadata)resp.AttributeMetadata;
    var option = opdata.OptionSet.Options.FirstOrDefault(o => o.Value == value);

    return option.Label.LocalizedLabels.FirstOrDefault().Label;
}

- I hope this helps!

Successful Downgrade of a Dynamics CRM 2011 Database from SQL 2012 to SQL 2008 R2

DISCLAIMER: I do not take any responsibility for any damage done by using this process.  I do not think it would be considered "supported" by Microsoft either.  This post is for education use only.  My scenario was  creating an organization for UAT only.  The real production org during our migration is not going to go through this process.

Let me start by saying that this is not a normal scenario.  Usually you are only going the other way.  But we took a copy of a client org out of a SQL 2008 R2 environment to do some work on it and then we needed to re-upload it to their instance as a UAT org and to test a process by which we were going to merge their two client organizations.

What we missed in the process was that we did our work in our sandbox environment and, in the process, upgraded the organization database to SQL 2012.  This presented a problem when trying to re-attach the org in their environment.

I first tried to use the copy database functionality and even export data functionality in SQL Server Management Studio as I found on some blogs to try to "downgrade" the database.  This did not work, I got errors using both methodologies for various reasons.

I decided to try one last thing and script out the database entirely in version 2008 R2 scripts and push it into the environment and see if Deployment Manager would accept it.

First, in the SQL 2008 environment I created a shell database with the same name to script everything into.
Then in the source SQL 2012 environment I right clicked on the database - clicked "Tasks" - and clicked "Generate Scripts".

The settings for the scripts are below:

1. choose to script the entire database and all objects

2. Here are the base options, click the "Advanced"  button to set the advanced options you will need.

3. Match the options from the next three screen shots

4. Now generate your script file.

5. If this file is big it might not open and run in Sql Server Management Studio.  If this is the case, use the command line tool sqlcmd.exe  to run the query on the target machine.  I had to do this with mine because my script file was over 900 mb.  If you need help with sqlcmd.exe syntax, use this MSDN article: http://msdn.microsoft.com/en-us/library/ms162773(v=sql.105).aspx

IMPORTANT: Specify an output text file when you run it with the -o option.  When you run the script you will see some errors at the top of the output file because it will try to script some security objects, this is ok, there should be no expectations that it would successfully be able to find these security objects in the target system, like AD groups by GUID and such.   Eventually you will start seeing the rows affected lines (usually 1 row at a time) being inserted.  This will take hours.  For a 950mb script file it took me about 3 hours to run.

When it was all done there will probably be a warning at the end of the file (not an error) about the large indexes CRM uses in some cases).  This is normal and can be ignored.

6.  Rename your org database (if desired), and import it via normal mechanisms in deployment manager.

For me it took the org as expected and it functions normally.

- I hope this helps!

DISCLAIMER: I do not take any responsibility for any damage done by using this process.  I do not think it would be considered "supported" by Microsoft either.  This post is for education use only.  My scenario was  creating an organization for UAT only.  The real production org during our migration is not going to go through this process.

Dynamics XRM Application Speed Builder Now Supports Access and Relationships

Jason Lattimer and I have released the next version of the Dynamics XRM Application Speed Builder.  The new version adds support for:

- Microsoft ACCESS as a datasource (previously only supported SQL Server)

- Full Relationship Support (allows you to explicitly specify relationships, but will also optionally build them based on relationships that already exist in your data source.

Overview from the CodePlex Page:

The Dynamics Xrm Application Speed Builder will analyze databases, then create the entities, attributes, relationships and forms in CRM for you.

The current functionality will read a SQL Server or MS Access database and create new CRM entities, attributes, and relationships based on the existing table structure. Metadata required to create the objects is pulled in and data types are matched up with their corresponding CRM types to reduce the work needed to get things created. At the same time most everything can be overridden before being created just in case your source database isn't perfect :) Last but not least we've included the option to drop all the newly created fields on the main form to give you a head start for creating the first user interface. 

Download the new version here:

http://xrmspeedy.codeplex.com/

If You Have Trouble Connecting up to CRM Online (O365) Using the Outlook Client

I don't know why but it seems like lately using the old "CRM Online" in the server URL doesn't work anymore.  I seem to have to use "https://disco.crm.dynamics.com" now.

I know Chris Cognetta from Tribridge has also blogged this, I haven't seen the article, but I am not the only one that this has caught off guard.

-Happy Saturday!

Check Out This Great Article About Being a CRM MVP From Gus Gonzalez!

Check out this great article about what it's like being a CRM MVP from Gus Gonzalez.  It features many prominant MVP's in the Microsoft Dynamics CRM community.  Including myself ;)

Bonus:  Scott Sewell evindently is an aspiring cat juggler.


Check it out: https://community.dynamics.com/product/crm/crmnontechnical/b/adopt2win/archive/2013/01/08/so-you-want-to-be-a-crm-superstar.aspx

-

Overriding the CreatedOn Attribute On Entities in Microsoft Dynamics CRM 2011

I wanted to write a quick blogpost because I think there are a lot of people out there that have never used the overriddencreatedon field that is on all CRM 2011 entities.  If you have ever tried to manipulate the createdon field manually using the CRM sdk you will find that it is basically read-only.

There is a workaround for this though.
If you populate the overriddencreatedon field when you insert your entities you can specify your own datetime for the createdon field.  Now when the field is saved the overriddencreatedon field datetime is put in the createdon field of the entity and the REAL createdon field is still saved in the overriddencreatedon field.

So all you have to do is populate the overriddencreatedon field and it will in turn populate the createdon field and the real date will then be saved in the overriddencreatedon field.

There is a limitation to this.
You cannot insert a datetime in the future.  This will throw an error.  If you are using an application on another server and using datetime.now you might want to also use .addminutes(-5) or something to that effect because if the server times are out of sync by even a little bit and the application server tries to insert a time that is in the future for the CRM server, it will fail.

-Happy Tuesday

The CRM Field Guide - A New Book by Various CRM MVP's Has Been Released

Many of the CRM MVP's  banded together and compiled their knowledge to release a stellar book that covers most aspects of CRM.  This book, with it's many knowledgeable authors, can be considered the true experts guide when it comes to Microsoft Dynamics CRM 2011.

Bonus:  I contributed to a small part of chapter 1  :)


Check it out here:
http://www.crmfieldguide.com/

or buy it from Amazon here:

http://www.amazon.com/The-Field-Guide-Joel-Lindstrom/dp/0981511899/ref=sr_1_1?ie=UTF8&qid=1348848654&sr=8-1&keywords=crm+field+guide

-Happy Monday!

Get User Roles from User ID Using C# in Microsoft CRM 4.0

It is fairly easy to use the web service to get the roles for a particular user based on their GUID.
This methodology is based on a post by Jim Wang at http://jianwang.blogspot.com/2008/01/crm-40-check-current-users-security.html in client-side jscript ; there isn’t much good information out on the web on how to do this in C#, and it is a bit more difficult than a normal retrievemultiple call considering it is a many-to-many relationship, so… here it is!

On a side note, congratulations Jim on getting awarded with the honor of CRM MVP.

public BusinessEntityCollection GetUserRoles(Guid guidUserID, ICrmService serv)
{
try
{
QueryExpression qe = new QueryExpression();
qe.EntityName = "role";
qe.ColumnSet = new AllColumns();

LinkEntity le = new LinkEntity();
le.LinkFromEntityName = "role";
le.LinkFromAttributeName = "roleid";
le.LinkToEntityName = "systemuserroles";
le.LinkToAttributeName = "roleid";

ConditionExpression ce = new ConditionExpression();
ce.AttributeName = "systemuserid";
ce.Operator = ConditionOperator.Equal;
ce.Values = new object[] { guidUserID };

LinkEntity le2 = new LinkEntity();
le2.LinkFromEntityName = "systemuserroles";
le2.LinkFromAttributeName = "systemuserid";
le2.LinkToEntityName = "systemuser";
le2.LinkToAttributeName = "systemuserid";

le2.LinkCriteria = new FilterExpression();
le2.LinkCriteria.Conditions.Add(ce);

le.LinkEntities.Add(le2);
qe.LinkEntities.Add(le);

RetrieveMultipleRequest rmRequest = new RetrieveMultipleRequest();
rmRequest.ReturnDynamicEntities = true;
rmRequest.Query = qe;

RetrieveMultipleResponse response = (RetrieveMultipleResponse)serv.Execute(rmRequest);

if (response.BusinessEntityCollection.BusinessEntities.Count > 0)
{
return response.BusinessEntityCollection;
}
else
{
return null;
}
}
catch (Exception ex)
{

WriteToFile("error: " + ex.ToString());

return null;

}
}

This post is provided as-is and implies no warranty; Jamie Miley does not assume any responsibility for problems arising from the use of this information.

Programmatically rendering a SQL Server Reporting Services (SSRS) report from Microsoft CRM 4.0 and capturing output in a file.

Programmatically running a SQL Server Reporting Services report and capturing output in a file for use from CRM.

 

It is possible in CRM to use SQL Server Reporting Services in order to run a report and capture the output in a file.  The first problem is that you only see GUIDs listed for CRM reports under the SSRS report manager.  To actually be able to run the report on the report server from the CRM report name you must publish the report for external use. 

 

1.       Go to “Workplace” and then “Reports” in CRM and click on the report you want to edit and click “Edit Report”.

2.       Now click “Actions” and then “Publish Report for External Use”

 

 

 If you now go into Sql Report Manager at http://myreportserver.mydomain.com/reports and go to your specific org database name, you will see the report you published for external use and any sub reports that are utilized by it.  This now allows you to use the web-service and call it by name.

 

 

 

Now, create a plug-in or other application you want to run the CRM report.

In Visual Studio, make a web service reference to the SQL Server Reporting Services web service(. And use something similar to the following snippet of code to render the file and save the output to a file.

If you are using a separate application you would probably want to hardcode credentials, but if you were doing this in a plug-in or custom workflow assembly you would want to use the default credentials.

I also left some commented out code in this example to show how to populate report parameters.

 


ReportExecutionService rs = new ReportExecutionService();
 

//rs.Credentials = System.Net.CredentialCache.DefaultCredentials;

rs.Credentials = new System.Net.NetworkCredential("username", "password", "domain");

rs.Url = "http://MySqlServer.MyDomain.com/reportserver/ReportExecution2005.asmx";

 

// Render arguments

byte[] result = null;

// Make sure you use your correct org database name of the following line

string reportPath = "/jmiley_MSCRM/Account Overview";

string format = "PDF";

string historyID = null;

string devInfo = @"<DeviceInfo><Toolbar>False</Toolbar></DeviceInfo>";

 

// Prepare report parameter.

//ParameterValue[] parameters = new ParameterValue[3];

//parameters[0] = new ParameterValue();

//parameters[0].Name = "EmpID";

//parameters[0].Value = "288";

//parameters[1] = new ParameterValue();

//parameters[1].Name = "ReportMonth";

//parameters[1].Value = "6"; // June

//parameters[2] = new ParameterValue();

//parameters[2].Name = "ReportYear";

//parameters[2].Value = "2004";

 

DataSourceCredentials[] credentials = null;

string showHideToggle = null;

string encoding;

string mimeType;

string extension;

Warning[] warnings = null;

ParameterValue[] reportHistoryParameters = null;

string[] streamIDs = null;

ExecutionInfo execInfo = new ExecutionInfo();

ExecutionHeader execHeader = new ExecutionHeader();

rs.ExecutionHeaderValue = execHeader;

execInfo = rs.LoadReport(reportPath, historyID);

//rs.SetExecutionParameters(parameters, "en-us");
String SessionId = rs.ExecutionHeaderValue.ExecutionID;

Console.WriteLine("SessionID: {0}", rs.ExecutionHeaderValue.ExecutionID);

try
{
result = rs.Render(format, devInfo, out extension, out encoding, out mimeType, out warnings, out streamIDs);

    execInfo = rs.GetExecutionInfo();
Console.WriteLine("Execution date and time: {0}",    execInfo.ExecutionDateTime);

 }
 catch (SoapException err)
 {
    Console.WriteLine(err.Detail.OuterXml);
 }
 // Write the contents of the report to an PDF file.
 try
 {
    FileStream stream = File.Create(@"c:\upload_report.pdf", result.Length);
    Console.WriteLine("File created.");
    stream.Write(result, 0, result.Length);
    Console.WriteLine("Result written to the file.");
    stream.Close();
  }
  catch (Exception error)
  {
     Console.WriteLine(error.Message);
  }

You can view full documentation on the SSRS web service from MSDN at:

http://msdn.microsoft.com/en-us/library/ms152787(SQL.90).aspx

 

Instead of PDF files, you can also create other types of files by changing the format string:

-          MHT web archive – “MHTML”

-          XML – “XML”

-          CSV – “CSV”

-          IMAGE – “IMAGE”

-          EXCEL – “EXCEL”

-          HTML – “HTML4.0”, “HTML3.2”, “HTMLOWC”

 

There are a few things to consider:

 

1.       The file copy operation above will use the app domain’s credentials for creating the output file.

If you are using a plug-in or workflow assembly this will be the CRM app pool security account.

If you want the file to be created on a different server, you may want to change the app pool security account to an Active Directory account instead of the default “network service”.

 

2.       Default credentials should be used for the web-service for plug-ins and workflow assemblies as it uses CRM security and will not let people access data through the reports that they do not have read access to in CRM.

 

This post is provided as-is and implies no warranty; Jamie Miley does not assume any responsibility for problems arising from the use of this information.

 

 

Map Custom Attributes from Opportunity Product to Quote Product to Order Product and so on in Microsoft CRM 4.0

It is possible to map custom attributes from opportunity product to quote product to order product to invoice product within CRM.  The best part about this workaround is that according to a recent Microsoft support case I opened, this is a supported workaround.

I will demonstrate this by going from quotedetail (quote product) to salesorderdetail (order product):
In SQL Server Management Studio.  Run the following query:
Select * from entitymapbase where targetentityname = 'salesorderdetail'
This query should return three items, we care about the row with a SourceEntityName column value of "quotedetail".

Now copy the GUID value of the EntityMapId column for that row.

Then I use this URL, and at the end of it I paste the GUID that I just copied:
http://yourservernamehere/yourorgname/Tools/SystemCustomization/Relationships/Mappings/mappingList.aspx?mappingId=

This gives me the secret hidden mappings that I was after, between "Quote Product" and "Order Product".  It is just another normal CRM GUI relationship mappings form.

This post is provided as-is and implies no warranty, Jamie Miley does not assume any responsibility for problems arising from the use of this information.