Topic: ‘SQL Server’

There’s an interesting post on the MSCRM Team Blog by Inna Agranov that verbalizes how to pre-filter your data in CRM, allowing you to query results much quicker. The post also contains a link to another article, located in the MSDN library. It describes in more detail how to filter CRM reports through SQL Reporting Services.

Click here to go to the Team Blog and read the post, or click here to go directly to the MSDN article.

I was checking out the MSCRM Team Blog today, and I found a new post surrounding the CRM Data Connector. It includes information about what the Data Connector is, how it’s used, and a common issue that can emerge when utilizing the connector – definitely an informative read. Access it on the MSCRM Team Blog site by clicking here.

Below is a simple SQL Statement to change the format of Contacts’ names in Microsoft CRM.  We didn’t reinvent the wheel with this, and we’re sure this is posted somewhere, but we have it and thought we would share.

Run this SQL statement against the MSCRM Organization DB.

(As always, before you make a modification to the DB, it is recommended that you make a backup of the DB or the tables that are being modified.)

 
“Last Name, First Name” format for Contacts:

UPDATE contactbase SET fullname = ISNULL(lastname, ”) + ‘, ‘ +

ISNULL(firstname, ”)

“First Name Last Name” format for Contacts:

UPDATE contactbase SET fullname = ISNULL(firstname, ”) + ‘  ‘ + 

ISNULL(lastname, ”)

 *This solution involves making changes to 1 or more databases.  It is not suggested that changes made to a database without first making a backup of the database.  If possible, alternatives such as a MS Hotfix or MS rollup should be used to try to solve the issue, before modifying a database.  If you do decide to modify the database, it is suggested that the modifications are made by an IT Director, Technical Specialist or MIS department.  Serious problems might occur if you modify the database incorrectly.  Be sure to backup the database before making changes.  Any problems might require that you rollback your databases and even result in the loss of data records.  I.B.I.S., Inc. cannot guarantee that these problems can be solved. Modify the database at your own risk.

An Internet-Facing Deployment allows users that are not on the internal network of your organization to access Microsoft Dynamics CRM through the web.  This functionality is great for those that are in and out of the office or on and off the local network.  Like most of us, having CRM accessible through the web is great, but what about the Outlook Client?  We all live in Outlook, so below is a scenario and a few helpful hints about configuring users’ Outlook Clients for an Internet-Facing Deployment.

Scenario

• The customer has an IFD set up for its CRM 4.0 users.
• Users outside the domain have configured their outlook client using “Service Provider.”
• Currently, when these users are outside of the local network, they do not have an issue accessing CRM. (They can get to CRM through both the Web and Outlook)
• When users are in the office and connected to the local network, they CANNOT connect to CRM through OUTLOOK. 
• When users are in the office and connected to the local network, they CAN access CRM through the web successfully.

Process

It is important to have the users configure their Outlook clients while at the office on the local network.  The only time that we can configure the Client for both internal and external URLs is while the client workstation is in the office on the local network.

•  When the users are on the local network, have them configure the Outlook Client using “My Company.”
• During the configuration, users will come to the option to enter an internal and external URL.
  • There will be a check mark between the text boxes.  Remove the check mark and enter the internal URL used to access CRM.  For example:  http://crm
  • Then enter the URL that they are using to access CRM through IFD while outside the office in the external URL text box at the bottom.  For example: http://crm.infinity.com

  �
  

• Continue to step through the configuration until it completes.
• Restart Outlook.
• Disconnect from the local network, and confirm that the outlook client can still be accessed with full functionality.

Assessment

What we have successfully accomplished is the ability for users to work in the Outlook Client, both on and off the local network.  When the users are connected internally, the Outlook Client will connect to CRM using the internal URL by default. Once they leave the internal network, the Outlook Client will attempt to connect using the internal URL, but when it sees that it cannot, it will then use the URL used to connect through IFD.

If we were to configure the Outlook client as ‘Service Provider,’ we do not give users the ability to use the Outlook Client on the local network.  This occurs because they have not defined an internal URL for CRM.

As always, if information such as this does not resolve your issue, documentation from Microsoft should be referenced.  This post should not replace documentation from Microsoft; however it can serve as a guide to solving your problems and as a helpful reminder.

When developing SQL Reporting Services Reports, you surely are aware of the checkbox to allow the “Page break at start” option when grouping data. However, you cannot allow a user to toggle whether or not they want a page break after each group. I didn’t like the idea of creating a second report that had the Page break option checked, so here is another solution starting with an existing report.

Our starting point is a simple Contact report that groups Contacts by the Account name. The initial report layout would look something like the one below. Please note that the group we currently have does not have the “Page break at start” option checked.

The first thing we need to do is to create a report parameter for the user to set as they please. Click the Report menu and select Report Parameters. Create a parameter similar to the one below. Click the Add button and name it “PageBreak.” Set the Data type to “Boolean” and enter a Prompt of “Put a Page Break after each group?” Set the Default Values to “Non-queried” and type in “False” without any punctuation. Click OK.

Next, we need to add another group above our existing group. This group will contain a formula based on our parameter and display the page break or not. Right click the left most cell of the header row (left of the cell showing FullName) and choose Insert Group. Enter values as in the screen shot below.

Name it PageBreak, Expression value is: =IIF(Parameters!PageBreak.Value,Fields!SomeField.Value,”")

Make sure to check the “Page break at start” checkbox. Lastly, click the Sorting tab and be sure to sort on Account Name (=Fields!accountidname.Value). This should be the same sorting as on the original group in your report.

Your report should now look like the screen shot below. The new group is highlighted.

Now, preview your report. You will see your Page Break parameter at the top of the report defaulted to false. Take a look at the page count.

Now, change the parameter to True, and click View Report. You will notice the page count will increase quite a bit.

That will do it.

“Microsoft SQL Server® 2008 contains a variety of features that, when implemented properly, can improve the performance of a Microsoft Dynamics® CRM 4.0 implementation and secure the data within that deployment….”

Click on the link below to navigate to the Microsoft Download Center and download the white paper:

http://www.microsoft.com/downloads/details.aspx?FamilyID=b5bb47a4-5ece-4a2a-a9b5-5435264f627d&DisplayLang=en

This one-day interactive workshop teaches you the basics on SQL Server Reporting Services (SSRS) reports. This course will teach you how to create and deploy SSRS reports. This course is for SQL savvy report writers and other technical personnel running on Microsoft Dynamics CRM 3.0 and higher and SQL 2005.

Here’s a link to the class:

http://www.ibisinc.com/SSRS_CRM_Class_Description.html

A SQL guru in our office put together this document on how to setup an IFD with Exchange Server installed:

1. Install the IIS resource kit.

2. Navigate to the CRM4IFDtool.exe

3. Set the tool to these settings.

4. Set Authentication to IFD+OnPremise

5. Add the local subnets that will not be using external addressing.

6. Set the external to https and app root to external name:plus crm port

7. Set the internal app root name: plus port. Find the site ID that you want to add the certificate to. IISweb.vbs /query “name of site using http name.” Look at the first column (”default web site <w3svc/1>. The 1 tells you the ID number for this site. Enter it in the command below “/S:#”

8. Then create a self-signed certificate.

9. Open All Programs, IIS resources, self-signed. Type selfssl.exe /N:CN=”certname” /K:1024 /S:1 /P:443

10. This will add the certificate to the server’s web site.

11. This does not put the certificate in the trusted store for the machine.

12. Open cmd line and type mmc.

13. Select File, then select Add/Remove Snap-in.

14. Select ADD and Certificates.

15. Then select ADD, Computer Acct, Next, Finish, Close, and OK.

16. Expand certificates to trusted store, certificates.

17. Now open IIS Manager.

18. Right click on the CRM site and select Properties. Select Directory Security, Server Certificate. You will go into a wizard mode (you are going to export the certificate to add to trusted certificates).

Select Export the current certificate and NEXT. You can select any path as long as you can get to it from the machine. Set a short password and mark key as exportable. Next, click Finish.

19. Import the certificate to the trusted store. Right-click the certificate folder. Select All Tasks, and then select Import.

Select Next and Browse. Change file type to .pfx. Select File and OPEN.

Click on NEXT and type in short password created from export, NEXT. Mark key as exportable. The Trusted Root Certificate store s/b selected, NEXT and Finish.

21. Close MMC you do not have to save the console, and close IIS.

22. If the above example is exact and you are using port:555 for application then you will need to have network admin allow the port through the firewall to this server

23. Add a public dns name to their external zone (ex: crm.XXXXX.com)

24. Test the application. Have a user open https://crm.XXXXX.com:5555

Take the scripts from this document, and create a SQL job to run nightly after a backup to ensure optimal SQL performance.

Rebuild Indexes

For SQL Server 2005:

a) Click Start, point to Programs, point to Microsoft SQL Server 2005, and then click SQL Server Management Studio.

b) In the Connect to Server dialog box enter the server name and select Windows authentication credentials, and then click OK.

c) In the Database Selection menu, select the Microsoft Dynamics CRM database that you want to run this script against.

d) In the Query window, type the following commands:

On the toolbar, click Execute Query. The results appear in the results pane.
De-Fragmenting Indexes with a Script

For SQL Server 2005:

e) Click Start, point to Programs, point to Microsoft SQL Server 2005, and then click SQL Server Management Studio.

f) In the Connect to Server dialog box enter the server name and select Windows authentication credentials, and then click OK.

g) In the Database Selection menu, select the Microsoft Dynamics CRM database that you want to run this script against.

h) In the Query window, type the following commands:

Update Statistics: