Topic: ‘SQL Server’

While setting up an integration for a client, it became apparent that there is a problem with the MSGP Publisher that will only allow you to publish messages to the scribepublisherqueue for specific tables. After adding a table to the publisher and choosing options, I tried to save the publisher and it threw the error “Error when setting up GP Publisher: Sequence contains more than one matching element”. I never did figure out what that meant. After doing my diligence and checking the knowledgebase and forums on Open Mind to no avail, Scribe support was contacted for some assistance. They informed me that there is a known issue with many tables not being accounted for by the publisher and that it is a defect and there is no fix at this time. This left me with no resolution or workaround (thanks Scribe!) While Scribe gave us what they considered an acceptable answer to the problem, it didn’t solve anything for our client’s integration which left us to figure out one on our own. Here is what I came up with: Read the rest of this entry »

For simple exports to Microsoft Excel, you can use the Advanced Find functionality to find the records, and then export. But keep in mind that you can only get one data set per Excel file. What about when the requirement is to export data from CRM to a multiple-tabbed Excel file where each tab is a different data set? Read the rest of this entry »

There is a big change in CRM 2011 that isn’t really talked about much, but it has an impact in some key areas. In CRM 4.0 there was a column in the database tables for each entity to tell you when a record was deleted or not – DeletionStateCode.* It had the following possible values: 0 meaning the record was active in the database, 2 meaning the record was marked for removal when the deletion service next runs, and 1 which was not used. On a schedule the deletion service would skim through the tables and remove all the records with a value of 2.  Fortunately or unfortunately (depending on how you look at it), the deletion service had problems and often didn’t run or would leave records in the system. When developing against the CRM environment (i.e. any custom SSRS reports, SQL queries, custom web pages, or plugins), you would need to take into account records with ‘deletionstatecode = 2’ (either excluding/including them based on what you were trying to do). There was an upside however; the inclusion/exclusion made it easy to be able to go back and look for missing records that may have been deleted accidentally and recover them or determine who had deleted them.

In CRM 2011, ‘DeletionStateCode’ no longer exists. When you delete a record, it goes bye-bye for good. There is no trace of it left behind. This means that you do not have to include it in your customizations against CRM anymore. If you want to be able to go back and retrieve old records you will need to do a restore to a backed up version of the database.  If you want to see who deleted what, you will need to use some type of audit logging.**

How does this affect your system? If you are new to Dynamics CRM 2011, it has no impact, but if you are upgrading…this means you may have some work to do. If you have customized your system beyond simple configuration and reference DeletionStateCode in your custom code, reports, or queries, you will need to go and remove those references to prevent errors from rendering your customized system useless. Additionally this is very important when planning for your upgrade from CRM 4.0 to CRM 2011. Make sure that you run the deletion service manually prior to upgrading and also check that all records with a DeletionStateCode = 2 that the service might have missed get removed from the system as well. If there are any records remaining in the database when the system gets upgraded, they will magically reappear as active records when you log into CRM 2011! While it might be a little tedious, it is not a difficult change process if you know ahead of time what to expect.

*Microsoft will tell you that it was unsupported to reference the DeletionStateCode if you ask them to assist in fixing any problems you may encounter in regards to this field. Catch 22 – you had to reference it while customizing 4.0 because the deletion service never worked and your data would be incorrect. Good news is, you don’t need to worry about it anymore because they removed the process - but your upgraded customizations will have to be modified.

**CRM 2011 has auditing functionality built in natively.

You have created a stored procedure, tested it, and figured out how to call it from X++. However, when you execute your code the procedure doesn’t work as expected. Your calculations are inconsistent.

Execution feedback is one reason that will cause inconsistent, wrong calculations when your stored procedures are called from X++. Feedback messages interrupt your X++ execution of your SQL stored procedure. For example, if you have a have a While Loop that updates five data rows only three might be updated because the execution was interrupted, by the feedback, before your Loop got to row four.

Read the rest of this entry »

I recently deployed an SRS Dashboard into an environment that had some interesting restrictions. Users inside the network could access through the usual http://crmserver:5555/ URL. However, users outside of the DMZ had to access CRM through a special URL, such as http://crm.companyname.com:5555. The problem with this is that the URL that you place in the sitemap to point to the dashboard report is static. To get around this and account for the different access methods we did the following.

Note: This post is contingent upon already knowing how to display a Dashboard as an option on the Navigation Bar or as your home page.

1)      Get the URL of the Dashboard report you are referencing. To do this, run the dashboard report from the reports area of CRM. Once rendered, hit CTRL-N to open the address bar. Copy the contents of the address bar. It will look something like this:

http://CRMORGNAME:5555/crmreports/viewer/viewer.aspx?id={4821DECB-EDA9-DE11-A944-000C291643D3}&helpID=SALES%20DASHBOARD.rdl&action=filter

2)      Open Notepad and paste the following text into it. What this does is examine what URL is being invoked and uses that specific URL. Be sure to note the following:

1. In the strReport variable the port is stripped out and only the organization name is used. The rest is exactly what you copied in step 1.
2. Replace any text that is in all caps with your deployment specific information.

<%@ Page AutoEventWireup=”true”  %>

 <%

            string strReport = “/CRMORGNAME/crmreports/viewer/viewer.aspx?id={4821DECB-EDA9-DE11-A944-000C291643D3}&helpID=SALES%20DASHBOARD.rdl&action=filter”;

            string strHost = Request.Url.Host;

            string strUrl = “”;

            if (strHost.ToLower() == “CRM.COMPANYNAME.COM”)

            {

                //Response.Redirect(strUrl + strReport);

                strUrl = “http:// CRM.COMPANYNAME.COM ” + strReport;

            }

            else

            {

                //Response.Redirect(“http://crmserver:5555″ + strUrl);

                strUrl = ” http://crmserver:5555″ + strReport;

            }

            Response.Redirect(strUrl);

        %>

3)      Save this file as DashboardRedirect.aspx in the \ISV folder underneath the installation folder of your CRM deployment.

4)      Export the Sitemap.xml from CRM.

5)      Open the file in a text editor.

6)      Find where you want to have the Dashboard link displayed on the navigation bar and paste the following text:

          <SubArea Url=”/ISV/DashboardRedirect.aspx” Icon=”/_imgs/ico_16_9102.gif” Title=”Sales Dashboard” />

7)      Save the Sitemap.xml and import back into CRM.

Now, when clicking on the Sales Dashboard link from the navigation pane, the redirect page will be called and the correct URL for the dashboard report will be invoked.

We have a client that uses the On Premise Microsoft CRM 4.0 in both internal and IFD mode.  The client also used SQL Reporting Services (SRS) to display a dashboard and the link to it is contained in the Site Map.  The SRS was also exposed through the IFD.  The issue was the Site Map link would either provide a link to internal or external URL, and since the authentication is different between internal and IFD mode, if a user wasn’t already using that mode they would be prompted to login or not have access at all.  While a real easy fix could have been providing two different links, try explaining to the average user which link they should for which mode they are using.

The solution we came up with was to have the site map pointing to a custom .NET web page that would do the appropriate redirecting.  The web page gets the Request.Url.Host and compares it against the IFD host string.  If it is a match you redirect to the IFD URL and if not you redirect to the internal URL.  This way we only had one Site Map entry and the solution is seamless to the end user.

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.