Log et al – Peter Curd

An irreverent peek into the inner rumblings of Peter Curd

May

18

Twitter updates on this blog

By pcurd

Back in 2009 I started including my public twitter feed on this blog, a practice I continued for 2 years or so until early 2012.

Today I have archived all of those posts as although they were a considerable percentage of my total posts they were not truly in keeping with my intentions for this blog and led me towards laziness.

If anyone needs to see my old tweets there are various services around including snapbird.org.

Oct

20

How to perform an SQL “IN” query in Linq

By pcurd

Using Linq to achieve a query that in SQL that uses the “IN” keyword – i.e. to check a value against a range of values – requires use of the .Contains method on a new array of the range of values.

For example, if I need to exclude from reports the following customers:

  1. Internal
  2. Demo
  3. Sample
I would in SQL use something like:
CustomerName NOT IN ('Internal', 'Demo', 'Sample')
In Linq I need something like:
!(new[] {"Internal", "Demo", "Sample"}).Contains(CustomerName))

Appropriately wired into EF or your ORM of choice.

I think this is confusing since it is written backwards to a SQL programmer, so it reads “does this list of things contain the value?” instead of “is the value in this list of things?” as we are used to in SQL.

Jan

28

How to get SQL Server Table Row Counts fast and efficiently

By pcurd

SQL Server Central ran an article this morning about using a system DMV to get table row counts without doing a table scan (so it’s VERY fast and has no performance hit).

I decided to test this on my test server, a virtual machine running on a Dell R900. I ran the queries four times, discounting the first time to be sure all were in memory, and averaging the last three for the following values.

Read more »

Jul

1

The FizzBuzz problem

By pcurd

Amy Kimber posted an article today referring to a post from Nick Telford on twitter:

Will The Real Programmers Please Stand Up? http://retwt.me/1NEBw // another example of how diluted our industry really is (link)

This interests me as I often find job interviews to be very generic and after about four in a row you get to know all the answers and appear much cleverer than you perhaps are.  I like a problem that makes you think and although FizzBuzz is a trivial problem, I wanted to answer it with a little bit of real world thinking.

To quote Amy, the FizzBuzz problem is “The idea is simple, all you have to do is write a program that prints out the numbers 1 to 100, but for multiples of 3, print Fizz instead of the number and for multiples of 5 print Buzz. If the number is a multiple of both, FizzBuzz should be printed.”

In the real world, specs change. Today it’s 3 and 5 and tomorrow it’s 4 and 9 and we need to add an extra one “Bibble” for 11.  I decided to solve the problem with a list of number and word pairs so that changing it would be easy.  .Net provides a nice KeyValuePair generic which I used as Int,String.

Originally I had this implemented using a Dictionary but as FizzBuzz must be implemented the correct way around (not BuzzFizz) I needed to change this to a SortedDictionary to ensure order of execution.

Code is as below:

using System;
using System.Collections.Generic;

namespace FizzBuzz
{
    class Program
    {
        static ICollection<KeyValuePair<int, string>> wordlist;
        static void Main(string[] args)
        {
            wordlist = new SortedDictionary<int, string>();
            wordlist.Add(new KeyValuePair<int, string>(3, "Fizz"));
            wordlist.Add(new KeyValuePair<int, string>(5, "Buzz"));
            PrintFizzBuzz(1, 100);
            Console.ReadKey();
        }

        static void PrintFizzBuzz(int start, int finish)
        {
            for (int i = start; i <= finish; i++)
            {
                Console.WriteLine(CalculateFizzBuzz(i));
            }
        }

        static object CalculateFizzBuzz(int i)
        {
            string CalculateFizzBuzz = "";
            foreach (KeyValuePair<int, string> word in wordlist)
            {
                if (i % word.Key == 0)
                    CalculateFizzBuzz += word.Value;
            }
            if (CalculateFizzBuzz.Length == 0)
                CalculateFizzBuzz = i.ToString();

            return CalculateFizzBuzz;
        }
    }
}

Update 27th February 2012:

Many great answers in the comments below, great to see how different languages can solve this problem. My solution is designed around flexibility and the ability to change the list of “Fizz”es and “Buzz”es which takes away from the brevity possible!

I found this article today from Calvin Bottoms regarding implementing FizzBuzz in Haskell – at 78 characters it’s impressive and his explanation of how it develops is a great read.

Jun

19

SWDevelopments Website launched

By pcurd

For the last few months I have been rebranding my personal projects and side work to “SW Developments” and today I have launched a website for this venture – http://swdevelopments.co.uk.

I will be developing this website and the venture with more details in the coming months.

SW Developments will consider any project and can produce cost effective, modern Content Management System driven websites,  more powerful custom development driven websites or any IT consultancy services for local companies.

Apr

25

Unable to add certain Active Directory users as Windows Logons to SQL Server 2008 on Windows Server 2008 R2

By pcurd

Last week I faced an issue where I was unable to create Windows Logons for SQL Server 2008 from Active Directory users on a Windows Server 2008 R2 server when a Windows Server 2008 server was able to.

The error was a 15401 error which are quite common and usually mean your Server Principal names are wrong, however I had checked this and knew it was not an authentication issue. See my post on Server Principal Names for more details on this. Plus I was able to add other users. The error was “Error 15401 – Windows NT user or group ‘domain\username’ not found”.

There is a Knowledge Base article which describes the problem (with a hotfix) but the symptoms are not the same as the knowledge base so I hope this makes it easier to find. The knowledge base article is KB976494 (Error 1789 when you use the LookupAccountName function on a computer that is running Windows 7 or Windows Server 2008 R2) http://support.microsoft.com/kb/976494.

Reblog this post [with Zemanta]

Apr

22

Access 97: The database engine can’t find Error

By pcurd

When linking to a SQL Server table in Access 97 today I came across this weird error:
“The database engine can’t find TableName. Make sure it is a valid parameter or alias name, that it doesn’t include invalid characters or punctuation, and that the name isn’t too long.”

As the table name was reasonably small, and I had longer table names already linked, I became suspicious.

The problem was indexes, if the total length of the table name plus the length of the name of the longest index is longer than 64 characters, you get that error. Making the index name smaller solved the problem.

Apr

5

DRM and me – The problem with The Settlers 7

By pcurd

This morning I happened to notice on Steam that there is an update for the Settlers 7 out and I thought to myself, wow did I miss the release?

I’m a big fan of the Settlers series and I own all of them from Settlers 1 (which I have on floppy disk somewhere) through to Rise of an Empire, the sixth game in the series, which I own on Steam.

Although there have been shakey moments in the francise, the fifth game The Settlers: Heritage of Kings tried to be too much like Spellforce without capturing the essence of RTS-RPG for example, Settlers games have always been fun to play.

So with much excitement I did a quick search.. nope, Settlers 7 (Paths to a Kingdom) is not available on Steam.. must be one of those regional locks which annoys me, considering The Settlers is made by Blue Byte Software, a German developer. Why does the US get it first? It’s published by Ubisoft – a French publisher!

So I decide to check out Ubisoft’s download page to see what the price is and there I find it. Well, I find the US page anyway. After some more searching I find the UK page – except it’s from GamesPlanet. But whatever, it’s got a Ubisoft logo on it.

£34.99 is a reasonable price for a new game, don’t even get me started on the “electronic copies should be cheaper” debate, so I start to check out what’s in this edition, after all the US copy is the Gold Edition and only £5 more. The feature difference isn’t important but I do notice one thing I’ve been secretly dreading all this time. Ubisoft’s DRM package (Digital Rights Management).

Read more »

Apr

1

Find Query Plans on Microsoft SQL Server

By pcurd

Today I was asked by Luke Smith a question about Query Plans in Microsoft SQL Server and how to return the Cached Query Plans to check they are decent.

I began to think about the DMV (Dynamic Management View) sys.dm_exec_cached_plans and whilst that includes some important information, it doesn’t include the plan itself.  So then I turned to another DMV, sys.dm_exec_query_plan passing it the plan_handle from the first DMV.

Finally, to be useful as a diagnostic tool I wanted the query text so I added in a third DMV sys.dm_exec_sql_text again passing the plan_handle. I then sorted by usecounts which does what it says on the tin and limted to those that are not system procedures, are actually plans, as opposed to records from parse trees or extended stored procedures, and have been used more than 10 times.  This returned a lot of data for me so I limited to the top 100, but this could obviously be tuned to a different environment.

Running this in SQL Server Management Studio has the bonus of being able to click on the XML in the query_plan and view the plan graphically which is definitely better than the XML!
Read more »

Mar

4

Renaming Microsoft SQL Server servers and the effects on SPNs

By pcurd

Last week I saw a post on Simon Sabin’s blog about SQL Server service accounts and SPNs and made a comment about the importance of SPNs when renaming a SQL Server or migrating several servers into .. less than several. I felt this an area worth expanding a little more.

I’ll describe a hypothetical SQL Server infrastructure, changes to be made and how I would resolve the Kerberos issues that would result.

The base infrastructure:

A simple SQL Server environment with two Microsoft SQL Servers – SQLA and SQLB. The dataset on these two servers is different, no databases are shared. The domain for this company is “example” and the DC is “example.com” and so their usernames are formed “example/UserName”.

The new infrastructure:

A new SQL Server is purchased with power enough to run the entire dataset and it is to be called SQLA. The service account is to be a domain user called “sqlservice”.

However, there are a lot of applications that link to SQLB by name and recoding them all is considered too much work. (A classic example is Access which doesn’t make changing the source of tables easy without relinking)

A solution:

Migrate the total dataset to the new server, assign it a name of SQLA and take account of Simon’s SPN advice – i.e. use Network Service or a domain account to run the SQL Service. Use DNS to create a record for “SQLB” pointing to SQLA. If you want to be really fancy, assign the original IP address of SQLB as an additional IP on SQLA.

The result:

Connecting to “SQLA” via NTLM, SQL Database logins (assuming they were migrated too) and Kerberos works fine – as you’d expect. However, connecting to “SQLB” only works for NTLM and SQL Database logins – Kerberos fails along the lines of “Cannot generate SSPI Context”.

The solution:

As you may have guessed from the title of this article, the solution lies with SPNs. When you register for a Kerberos token you are doing so against a server name – and in this case, when talking to “SQLB”, that server name is wrong. So how does one send the correct name? Well in the example above, you can’t. You are stuck sending “SQLB”. The only solution is to make the name not wrong. To do this, we register another SPN against SQLA – effectively allowing it to understand and use the tokens made against “SQLB”.

The syntax is along these lines: (Please note, written from memory so some tweaking may be in order)

SetSPN -a mssqlsrv/SQLB.example.com:1433 example\sqlservice
SetSPN -a mssqlsrv/SQLB.example.com example\sqlservice
SetSPN -a host/SQLB.example.com
SetSPN -a host/SQLB

More details on SetSPN can be found on MSDN at http://msdn.microsoft.com/en-us/library/ms178119.aspx (Registering Kerberos Service Principle Names by Using Http.sys).