Thursday, November 29, 2018

How to generate sql scripts for SQL Server Database without its organisation data or information


There may be few scenario where we want to mimic our (production) database structure in some other local environment so that development team can proceed with their integration or development activity.

We defiantly do not want to share our data or information for security reason.
However, there could be a possible reason that we might be storing some configuration data that development team might be interested in it.

For an example Employee Designation or Rank, various type of leaves.

Please note: For security reason few of the images are been altered to hide the server and credential details

  1. Go and open SQL Management Studio.
  2. Connect to your Database Server.
  3. Expand the list of Databases from Object explorer.
  4. Right click the desired Database and navigate to Tasks -> Generate Scripts…
  5. The above action will launch the Generate and Publish Scripts wizards. Proceed with next button.
  6. Select the option for Script the entire database and all database objects and proceed with Next button.
  7. Choose output type to Save scripts to a specific location.
  8. Choose option to Save to file. Enter manually the file name with extension .sql as depicted in below image. This is the location where generated script will be saved.
  9. Proceed further by clicking on the next button.
  10. Review your selections and click on the next button to proceed.
  11. You should see the generation of scripts began and it may take a while depending upon the complexity of database metadata.
  12. Click on Finish button and locate the file on the destination folder choose in one of the above steps.


Wednesday, November 28, 2018

Query data with many to many relationship Dynamic 365 CRM

I have created a generic method with detail explanation to get data about entity, which are in many to much relationship. Please refer following blog:



Thanks.
Vipin Jaiswal
vipinjaiswal12@gmail.com

Friday, November 23, 2018

Fetch Marketing List of a campaign with C# plugin in Dynamic 365 CRM


How to fetch the marketing lists associated with a campaign or campaign activity in Dynamic 365 CRM?

Within Dynamics 365 CRM, there are a number of entities that have N:N relationships. One of these is the CampaignItem entity that connects Campaigns, Marketing Lists, Products and Sales Literature.

I have created a generic method below in C# with detail explanations.



Here is how I am invoking my method

EntityCollection DynamicMarketingLists =
    GetManytoManyRelationShip(CampaignActivityId,
                                    "list",
                                    "listid",
                                    "campaignactivitylist_association",
                                    "campaignactivity",
                                    "subject",
                                    _service);




Here is method definition


private EntityCollection GetManytoManyRelationShip(
    Guid entityBGuid,
    string offenceEntityName,
    string offenceColumnSet,                                                            
    string relationShipName,
    string legalEntityName,
    string legalColumnSet,                                                            
    IOrganizationService _service)
{
 QueryExpression query = new QueryExpression(offenceEntityName);
 ColumnSet cols = new ColumnSet();
 cols.AddColumn(offenceColumnSet);
 cols.AddColumn("listname");
 query.ColumnSet = cols;

 Relationship relationship = new Relationship(relationShipName);
 RelationshipQueryCollection relationshipColl = new  RelationshipQueryCollection();
 relationshipColl.Add(relationship, query);

 RetrieveRequest request = new RetrieveRequest();
 request.RelatedEntitiesQuery = relationshipColl;
 request.Target = new EntityReference(legalEntityName, entityBGuid);
 request.ColumnSet = new ColumnSet(legalColumnSet);
 RetrieveResponse response = (RetrieveResponse)_service.Execute(request);

 return response.Entity.RelatedEntities[relationship];
}











Thanks.
Vipin Jaiswal
Vipinjaiswal12@gmail.com

Wednesday, November 7, 2018

Export Solution from MSCRM version 9 and import in version 8



One of my colleague who was remotely supporting me with some customizations was not able to access CRM environment, so he suggested to developed thing on free trail of Dynamic CRM and later to import customizations. He has done customization in specific solution in CRM Online version 9.

We realized that the target environment is version 8 and of On-Premise, CRM by default does not support later version to be imported to old version.

In order to save ourselves with lot of rework, we took some time to import solution by making certain changes in customizations.xml and solution.xml and yeah it work.

Here are list of changes we did.

1) Remove following Tag’s from customizations.xml

<IsDataSourceSecret>0</IsDataSourceSecret>

<AutoNumberFormat></AutoNumberFormat>

<IsBPFEntity>0</IsBPFEntity>

<IsRetrieveAuditEnabled>0</IsRetrieveAuditEnabled>

<IsRetrieveMultipleAuditEnabled>0</IsRetrieveMultipleAuditEnabled>

<EntityDataProviders />

<IsVisibleInMobile>1</IsVisibleInMobile>
         
<IsVisibleInMobileClient>1</IsVisibleInMobileClient>

<IsReadOnlyInMobileClient>0</IsReadOnlyInMobileClient>

<IsOfflineInMobileClient>0</IsOfflineInMobileClient>

<MobileOfflineFilters></MobileOfflineFilters>


2) Remove ExternalValue="" and Color="#0000ffAttribute from <option> tag in customizations.xml




As old version of dynamic CRM does not support these attributes for optionset.


3) Add <ObjectTypeCode> tag in customizations.xml for all entities listed as highlighted in below image.





Object type code should be specific to entity being used.

Later version of Dynamic CRM expect this tag under
Entities >> Entity >> Name >> ObjectTypeCode.


4) In Solution.xml file we need to change the value of attributes of very first Tag <ImportExportXml> with respect to the target environment.

As an example, this is what it looks like when it is Dynamic CRM version 9.1

<ImportExportXml version="9.1.0.626" SolutionPackageVersion="9.1" languagecode="1033" generatedBy="CrmLive" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

In my Case I need to change it to following one.

<ImportExportXml version="8.1.0000.0359" SolutionPackageVersion="8.1" languagecode="1033" generatedBy="OnPremise" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

Just a warning note, this may not be the best practise or a supported way from Microsoft Dynamic CRM.

In addition, while doing this there could be more errors that needed to be resolved.

However, we have created hundreds of fields and we do not want to repeat the process of making the fields in Dynamic CRM again, so this help us a lot in saving our time.

Please let me know what other errors in your scenario you getting.

Thanks,
Vipin Jaiswal
vipinjaiswal12@gmail.com