Finding out which tables in a SQL database have no rows

Posted on | by brian | Leave a Comment on Finding out which tables in a SQL database have no rows

How many times have you looked at a production database and found a table that has no rows in it? Then, when questioning the developers, you are told, “Oh, that table was never used.” (That’s slack undertow.) It happens to me often enough, so I wanted to come up with a way to easily find all the tables in a SQL database that have no rows.

One line of SQL (which actually runs three lines of SQL, once for each table in the database) is all you need:


exec sp_MSforeachtable 'declare @count int; select @count = count(*) from ?; if @count = 0 print ''?'';'

Run that, and the output will list the schema and table name of every table in the current database that has no rows.

Hide the “Home” link in the Squirrel theme for WordPress

Posted on | by brian | 1 Comment on Hide the “Home” link in the Squirrel theme for WordPress

This is one of those “I’m going to post this as a note to myself” posts…

I recently found the Squirrel theme for WordPress, which is a good for simple corporate-looking sites. One thing I wanted to do was to remove the “Home” link. Here’s how.

In the theme editor section, edit the file squirrel-functions.php. Find the method squirrel_nav_menu_items. In it, comment out all lines except the last line. The function should now look like this:

function squirrel_nav_menu_items($items) {
	/*
	if (is_home()) {
		$homelink = '<li class="current_page_item">' . '<a href="' . home_url('/') . '">' . __('Home', 'squirrel') . '</a></li>';
	} else {
		$homelink = '<li>' . '<a href="' . home_url('/') . '">' . __('Home', 'squirrel') . '</a></li>';
	}
	$items = $homelink . $items;
	*/
	return $items;
}

That’s it; home link gone. Nice free theme, and nice easy hack.

Comparing the cost of signing and implementing legislation

Posted on | by brian | Leave a Comment on Comparing the cost of signing and implementing legislation

Legislator 1: “Let’s create a new law that creates an emergency phone number for health care emergencies, just like 911!”
Legislator 2: “Awesome, I’ve just drafted it, let’s use ‘011’!”
Legislator 3: “OK, it’s passed!”

Cost for legislators to sign their name to the bill: trivial.
Cost for compliance and implementation: priceless.

Dynamics CRM doesn’t enable search on Address ZIP/Postal Code by default

Posted on | by brian | Leave a Comment on Dynamics CRM doesn’t enable search on Address ZIP/Postal Code by default

In the “oh my, how did that slip through?” category comes this one: in Dynamics CRM 2011, the ZIP/Postal Code field on the Address (CustomerAddress) entity is not searchable by default.

Weirder is that the ZIP/Postal Code fields on the Account, Lead, and Contact entities are searchable by default. Weird only because they use the same underlying table (CustomerAddress).

Keep this in mind when you wonder why you can’t search for additional addresses by ZIP code. Make that ZIP/Postal Code field on the Address entity searchable, and you’re good to go.

Damn the documentation: FtpWebRequest.Timeout default value is NOT Infinite

Posted on | by brian | 3 Comments on Damn the documentation: FtpWebRequest.Timeout default value is NOT Infinite

For the past few weeks, an FTP upload has been failing, rather religiously, with the following .Net error:

System.Net.WebException: The underlying connection was closed: An unexpected error occurred on a receive.

For the FTP upload, I was using the .Net FtpWebRequest class, in a rather simple code snippet:

FtpWebRequest request = (FtpWebRequest)WebRequest.Create(config.Uri);
request.UsePassive = config.UsePassive;
request.Method = WebRequestMethods.Ftp.UploadFile;
request.Credentials = new NetworkCredential(config.UserName, config.Password);

The FTP connection was dropping, but why? The transfer file was rather large (over 50MB), but reading the MSDN documentation for the FtpWebRequest.Timeout property, it was pretty clear that the .Net Framework wasn’t timing out, because:

FtpWebRequest.Timeout Property

Type: System.Int32
An Int32 value that contains the number of milliseconds to wait before a request times out. The default value is Infinite.

I tried active and passive connections; both failed. I checked the firewall; nothing unusual. I tried other external servers; they all failed. What gives?

Finally, exasperated, I tried forcing the timeout:

FtpWebRequest request = (FtpWebRequest)WebRequest.Create(config.Uri);
request.UsePassive = config.UsePassive;
request.Method = WebRequestMethods.Ftp.UploadFile;
request.Credentials = new NetworkCredential(config.UserName, config.Password);
request.Timeout = -1;

Viola! The FTP upload did not fail! Stepping through the code, I found that, contrary to the documentation, the default value for FtpWebReqeust.Timeout is not infinite:

That is right, my friends: contrary to what you read in the documentation, the default value is 100,000 milliseconds, or about 1 minute, 40 seconds.

Lessons learned:

  1. Don’t believe everything you read.
  2. Trust, but verify.
  3. Infinite is not infinite when it is not infinite.

URL Rewriting for user-friendly URLs with Dynamics CRM 2011

Posted on | by brian | 24 Comments on URL Rewriting for user-friendly URLs with Dynamics CRM 2011

Anyone who has attempted to configure Dynamics CRM 2011 with an Internet-Facing Deployment (IFD) knows that it is no trivial task. Where there are blog posts that discuss setting up an IFD, and Microsoft documentation for configuring the IFD, they often assume that ADFS and Dynamics CRM are installed on the same server, and that there is only one Dynamics CRM front-end server. Unfortunately, real-world implementations don’t always follow that.

For example, take the following configuration:

  • a Dynamics CRM front-end server on the internal network, providing services to internal clients
  • a Dynamics CRM front-end server in an Internet-facing zone, providing services to external clients
  • a separate ADFS server accessible to internal and external clients

Dynamics CRM with IFD requires a combination of ADFS relaying party trusts and DNS configuration to get things working. One caveat with IFDs is that the internal and external host names for the Dynamics CRM front-end servers must be different because, externally, the host name includes the CRM organization name. Where, internally, you may have https://icrm.contoso.com/crm, externally you would have https://crm.contoso.com.

Let’s flesh out our sample implementation and requirements:

  • icrm.contoso.com is our internal Dynamics CRM front-end server, accessible only on the internal network
  • ecrm.contoso.com is our external Dynamics CRM front-end server, accessible to our internal network and the public Internet
  • adfs.contoso.com is our ADFS server, accessible to our internal network and the public Internet
  • We have two Dynamics CRM organizations: CRM and CRM-Test.
  • We want our internal and external (public Internet) clients to access CRM using the same URLs: crm.contoso.com and crm-test.contoso.com. In other words, we don’t want the two-URL problem outlined above.

The last bit has nothing to do with Dynamics CRM: it is all done in IIS. Let me explain how. Continue reading →

Fixing ‘State code or status code is invalid’ errors when deleting solutions in Dynamics CRM 2011

Posted on | by brian | 2 Comments on Fixing ‘State code or status code is invalid’ errors when deleting solutions in Dynamics CRM 2011

Working with Dynamics CRM 2011, I started getting an error when attempting to delete a managed solution:

Error deleting a managed solution: State code or status code is invalid
State code is invalid or state code is valid but status code is invalid for a specified state code.

The ErrorDetails.txt file states the following — note the text in bold.

Unhandled Exception: System.ServiceModel.FaultException`1[[Microsoft.Xrm.Sdk.OrganizationServiceFault, Microsoft.Xrm.Sdk, Version=5.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35]]: 1 is not a valid status code for state code SavedQueryState.Inactive on savedquery.Detail:

Error Code -2147187704

CallStack
at Microsoft.Crm.Extensibility.VersionedPluginProxyStepBase.Execute(PipelineExecutionContext context)
at Microsoft.Crm.Extensibility.Pipeline.Execute(PipelineExecutionContext context)
at Microsoft.Crm.Extensibility.MessageProcessor.Execute(PipelineExecutionContext context)
at Microsoft.Crm.Extensibility.InternalMessageDispatcher.Execute(PipelineExecutionContext context)
at Microsoft.Crm.Extensibility.ExternalMessageDispatcher.ExecuteInternal(IInProcessOrganizationServiceFactory serviceFactory, IPlatformMessageDispatcherFactory dispatcherFactory, String messageName, String requestName, Int32 primaryObjectTypeCode, Int32 secondaryObjectTypeCode, ParameterCollection fields, CorrelationToken correlationToken, CallerOriginToken originToken, UserAuth userAuth, Guid callerId, Guid transactionContextId, Int32 invocationSource, Nullable`1 requestId, Version endpointVersion)
at Microsoft.Crm.Extensibility.OrganizationSdkServiceInternal.ExecuteRequest(OrganizationRequest request, CorrelationToken correlationToken, CallerOriginToken callerOriginToken, WebServiceType serviceType)
at Microsoft.Crm.Extensibility.OrganizationSdkServiceInternal.Execute(OrganizationRequest request, CorrelationToken correlationToken, CallerOriginToken callerOriginToken, WebServiceType serviceType)

This error came about after we marked some of the out-of-the-box views as Inactive (like “Accounts: Responded to Campaigns in Last 6 Months”). Looking at the database, the statecode (Status) and statuscode (Status Reason) for these views (in table SavedQueryBase) were both “1”. There was a simple workaround to this: change the statuscode to “2”, using the following SQL.

update savedquerybase
set statuscode = 2
where statuscode = 1
and statecode = 1

That allowed the solution to delete without error, and the views I wanted inactivated were still inactive. Seems a bug in Dynamics CRM 2011, but I can’t say for sure.

Of course, use this as your own risk!

Troubleshooting Windows Authentication in IIS

Posted on | by brian | Leave a Comment on Troubleshooting Windows Authentication in IIS

In a recent deployment of Microsoft Dynamics CRM 2011, users were accessing the application via the server’s host name, e.g. https://crmserver01.company.com. That is rather unfriendly, so we created a new DNS record for crm.company.com — giving users the easier-to-remember https://crm.company.com (and removing a dependency on a server name).

Unfortunately, authenticating to https://crm.company.com didn’t work for Internet Explorer users. IE would prompt for the credentials, but they were never accepted. Authentication did work for other browsers, and all browsers — including IE — were able to authenticate to https://crmserver01.company.com without issue.

This didn’t make sense. The domain name of the server shouldn’t matter (both were in IE’s Local Intranet Zone), nor should the browser version. But the different experience between browsers was all I had to go on, so I did some (network) sniffing.

Using Fiddler2 to monitor the network traffic created by web browsers, I opened Firefox and connected to http://crm.company.com (no SSL, so it’s easier to monitor network traffic). Firefox prompted me for my password, I typed it in, and was taken to the Dynamics CRM “unsupported browser” page. (That lack of support for non-IE browsers is a separate issue, but a fix is supposedly in the works.)

Here’s what Fiddler reported for those requests. I’ve truncated the request/response text to only show what’s relevant.

  1. Request
    GET http://crm.company.com
  2. Response
    HTTP/1.1 401 Unauthorized
    WWW-Authenticate: Negotiate
    WWW-Authenticate: NTLM
  3. Request
    GET http://crm.company.com
    Authorization: NTLM <token>

The responses after that don’t matter; I’m connected. I then did the same steps with Internet Explorer: connected to http://crm.company.com, get prompted for the password, type the password, get prompted again, wash rinse repeat two times, then get the boilerplate “401 Unauthorize” page. Here’s the Fiddler report:

  1. Request
    GET http://crm.company.com
  2. Response
    HTTP/1.1 401 Unauthorized
    WWW-Authenticate: Negotiate
    WWW-Authenticate: NTLM
  3. Request
    GET http://crm.company.com
    Authorization: Negotiate <token>

IIS7 comes with Negotiate (Kerberos) and NTLM providers for Windows AuthenticationNote the difference? Firefox uses NTLM to authenticate; IE uses Negotiate (Kerberos). This was the root of the problem: Kerberos isn’t permitted to authorize users accessing crm.company.com, but it is permitted to authorize users accessing crmserver01.company.com. NTLM doesn’t have that restriction. So, it’ll always work in non-IE (non-Kerberos) browsers; and it will work to the host name URL in IE browsers; but it won’t work to the alternate host name in IE.

How do you fix this? By reading the blog post, Configuring and Troubleshooting NTLM and Kerberos on Windows 7 (Windows Server 2008) and IIS7. Essentially, you tell the server to allow Kerberos to the “other” domain name by running the following command from the console: setspn -A HTTP/crm.company.com crmserver01

Another solution is to configure your web site to only use NTLM authentication, or to give NTLM authentication higher priority than Kerberos. However, this is more a workaround than a fix: the point of IE/Windows is to use Kerberos, not to avoid it. Still, it’s an option if you can’t run the setspn command for some reason.

Once you do either of those, all browsers will work to either domain name, as you would expect, and your users will be happy. 🙂

Setting the entity owner to a team using Scribe Insight and Dynamics CRM 2011

Posted on | by brian | 3 Comments on Setting the entity owner to a team using Scribe Insight and Dynamics CRM 2011

While working on migrating data from a legacy CRM system to Microsoft Dynamics CRM 2011 using Scribe Insight, I decided to set the owner of all migrated entities to a specific team (rather than the default, which is the Dynamics user specified in the Scribe connection). To do this seemingly would require one simple steps: to look up the GUID of the team, and set the value of the Dynamics CRM “ownerid” to that value.

To set the “ownerid” I used the DBLOOKUP command to look up the team by name, and return the teamid: DBLOOKUP( “MyTeamName”, “CRM”, “team”, “name”, “teamid” ). This worked fine, but on its own, Dynamics threw an error, saying that the GUID wasn’t valid.

The problem lies in how Dynamics manages owners: an owner can be a user or a team. The owner of an entity is assumed to be a user unless you change the “owneridtype” field, which is visible in Scribe but is not listed in the Dynamics data dictionary (as far as I could tell). If the owner is a user, the “useridtype” should be 8. If the owner is a team, the “useridtype” should be 9.

After setting the “useridtype” to 9, Dynamics accepted the GUID of my team in the “ownerid” field. Problem solved!

An exercise in frustration: Getting product keys and support using the Microsoft Dynamics CustomerSource web site

Posted on | by brian | 3 Comments on An exercise in frustration: Getting product keys and support using the Microsoft Dynamics CustomerSource web site

“Microsoft Dynamics CustomerSource is an information-packed, password-protected site for customers who use Microsoft Dynamics products.” (Microsoft’s words, not mine.) I decided to use this information-packed, password-protected site to get my recently purchased product keys for Dynamics CRM 2011.

Below is a summary of my experience.

First, I go to the Product & Service summary page on the My Account page.

Then, I click on Registration Keys.

Then, I choose the appropriate version (2011) and upgrade option (No) and get the following message: “The keys you are trying are of Volume license, hence they will not be shown from MBS.” (I have no idea what MBS is.)

Frustrated, I try getting support by clicking the support link on the “Contact Dynamics Operations” page.

Which yields a very unhelpful support page that tells me, “Our apologies…An unexpected error occurred.” At least they are apologetic.

Despairingly, I click the ROC Contact Information at the bottom of the page (I have no idea what “ROC” is) and get a different apologetic error.

Pretty unimpressive, even for Microsoft.