Ensuring a SQL Server column copies as text to an Excel column

The problem arises when you have a column with character strings that look like numbers. Looking like a number isn’t a problem in and of itself, unless the value starts with the character “0”. Excel will try to treat the column’s values as a number, and therefore eliminate any leading 0s.

The trick is to set up the column properties in the spreadsheet before you copy the values into it.

  1. Open a new Excel spreadsheet
  2. Find the column that corresponds to the SQL column with string data that looks like number data
  3. Right-click on the column and select “Format Cells…”
  4. On the Number tab, select Text as the Category
  5. Click OK
  6. Back in SSMS, select the top-right corner of the grid, then right-click it
  7. Select “Copy with Headers”
  8. In Excel, paste the data into the spreadsheet

Now the column should properly maintain any leading 0s on the strings-that-look-like-numbers.

(I rarely need to do this, but when I do, I inevitably end up searching online for the solution. May as well document it here so that I can find it when I need it.)

Authentication fails with access_denied error while using Microsoft.Owin.Security.Google 3.0.0

Just a quick tip:

If you’re trying to use Google Oauth 2.0 to authenticate users in your MVC 5 application, start with this tutorial:

Code! MVC 5 App with Facebook, Twitter, LinkedIn and Google OAuth2 Sign-on

When you try to log in using a Google account, you’ll get an access_denied error back from Google. To resolve the issue, read this blog post:

Changes to Google OAuth 2.0 and updates in Google middleware for 3.0.0 RC release

In a nutshell, you need to enable Google+ API access for your application from the Google Developers Console.

More info in this Katana Project issue.

Enumerating HttpModules: MVC Edition

About 6 years ago, I wrote a post about Enumerating HttpModules in ASP.NET. On my current project, I once again needed to view the loaded HttpModules, but this time in ASP.NET MVC. The code is very similar; it just has some MVC-isms and has been LINQ-ified now.

Here is the relevant code:

public ActionResult Index()
    // Get the modules for the current application.
    var modules = HttpContext.ApplicationInstance.Modules;

    // Convert the collection of keys to an IEnumerable<string>, and then use the keys to
    //  index into the list of modules to create a dictionary.
    //  * Key = module key name
    //  * Value = module full type name
    var loadedModules = modules
        .Select(moduleKey => new { Key = moduleKey, Module = modules[moduleKey] })
        .ToDictionary(m => m.Key, m => m.Module.GetType().FullName);

    return View(new HomeIndexModel(loadedModules));

And here is what the output would look like in the default ASP.NET MVC Bootstrap template:

Loaded HttpModules

The sample code is available on GitHub: EnumerateMvcHttpModules

Happy enumerating!

CSC : warning CS1685: The predefined type ‘System.Runtime.CompilerServices.ExtensionAttribute’ is defined in multiple assemblies in the global alias

In one of my ASP.NET MVC 5 projects that targets .NET 4.5.1, I noticed that I was getting a new compiler warning at build time:

CSC : warning CS1685: The predefined type 'System.Runtime.CompilerServices.ExtensionAttribute' is defined in multiple assemblies in the global alias

On a hunch, I fired up JetBrains dotPeek, loaded all of the assemblies in my bin directory, and did a Type search (Navigate -> Go to Everything / Type...). It turns out that Json.NET actually implements its own version of ExtensionAttribute:

// Type: System.Runtime.CompilerServices.ExtensionAttribute
// Assembly: Newtonsoft.Json, Version=, Culture=neutral, PublicKeyToken=30ad4fe6b2a6aeed
// MVID: 2CCEAD7E-60D5-4E86-87A2-3819FCE6C567
// Assembly location: (redacted)\bin\Newtonsoft.Json.dll

using System;

namespace System.Runtime.CompilerServices
  /// <remarks>
  /// This attribute allows us to define extension methods without
  ///             requiring .NET Framework 3.5. For more information, see the section,
  ///             <a href="http://msdn.microsoft.com/en-us/magazine/cc163317.aspx#S7">Extension Methods in .NET Framework 2.0 Apps</a>,
  ///             of <a href="http://msdn.microsoft.com/en-us/magazine/cc163317.aspx">Basic Instincts: Extension Methods</a>
  ///             column in <a href="http://msdn.microsoft.com/msdnmag/">MSDN Magazine</a>,
  ///             issue <a href="http://msdn.microsoft.com/en-us/magazine/cc135410.aspx">Nov 2007</a>.
  /// </remarks>
  [AttributeUsage(AttributeTargets.Assembly | AttributeTargets.Class | AttributeTargets.Method)]
  internal sealed class ExtensionAttribute : Attribute

Back in my web project, I looked at the properties for Newtonsoft.Json and noticed that the Runtime Version was set to v2.0.something. After reading through this Json.NET discussion thread on the matter, I decided to reinstall the NuGet package:

PM> Update-Package Newtonsoft.Json -Reinstall

And voilĂ ! Newtonsoft.Json‘s properties once again showed that Runtime Version points to v4.0.30319, and the compiler warning went away. This modified my .csproj file, and diffing it immediately revealed the source of the problem. Newtonsoft.Json‘s hint path had somehow been set to this:


Instead of this:


Hope this helps.

The File Nesting extension in Visual Studio 2013 Ultimate

If you don’t know about the File Nesting extension from Visual Studio, watch the demo video on Channel9. It’s a very nice productivity tool.

After installing the File Nesting extension in Visual Studio 2013 Ultimate, right-clicking and nesting individual files worked, but right-clicking on, e.g., the Scripts folder and selecting File Nesting -> Auto-nest selected items did nothing.

Flummoxed, I had a peek in Tools -> Options, and lo and behold, there is a settings page for File Nesting. By default, all of the rules were disabled. Once I tweaked the options, auto-nesting started working as expected.

Setting these options to true made auto-nest work for me.

Setting these options to true made auto-nest work for me.

Just a little tip that will hopefully save you some headaches.

Using EFProf and MiniProfiler simultaneously in an ASP.NET MVC 5.1, Entity Framework 5 Code-First Project

We’re already using the excellent Entity Framework Profiler (EFProf) to gauge EF performance in our application. However, we wanted more information about the overall application performance, and thus turned to MiniProfiler.

Installing MiniProfiler was easy enough, but upon running the application, I got an error:

Unable to determine the provider name for connection of type ‘HibernatingRhinos.Profiler.Appender.ProfiledDataAccess.ProfiledConnection`1[[System.Data.SqlClient.SqlClientFactory, System.Data, Version=, Culture=neutral, PublicKeyToken=b77a5c561934e089]]’.

Hmm. That’s weird. So I commented out my EFProf initialization code:


And MiniProfiler worked fine.

I then wrote a test application to try to isolate the problem, and got a different error:

System.NotSupportedException: Underlying ProfiledCommand is not cloneable

It turns out that MiniProfiler expects its DbCommand to implement ICloneable:

/// <summary>
/// clone the command, entity framework expects this behaviour.
/// </summary>
/// The .
public ProfiledDbCommand Clone()
{ // EF expects ICloneable
    var tail = _command as ICloneable;
    if (tail == null) throw new NotSupportedException("Underlying " + _command.GetType().Name + " is not cloneable");
    return new ProfiledDbCommand((DbCommand)tail.Clone(), _connection, _profiler);

Unfortunately, dotPeek reveals that EFProf’s ProfiledCommand does not implement ICloneable:

namespace HibernatingRhinos.Profiler.Appender.ProfiledDataAccess
  public class ProfiledCommand : DbCommand
  // ...

Fortunately, there is a workaround: initialize MiniProfiler before initializing EFProf. Or, in source code form, do this in Global.asax:

protected void Application_Start()
    // ...

    MiniProfiler.Settings.SqlFormatter = new StackExchange.Profiling.SqlFormatters.SqlServerFormatter();


Instead of this:

protected void Application_Start()
    // ...


    MiniProfiler.Settings.SqlFormatter = new StackExchange.Profiling.SqlFormatters.SqlServerFormatter();

And EFProf and MiniProfiler will coexist happily together.

(Note that this means EFProf initialization happens in Global.asax instead of in PreApplicationStartMethod installed by the EFProf NuGet package.)

Version information:

  • ASP.NET MVC 5.1.0
  • Entity Framework 5.0.0
  • EFProf Build 2225
  • MiniProfiler 2.1.0