Mitch Wheat - Treat the cause, not the symptoms!

Having not been able to automatically upgrade to the 64bit version of Office (having the 32 bit version of 2010 installed), I installed the 32bit version of Office 2013, so I could try out a few of Excel’s new features (Yeah, I know I should have VM’ed, but I trust those programmers at Microsoft…)

Unfortunately, just like VS2012, Microsoft have broken just about every well established and tried and tested UI design rule: and not for the better. Office 2013 looks as if the folks at Microsoft have confused the concepts of reducing too much ‘Chrome’ with proper visual ‘Demarcation’. Having everything blend into everything else is not a good UI, by any measure. I’m gobsmacked that they could have produced something so poor. It’s simply, bloody awful! Microsoft: The Emperor isn’t wearing any clothes!!

I don’t believe this design ‘strategy’ was done to benefit users. It was done so that Microsoft can seamlessly (supposedly) integrate their mobile and desktop markets. This is not a strategy –>  “We need to shift our emphasis to the mobile market (we sure as hell aren’t selling many versions of Windows 8 for the desktop), lets change our UI to ‘fit’ tablets. Bugger the existing desktop users…”. I’m sorry but that’s a Fail.

BTW, looks like some Microsoft Teams didn’t get the memo about using All CAPS!! (Excel 2013 [Vanilla] Screenshot)

Was making Windows 8 share a similar interface to the phone and tablets done to benefit end users? Seems unlikely. Windows 8 is shaping up to be another Vista (Microsoft have been very shy the last 2 quarters and not released the sales figures for Windows 8). Looking forward to having the Start button back? Windows codenamed ‘Blue’ is coming soon…Hurrah!

Sad thing is, I really want to like Microsoft products. SQL Server is by far the best software I’ve ever used. It is awesome.

So having said all that, I thought I’d give 2013 ago to see if I could subdue my bias and get used to the rather poor UI. First email I try to send, Outlook 2013 crashes. Restart. Try again, it crashes. I’ve used Outlook 2010 every day for over 2 years and haven’t had a single crash.

Uninstalled Office 2013. HELLO Office 2010 !

I’ve used every version of Microsoft Office pretty much since it came out. I currently have the 32 bit version of Office Pro 2010 installed. Attempting to install the 64 bit version of Office 2013 to evaluate Excel’s Power View results in:

Wouldn’t it be nice if it had the option to do this for me and migrate all my settings?!? Especially since I don’t recall there being a 64 bit version of Office 2010 on release (or was it just that it had issues with drivers and add-ins?).

Several users reported a bug running SQLDiagCmd; I’d introduced a silly error in the command line options. [SQLDiagUI was unaffected]

I’ve uploaded an updated version (v1.0.5.13101) which you can download from the links below (or from any of the previous posts):

SQLDiagCmd.zip

SQLDiagUI.zip

No, it’s not Uncle Ernie!

SQL Fiddle is a very useful online tool for evaluating TSQL against a plethora of database engines (like jsFiddle does for javascript / jQuery), written by Jake Feasel

I found JS Fiddle to be a great tool for answering javascript / jQuery questions, but I also found that there was nothing available that offered similar functionality for the SQL questions. So, that was my inspiration to build this site. Basically, I built this site as a tool for developers like me to be more effective in assisting other developers.

It currently supports these database engines (in various versions): MySQL, PostgreSQL, MS SQL Server, Oracle, and SQLite

You’ve heard of a positive match character group [] and a negative match character group [^]. But did you know there is also a Character Class Subtraction? I didn’t. It’s supported in .NET but not in the majority of RegEx flavours.

A character class subtraction expression has the following form:

          [base_group - [excluded_group]]

The square brackets ([]) and hyphen (-) are mandatory. The base_group is a positive or negative character group as described in the Character Class Syntax table. The excluded_group component is another positive or negative character group, or another character class subtraction expression (that is, you can nest character class subtraction expressions).

For example, suppose you have a base group that consists of the character range from "a" through "z". To define the set of characters that consists of the base group except for the character "m", use [a-z-[m]]. To define the set of characters that consists of the base group except for the set of characters "d", "j", and "p", use [a-z-[djp]]. To define the set of characters that consists of the base group except for the character range from "m" through "p", use [a-z-[m-p]].

Using this format, the pattern

^[\w-[v123]]$

can be used in .NET to match all alphanumeric characters (any word character) excluding the letter v and numbers 123. 

The MSDN page for the .NET Regex definitions doesn’t seem to appear high in the search indexes, so bookmarking here for my future reference: Character Classes in Regular Expressions

This is useful for comparing Regex capabilities in different languages: regex flavor comparison chart

Thanks to Todd who reported a bug when connecting with username and password (I messed up the connection string).

I’ve uploaded an updated version (v1.0.4.13057) which you can download from the links below (or from any of the previous posts):

SQLDiagCmd.zip

SQLDiagUI.zip

[Servername will now take a semi-colon separated list of servers to run against, but with the limitation of using the same credentials and diagnostic script.]

If you want a full blown Command Line Parser then there are several good options available:

• Command Line Parser Library
• NDesk.Options
• Powerful and simple command line parsing in C#

[I used the Command Line Parser Library recently in the SQL Diagnostic Runner I wrote.]

If you just want a very basic parser, supporting simple options in the format  /argname:argvalue  then you could use this:

/// <summary>
/// Very basic Command Line Args extracter
/// <para>Parse command line args for args in the following format:</para>
/// <para>    /argname:argvalue /argname:argvalue ...</para>
/// </summary>
public class CommandLineArgs
{
    private const string Pattern = @"\/(?<argname>\w+):(?<argvalue>.+)";
    private readonly Regex _regex = new Regex(Pattern, RegexOptions.IgnoreCase|RegexOptions.Compiled);
    private readonly Dictionary<String, String> _args = new Dictionary<String, String>();

    public CommandLineArgs()
    {
        BuildArgDictionary();
    }

    public string this[string key]
    {
        get { return _args.ContainsKey(key) ? _args[key] : null; }
    }

    public bool ContainsKey(string key)
    {
        return _args.ContainsKey(key);
    }

    private void BuildArgDictionary()
    {
        var args = Environment.GetCommandLineArgs();
        foreach (var match in args.Select(arg => _regex.Match(arg)).Where(m => m.Success))
        {
            try
            {
                _args.Add(match.Groups["argname"].Value, match.Groups["argvalue"].Value);
            }
            // Ignore any duplicate args
            catch (Exception) {}
        }
    }
}

In .NET versions prior to .NET 4.5, the largest allocation for any single object is 2GB.

On 64-bit platforms, in .NET versions 4.5 and greater, it is possible to enable the allocation of arrays that are larger than 2 GB in total size (but NOTE this does not change other limits on object size or array size):

• The maximum number of elements in an array is UInt32MaxValue.

• The maximum index in any single dimension is 2,147,483,591 (0x7FFFFFC7) for byte arrays and arrays of single-byte structures, and 2,146,435,071 (0X7FEFFFFF) for other types.

• The maximum size for strings and other non-array objects is unchanged.

The default setting is not enabled.

You can enable this feature by using the gcAllowVeryLargeObjects element in your application configuration file:

<configuration>
  <runtime>
    <gcAllowVeryLargeObjects enabled="true" />
  </runtime>
</configuration>

Before enabling this feature, ensure that your application does not include unsafe code that assumes that all arrays are smaller than 2 GB in size. For example, unsafe code that uses arrays as buffers might be susceptible to buffer overruns if it is written on the assumption that arrays will not exceed 2 GB.

Ref.

Bookmarking for future reference: Guide to recognizing popular timestamp formats

David Vogelaar (and others) kindly reported a bug: I wasn’t converting invalid filename characters when using an SQL Server instance name for the auto-generated results filename. This has been fixed. You can download version 1.0.2 from the previous download links or the ones below.

SQLDiagCmd.zip

SQLDiagUI.zip

There is a known issue:   

The results file is generated OK but sometimes when you open it in Excel a seemingly ‘nasty’ message is shown:

"Excel found unreadable content in ‘???.xlsx’. Do you want to recover the contents of this workbook?"

The file will open OK if you chose to recover: simply accept the prompts and save over the original. I will fix as soon as I can.

I had been thinking of collating the differences between temp tables and table variables and posting it, but Martin Smith has already written a great summary over at DBA StackExchange:

What's the difference between a temp table and table variable in SQL Server?

It’s broken up into the following categories:

Glenn Berry mentioned the runner for his diagnostic scripts I wrote a few weeks ago. He also mentioned it would be nice to have a UI version. I had already written a basic UI when I initially released the command line version. So here it is, warts and all! (along with a minor update to the command line version)

SQLDiagUI.zip

SQLDiagCmd.zip

Comments and feedback welcome.

One of the things I think StackOverflow has got wrong is hiding, closed, highly useful questions that are deemed in some way not to ‘fit’ the site’s philosophy (whatever that might be). If your rep is higher than 10K, you can view these hidden closed questions. The site has bigger problems such as the increasing amount of very, very poor quality questions that amount to nothing more than “I can’t be bothered doing/looking up X. Please do X for me”.

Here’s an example: List of freely available programming-books

Can’t see it? I’d obviously prefer to link to the entire question and answers, but assuming you can’t see it, here’s an excerpt from the answer begun by George Stocker (who ironically is one of the people who closed it), and then contributed to by many people as a community wiki:

Meta-Lists

• 25 Free Computer Science Ebooks
• Book Training - On Video
• Cheat Sheets (Free)
• CodePlex List of Free E-Books
• Free Tech Books
• How to Design Programs: An Introduction to Computing and Programming
• Microsoft Press: Free E-Books
• MindView Inc
• O'Reilly's Open Books Project
• Sofware Program Managers Network
• TechBooksForFree.com
• Theassayer.org
• Wikibooks: Programming

Language Agnostic

• 97 Things Every Programmer Should Know
• Algorithms and Data-Structures (PDF)
• Algorithms (draft)
• The Architecture of Open Source Applications
• The Art of Unix Programming
• Best Kept Secrets of Peer Code Review
• Binary Trees
• The Cathedral and the Bazaar
• Clever Algorithms
• Communicating Sequential Processes (PDF) - Tony Hoare
• Compiler Construction (PDF)
• Computer Musings (lectures by Donald Knuth)
• Data Structures and Algorithms: Annotated Reference with Examples
• Database Fundamentals (PDF)
• Data-Intensive Text Processing with MapReduce (PDF)
• The Definitive Guide to Building Code Quality
• Designing Interfaces by Jennifer Tidwell
• Digital Signal Processing For Engineers and Scientists
• Domain Driven Design Quickly
• Don't Just Roll the Dice
• Essentials of Metaheuristics by Sean Luke
• Essential Skills for Agile Development
• A Field Guide To Genetic Programming
• Flow based Programming
• Foundations of Computer Science - Al Aho and Jeff Ullman
• Foundations of Programming
• Getting Real
• Getting started with Open source development (PDF)
• The Great Tree List Recursion Problem
• Guide to the Software Engineering Body of Knowledge
• How Computers Work
• How to Design Programs
• How to Think Like a Computer Scientist
• How to Write Parallel Programs
• How to write Unmaintainable Code
• I Am a Bug
• An Introduction to the Theory of Computation
• Introduction to Functional Programming (class lectures and slides)
• Introduction to Information Retrieval
• Is Parallel Programming Hard, And, If So, What Can You Do About It?
• Learn to Program
• Let's Build a Compiler
• Linkers and loaders
• Linked List Basics
• Linked List Problems
• Mathematical Logic - an Introduction (PDF)
• Matters Computational
• Mining of Massive Datasets
• NASA Manager Handbook for Software Development (PDF)
• NASA Software Measurement Handbook
• Object-Oriented Reengineering Patterns
• Online Course Materials
• OO Design
• Operating Systems and Middleware (PDF and LaTeX)
• Patterns and Practices: Application Architecture Guide 2.0
• Patterns of Software: Tales from the Software Community (PDF)
• Planning Algorithms
• PNG: The Definitive Guide
• Pointers And Memory
• Producing Open Source Software
• Programming Languages: Application and Interpretation
• Programming Methodology
• Programming Pearls
• Project Oberon (PDF)
• Seamless Object-Oriented Software Architecture
• Security Engineering
• Structure and Interpretation of Computer Programs
• Summary of the GoF Design Patterns
• The Little Book of Semaphores
• The TCP/IP Guide
• Think Stats: Probability and Statistics for Programmers (PDF, code written in Python)
• Type Theory and Functional Programming
• Understanding IP Addressing: Everything you ever wanted to know (PDF)

NET (C# / VB / Visual Studio)

• C# Essentials
• C# Programming - Wikibook
• C# Yellow Book (intro to programming)
• Charles Petzold's .NET Book Zero
• Data Structures and Algorithms with Object-Oriented Design Patterns in C#
• Entity Framework
• Moving to Microsoft Visual Studio 2010
• Nemerle
• Programmer's Heaven C# School Book (covers C# 1.0 and 2.0)
• Threading in C#
• Visual Basic Essentials
• Visual Studio Tips and Tricks (VS 2003-2005 only)

SQL (implementation agnostic)

• Developing Time-Oriented Database Applications in SQL
• Use The Index, Luke!: A Guide To SQL Database Performance
• Learn SQL The Hard Way

I’ve always had a good laugh at Oracle for having a 30 character limit on table/column/index names (and probably other objects I don’t know about)

Mentioned here on StackOverflow:

“Not just millions of lines of DBA written code, but plenty of oracle internal code no doubt too. This topic came up in a session with Steven Feuerstein and he said he didn't think they would ever change it.”

“They couldn't exactly trumpet it as a new feature, either... they'd spend a lot of time extending the limit, and then announce "you can now use names longer than 30 characters!". They'd be the laughing stock”

While writing SQLDiagCmd (a runner for Glenn Berry’s SQL Server diagnostic scripts), I re-discovered that Excel 2010 still has a limit of 31 characters for Worksheet names (and several weird bits of behaviour relating to that limit). Really?!? Why would anyone want more than 31 characters for a work sheet name? It is 2013 right, not 1970?

Add that to the fact that worksheet names have to be unique (I understand the need for that), and Voila! unnecessary code to guarantee uniqueness with 31 characters! Someone please tell me there’s a way to override this ludicrous limit…

I wasn’t aware of the handy TextFieldParser class, that parses a text file in a reader oriented way:

The TextFieldParser object provides methods and properties for parsing structured text files. Parsing a text file with the TextFieldParser is similar to iterating over a text file, while the ReadFields method to extract fields of text is similar to splitting the strings.

Maybe because it’s in the Microsoft.VisualBasic.FileIO namespace… How to: Read From Comma-Delimited Text Files in Visual Basic

Sure, you can write one yourself, but it’s handy for quick spikes:

using (var reader = new TextFieldParser(@"c:\logs\bigfile"))
{
    reader.TextFieldType = FieldType.Delimited;
    reader.Delimiters = new [] { Constants.vbTab };

    while (!reader.EndOfData)
    {
        try
        {
            // If ReadFields encounters blank lines, they are skipped and next non-blank line is returned.
            string[] currentRow = reader.ReadFields();

            // Process row....
            foreach (string currentField in currentRow)
            {

            }

        }
        catch (MalformedLineException ex)
        {
            // Handle malformed line....
        }
    }
}