Migrating questionable date strings

I’ve been having a lot of fun with data migration lately /s

Anyway I have an evil source database that has free-form strings that allegedly represent dates. I look at this data and think to myself, this is why we use the corect data types and have validation, but I digress….

This data contains such wonderful dates as ‘N/A’, ‘feb 2009’, ‘2008’, ‘09/28/09’ and ‘19/11/2014’

So we have partial dates, mixed date formats and things that just are not dates, which I’d like to be dates or NULLs after migration.

I’ve crafted a bit of SQL that works in my case to perform the conversions that I need. Because of the varing formats of my inputs TRY_PARSE and TRY_CONVERT have differing levels of success, and to add to this when you feed an empty string into TRY_CONVERT you’ll get back ‘1900-01-01’ which I don’t want.

SELECT [Date] as SourceColumn,
    ISNULL(
        ISNULL(TRY_PARSE([Date] as DATE),
            TRY_PARSE([Date] as DATE USING 'en-GB')
        ),
        CASE WHEN LEN([Date])=0 THEN NULL ELSE TRY_CONVERT(Date, [Date]) END
    ) as OutputData
FROM [source_table]

The code works as TRY_PARSE returns NULL if it can’t successfully extract a date.

Combined with the IFNULL function this allows us to wrap extra parse attempts using other cultures and then using a CASE Statement to ensure I get NULL if the input string is ‘’

Inside the CASE I optionally use TRY_CONVERT which handles the cases where I have inputs like ‘2009’ and ‘feb 2009’ or other text/partial dates.

Here’s a sample output from the query shown above

SourceColumn OutputData
19/11/2000 2000-11-19
04-21-2010 2010-04-21
2010 June 2010-06-01
Dec-01-2010 2010-12-01
feb 2011 2011-02-01
2008 2008-01-01
xxxxxxxx NULL
NULL
6/10 2016-06-10

Hopefully this saves someone else some time in the future, or if you know of a better way of doing this please do let me know.

Advertisements
Posted in Development, SQL | Tagged | Leave a comment

Don’t get burned by Redis ConnectionMultiplexer; A sample wrapper

Every had a latent bug go undetected and then jump up and bite you?
Yeah, not the nicest feeling.

I’ve been working on a pretty interesting project that now makes use of Redis to provide a caching layer as the system uses multiple servers and does some reasonably heavy computation to prepare the data for use in the front end. For reference there are numerous REST calls to get the basic data and then a raft of search queries that are executed (again via REST) to build up a data payload of about 1MB. Anyhow given this complexity we cache this. previously we just used the old school System.Web.HttpRuntime.Cache. While this worked it had a few limitations, most notably ensuring consistency of cached data across multiple servers is all but impossible. So we elected to change out implementation to use a Redis cache server, which we can easily provision in Azure, WIN!

So we implemented a cache wrapper object to abstract away the complexity of connecting to the cache etc. Actually we borrowed the wrapper object we’d implemented on another project…

If you read the How to Use Azure Redis Cache article there is some great guidance in there on how to set up a connect to your cache. There’s a single line in that article that is EXTREMELY important; “The connection to the Azure Redis Cache is managed by the ConnectionMultiplexer class. This class is designed to be shared and reused throughout your client application, and does not need to be created on a per operation basis.” In fact this class MUST be shared in a single instance manner. Now if you’re using an IOC container, such as Unity or Ninject, all you need do is ensure that your IOC container has an instance of your wrapper class to treat as a singleton and then this criteria is met.

Now, the project that we borrowed that wrapper class into isn’t using a IOC container, unlike where it came from. The net result was that we wound up creating a ConnectionMultiplexer instance every time our RedisCache wrapper object was instantiated meaning that we slowly added more and more open client connections to the Redis server. Being that this code was running on IIS the app pool was recycling nightly, as they do, and closing all of those open connections…
So we didn’t notice our problem, until the number of calls into the code that talked to Redis reached a certain level, at which point the Redis server came to a grinding halt with a load metric of 100% 😐

Full credit to the Azure Support team, they have been super responsive and helped me resolve the issue we had with our code. Personally I’d love the Azure team to include a full class listing in that article, or linked from it, that handles the connections properly. But until such time as they do so I’m going to provide one here which they came up with in the thread I work with them.

public class RedisCache : IRepositoryCache
{
  private static ConfigurationOptions _configurationOptions;
  private readonly CachePrefix _prefix;

  public RedisCache(ConfigurationOptions configurationOptions, CachePrefix prefix)
  {
    if (configurationOptions == null) throw new ArgumentNullException("configurationOptions");
    _configurationOptions = configurationOptions;
    _prefix = prefix;
  }


  private static IDatabase Cache
  {
    get
    {
      return Connection.GetDatabase();
    }
  }

  private static readonly Lazy<ConnectionMultiplexer> LazyConnection 
    = new Lazy<ConnectionMultiplexer>(() => ConnectionMultiplexer.Connect(_configurationOptions));

  public static ConnectionMultiplexer Connection
  {
    get
    {
      return LazyConnection.Value;
    }
  }
  public void ClearItem(string key)
   {
     key = _prefix + key;
     if (key == null) throw new ArgumentNullException("key");
     Cache.KeyDelete(key);
   }

  // Other cache access methods ommited for brevity
}

The key things that make this implementation work is that the _configurationOptions member and the wrappers around the ConnectionMultiplexer are static and therefore shared among all instances of this class.

Once I got this version of the code up into production then the number of open connections to the Redis server dropped right off and hasn’t grown out of control since 🙂

Anyway, hopefully this helps someone else avoid making the same mistake we did.

Posted in Azure, Best Practice, Development | 23 Comments

Update the installed certificate for an Identity Provider

If you use ADFS or some form of federated identity in SharePoint eventually you’re likely to need to update the certificate you have installed. This is because SharePoint holds a copy of the public certificate to verify the incoming SAML Claims tokens. Thankfully it’s reasonably painless and requires no downtime for SharePoint.

Here’s a script I’ve used to get this job done quickly and painlessly.

Add-PSSnapIn Microsoft.SharePoint.PowerShell
$cwd = Resolve-path .
$certPath = Join-Path $cwd "NewCert.cer" 
$cert = New-Object System.Security.Cryptography.X509Certificates.X509Certificate2("$certPath") 
Get-SPTrustedRootAuthority "Trusted Root Authority Name" | Set-SPTrustedRootAuthority -Certificate $cert 
Set-SPTrustedIdentityTokenIssuer -Identity "Trusted Token Issuer Name" -ImportTrustCertificate $cert  
Posted in Uncategorized | Leave a comment

Browsing localhost using “Project Spartan” AKA “Microsoft Edge”

I’m using Windows 10 as my primary OS now, maybe that’s a bit nuts but hey 10162 is a lot more stable than some of the early builds, and the driver support is pretty good now too.

I fired up an Angular project I’d been working on and found I couldn’t load the site using Edge. The site loaded as expected in Chrome and IE but wouldn’t work for Edge, after few seconds I saw this lovely error screen.
edge cant load localhost

After some searching I found this thread on TechNet which revealed that we need to add an loopback exemption for Edge, or Spartan, depending on which build of Windows 10 you’re running.

CheckNetIsolation LoopbackExempt -a -n=microsoft.microsoftedge_8wekyb3d8bbwe
CheckNetIsolation LoopbackExempt -a -n=microsoft.windows.spartan_cw5n1h2txyewy

You may also need to open a new tab after adding the exemption in order to successfully browse to you locally hosted website.

What about that package name used in the command used?
Well, take a look under C:\Windows\SystemApps and you can see the full package name listed there. I would imagine that if you wind up building your own apps that require access to localhost then you’ll need to add specific exemptions that use the full name of the app package.

Posted in Mirosoft Edge, Windows 10 | Leave a comment

Page mode and JavaScript

So I ran into a scenario recently where a customer had a jQuery script making some DOM modification which they wanted and everything looked good.

Until you went to edit the properties of some web parts. Unfortunately due to the DOM manipulation that the script was doing it was impossible for a user to edit these web parts.Not all that helpful.

Given that this is on a custom page layout the answer is just to add a EditModePanel with the attribute PageDisplayMode=”Display” surrounding the offending script tag, job done, the script is no longer in the page in design, AKA edit, mode.

But what about when the script still needs to do some changes or is being injected via a script editor web part?

The solution is luckily very simple. SharePoint kindly puts a hidden input field into the page while it’s in design mode.

<input name="MSOLayout_InDesignMode" id="MSOLayout_InDesignMode" type="hidden" value="1"/>

All you need to do is check this with a single line of jQuery and use this to control your logic flow.

if($('#MSOLayout_InDesignMode').val() !== “1”){
    //do display mode only stufff
}

Nice and easy, two simple methods of having scripts which only run in the display mode of your choosing.

Posted in Uncategorized | Leave a comment

SharePoint MVPs do an AMA

Are you a redditor?

If you are start collating some questions for the SharePoint MVP AMA which will be held on October 30 6am (October 29 at 1pm EST according to the post in /r/sharepoint). If you’re not a redditor then just come along and lurk.

There will be a lot of well known SharePoint and Office 365 MVPs participating so this is a great time to ask those burning questions.

See you there 🙂

Posted in Uncategorized | Leave a comment

Why do I need to use SPWeb.AllowUnsafeUpdates?

We have a customer who has a couple of custom feature bound onto a WebTemplate that was giving them grief when they attempted to provision sites from this template via PowerShell but worked just fine when using the web UI.

What the team was experiencing was that during the activation of certain features errors with the message “The security validation for this page is invalid”. Now the fix is simple, set AllowUnsafeUpdates=true during the custom feature activated code.

But why do we need to do this?

First let’s look at what’s happening in the context of making these changes via the web UI. In SharePoint there is a FormDigest control, this control places some security validation information into the page which is included in the POST back to the server. SharePoint uses this information to verify that this request to change the contents of its databases does correspond to a request from a page that was served up by SharePoint.

Now when we attempt to make these changes from custom code that’s getting executed from PowerShell there’s no page, no POST and no form digest information bundled along. So SharePoint attempts to verify the form digest a.k.a “security validation” and in the interests of self protection quite rightly throws an exception. This behaviour of SharePoint wanting it’s changes to come from a web browser can also present via the message “Updates are currently disallowed for GET requests”.

Of course because what we’re trying to do here is a valid use case SharePoint supports disabling these checks via the AllowUnsafeUpdates property on the SPWeb object. Now because setting this property to true opens up potential security risks you shouldn’t just set it to true and leave it that way, just toggle it for while you need to make these changes and flip it back to the way it was.

bool unsafeUpdates = web.AllowUnsafeUpdates;
web.AllowUnsafeUpdates = true;
//make some changes;
web.AllowUnsafeUpdates = unsafeUpdates;

Hopefully this has helped to explain the WHY behind the use of AllowUnsafeUpdates that you’ll often see in custom server side code.

Posted in Uncategorized | 2 Comments