Inside the tent....

Winners of the Bejewelled Puzzle

Andrew Clarke — Mon, 03 Nov 2008 17:10:00 GMT

There are three winners:

Timothy Walters - for his second solution with the offset table; He gets a $50 Amazon Voucher.
Ryan Randall – for his first and second solutions; He gets a choice between a license for SQL Prompt and SQL Data Generator
DrLechter – for his first and second solutions; He gets a choice between a license for SQL Prompt and SQL Data Generator

Timothy Walters's solution is very simple and elegant. Almost all of the logic has been implemented in the lookup table and that makes the solution portable and flexible.

In addition, his solution can be easiely modified in order to show the directions of the moves by adding one more column to the offset table.

Here is how it can be done:


--====== Table matches needs to be loaded only once
CREATE TABLE matches(offsetRow1 INT, offsetCol1 INT, offsetRow2 INT, ofsetCol2 INT, directions VARCHAR(20))
-- for horizontal 
INSERT INTO matches VALUES(-1, -1, -1, -2, 'up')
INSERT INTO matches VALUES(-1, -1, -1, 1, 'up')
INSERT INTO matches VALUES(-1, 1, -1, 2, 'up')        
INSERT INTO matches VALUES( 1, -1, 1, -2, 'down')           
INSERT INTO matches VALUES( 1, -1, 1, 1, 'down')
INSERT INTO matches VALUES( 1, 1, 1, 2, 'down')       
INSERT INTO matches VALUES( 0, -2, 0, -3, 'left')     
INSERT INTO matches VALUES( 0, 2, 0, 3, 'right')            
-- for verical
INSERT INTO matches VALUES(-2, -1, -1, -1, 'left')
INSERT INTO matches VALUES(-1, -1, 1, -1, 'left')
INSERT INTO matches VALUES( 1, -1, 2, -1, 'left')
INSERT INTO matches VALUES(-2, 1, -1, 1, 'right')
INSERT INTO matches VALUES(-1, 1, 1, 1, 'right')
INSERT INTO matches VALUES( 1, 1, 2, 1, 'right')
INSERT INTO matches VALUES(-2, 0, -3, 0, 'up')
INSERT INTO matches VALUES( 2, 0, 3, 0, 'down')
 
--==================================================
;WITH CTE
      AS
  (
  SELECT
        [Row] = CAST( [#] AS INT ),
        [Col] = CAST( [Col] AS INT ),
        [Value]
    FROM bejeweled
        UNPIVOT ([Value] FOR [Col] IN ([1],[2],[3],[4],[5],[6],[7],[8],[9])) unpvt
  )
SELECT DISTINCT T.Row, T.Col, T.Value, directions
  FROM CTE T
      JOIN CTE T1
      ON T.Value = T1.Value
      JOIN CTE T2
      ON T.Value = T2.Value
      JOIN matches
      ON (T1.Row - T.Row) = offsetRow1
    AND (T1.Col - T.Col) = offsetCol1
    AND (T2.Row - T.Row) = offsetRow2
    AND (T2.Col - T.Col) = ofsetCol2
  ORDER BY 
 T.Row, T.Col

There is one more person – AmandaS that should be specially mentioned. Her second solution was very similar to Timothy Walters' and Ryan Randall's solutions, but was submitted five days later.

A few more people should be mentioned as well:

Saharafrog – interesting solution
Doug Stewart
Steve Munson - his solution shows the coordinates of the cells that should be moved and the coordinates of destination cells
RBarry Young
Dennis A
SkyBeaver

Litigation, and the 'Production' of Documents.

Andrew Clarke — Fri, 24 Oct 2008 17:59:00 GMT

Several recent events have been unsettling for many IT Managers. The recent collapse of banks has led to FBI investigations in the States, and groups of shareholders in the both the States and the UK have threatened litigation. At the time I write, there is evidence of criminal activity but not enough for prosecutions. There have also been price-fixing investigations by the UK's Office of Fair Trading.

All these investigations have required the 'production' of documents. I've taken part in several of these, and they can be extraordinarily frustrating and time-consuming, even when there is no question of a cover-up. I've also had to find data for MI5 as part of terrorist investigations. It is, of course, the duty of IT management to ensure that all electronically-held data is easy to find and conforms with the company's 'Retention Policy'.

Retention Policy?

In businesses, there is a huge amount of electronic data kept. This is mostly done in case of litigation, but corporate lawyers are generally reluctant to give clear guidance about this. When litigators seek 'production' of documents from an opponent, as they have every right to do, the confusion and duplication in document archives can make life very difficult for the legal department. There have been numerous court cases that have generated serious sanctions when legal counsel and their clients failed to produce documents during this 'discovery' process or worse, pretended that they had been destroyed, or had never existed.

'Discovery' is an expensive business, and the costs of litigation are enormously increased if lawyers cannot get hold of all the relevant documents.

It is not enough just to store everything. In fact, it can make matters worse because of duplication. Everything has to be read by someone who is aware of the legal ramifications of what has been written. To be able to cope, business must specify in their 'Retention Policy' what needs to be retained in terms of business risk, Business continuity, regulatory requirements, legal compliance, and statutory guidelines and requirements. These Policies must clearly state what data and documents can be discarded, and must be consistent across the enterprise.

There may be business needs that may exceed those requirements due to the potential historic, intrinsic or enduring value of the information. If the information has met all of these needs and is no longer considered to be valuable it must be disposed of in a way that protects privacy and confidentiality.

Any data can become the subject of a legal hold, which means that its retention must be indefinite until the legal hold is removed.

Emails cause unique problems because users rely on them for tracking projects and workflow. This makes any arbitrary 'disposal date' impossible. They also must be stored in such a way that they can be scrutinised. There can be no such thing as 'private emails' in the workplace. There have been several investigations where an email has proved to be the key evidence that has led to a criminal prosecution or the resignation of a politician

ILM Applications are worse than useless without a consistent policy for the retention and access of documents. Automating a confusion will just make the confusion faster. The policy has to be enforced and tested to make sure that, in an emergency, all necessary documents can be found.

I hope you never have to be involved in the litigation process. If you do, then the only way to cope is to have an effective policy, a way of disposing of all irrelevant or duplicate data, and the technology to search your data rapidly and effectively.

The LSE TradElect incident: We all lose

Andrew Clarke — Fri, 12 Sep 2008 11:46:00 GMT

The crash of the TradElect trading system on the London, and Johannesburg, Stock Exchange for seven hours was a disaster that probably led to the loss of around £3bn in trading.

The system was based on SQL Server 2000.

High volumes of trading on global exchanges were expected that day, following the news that Fannie Mae and Freddie Mac were being taken over. At 9:15 AM GMT, the Exchange's software failed due to "connectivity issues." It took Six-hours and 45-minutes for the London Exchange, along with the Johannesburg Stock Exchange, once more to start trading.

This incident has put a new question-mark on the LSE’s viability. In terms of cost, it is one of the most spectacular in recent history

The system was built, evidently, built using Windows Server 2003, SQL Server 2000, and a custom .NET application in C# by Microsoft and Accenture. Microsoft described the application as being ‘One hundred per cent reliable on high-volume trading days’ http://www.microsoft.com/uk/getthefacts/lse.mspx. The TradElec runs on more than a 100 HP ProLiant servers in several locations in London.

The system promised to give sub-ten millisecond response times, rather than 7 hour response times.

The LSE describe TradElect in their website as ‘…the Exchange’s new trading system. It brings unprecedented levels of performance, enhanced functionality and new services to our markets whilst maintaining our exemplary record for reliability.’

A spokesman told Reuters on Tuesday. "It was software-related, a coincidence, due to two processes we couldn't have foreseen, We've introduced a fix and we're confident it will not happen again.". She said the fault was not due to high trading volume. "That's a red herring. We were able to eliminate that pretty quickly."

So what was it? Another statement attributed the failure to network software issues between the LSE's Extranex private network (which links the exchange and clients) and the trading gateway to TradElect.. As yet, there is no confirmation of this by Cisco.

The whole incident has had the Linux world rubbing their hands with glee and maintaining that Microsoft technology just wasn’t up to it. Some commentators have even tried to make out that the LSE would have done better with a LAMP-based system with MsSQL.

The whole incident has been a major embarrassment for Microsoft since they made so much of the reliability of the system. Once the amusement of searching Microsoft sites for references to TradElect has waned, though, there comes the thought that, despite the crowing of the Linux gurus, it is quite likely that it had nothing whatsoever to do with the Microsoft technology, and it really was a problem with the software within the network. If this was the case, why hasn’t a statement been made? The delay is bad news because, in the meantime, what damages the reputation of TradElect , also damages confidence in the Windows Server, SQL Server , and NET platform. It was, after all, going to be the ‘benchmark’ application for the platform.

Pasting code into a Blog.

Andrew Clarke — Tue, 15 Jul 2008 13:45:00 GMT

One reason that I often hear for the apparent inability of certain people to blog is that there is no way for Community Server to display code. It isn't actually that hard. The obvious choice is to use Phil Factor's prettifier. It is the reason he wrote it. If you need to show exactly what is in Visual Studio, or SSMS, there is another way.

The problem you have is that Community Server is usually set to only allow FONT tags to enhance text. This makes life difficult, though it ensures that a blogger can't cause too much havoc with surrounding parts of the page.

Paste your code into Word. Make sure that it has pasted it in, using the 'keep source formatting' option. Then, save it as HTML. Alternatively, you can use Outlook. (I set the format of the message to be HTML. I then send the code to myself, right-click the results and select 'view source code'.)

I then take the HTML code into a nice text editor, 'top and tail' it (take out the HTML, BODY tags and the DIVs, leaving just the Ps in place) and do the following substitutions with the objective of taking out the SPANs (no need really as they don't get rendered anyway) and changing the Ps into BRs

FROM	TO	Result
style='font-size:11.0pt;font-family:"Courier New"'		Deleted all occurences
</p>\r\n<p class=MsoNormal	<BR	(this is RegEx) correct the spacing between lines. replace all paragraph tags with line break tabs

You may find that the font has been styled for a different size, depending on the setting in Visual Studio. This means that yo will need to remove style='font-size:10.0pt;font-family:"Courier New"' rather than 11.0pt.

Then create, or edit, the blog. Paste the modified code into the HTML pane (the tab is on the bottom-right of the panel in Community server) Select the code and, in the design pane, make the size of the code 2 rather than 3. Select the code and specify that it should be Courier

All that we're actually doing here is trying to remove all the Word silliness of trying to be all things to all men. All you actually need is the colour information and the non-breaking spaces to do the indentation. The final substitution makes the paragraph tag render as you want it to.

ALTER Function [dbo].[ufsRemoveDelimited]
    (
      @String VARCHAR(MAX),
      @OpeningDelimiter CHAR(1),
      @ClosingDelimiter CHAR(1)
    )
RETURNS VARCHAR(8000)
AS BEGIN
    DECLARE @newString VARCHAR(8000)
    IF @OpeningDelimiter = @ClosingDelimiter
        BEGIN
            RETURN NULL
        END
    IF @OpeningDelimiter + @ClosingDelimiter + @String IS NULL
        BEGIN
            RETURN @String
        END
    SELECT @NewString = ''
    SELECT @newString = @newString + SUBSTRING(@String, number, 1)
    FROM    numbers
    WHERE   number <= LEN(REPLACE(@string, ' ', '|'))
            AND CHARINDEX(@OpeningDelimiter, @string + @OpeningDelimiter,
                          number) < CHARINDEX(@ClosingDelimiter,
                                              @string + ' '
                                              + @closingDelimiter, number)
            AND number <> CHARINDEX(@OpeningDelimiter, @string, number)
    RETURN @NewString
   END

Here is some C#, copied out of Visual Studio 2005

// Marshal.cs
using System;
using System.Runtime.InteropServices;

class PlatformInvokeTest
{
    [DllImport("msvcrt.dll")]
    public static extern int puts(
        [MarshalAs(UnmanagedType.LPStr)]
        string m);
    [DllImport("msvcrt.dll")]
    internal static extern int _flushall();

    public static void Main()
    {
        puts("Hello World!");
        _flushall();
    }
}

Here is a small sample of VB code

Private Sub Button1_Click(ByVal sender As System.Object, _
    ByVal e As System.EventArgs) Handles Button1.Click

    ' Stores the return value.
    Dim RetVal As Integer
    RetVal = MBox(0, "Declare DLL Test", "Windows API MessageBox", _
        MB_ICONQUESTION Or MB_YESNO)

    ' Check the return value.
    If RetVal = IDYES Then
        MsgBox("You chose Yes")
    Else
        MsgBox("You chose No")
    End If
End Sub

RSS feeds on the site

Andrew Clarke — Thu, 17 Apr 2008 09:01:00 GMT

There a number of RSS feeds on the Simple-Talk site. For historical reasons, these feeds are tucked away in a number of different places on the site. Just for convenience, I'm putting them here.

Each blogger actually has his own feed, but I've only listed the three most prolific bloggers.

A Prompt Delivery

Andrew Clarke — Wed, 02 Apr 2008 08:57:00 GMT

I wandered into Red-Gate's offices the other day. I'd been momentarily distracted by the autumn leaves, thoughtfully strewn in the hallway, probably by a visiting Zen Buddhist to remind us of the sacred presence of nature even in the Techno Park. Suddenly I was waylaid by the charming Tanya to take part in a bug-hunt. Somehow, I found myself in a room full of laptop computers and various developers, varying from the apprehensive (SQL Prompt developers) to the smug (ex-SQL Prompt developers).

We were giving SQL Prompt 3.8 RC a quick post-test check-over before the new release candidate version of 3.8 is made available.

The interesting thing about SQL Prompt 3.8 is that it is now using the code reformatter from SQL Refactor to lay out your SQL code nicely. This means that, for the first time, even the users of Query Analyser now are able to reformat code automatically. There are twenty different aspects of the reformatting you can change, in four different sections. It is a very clever piece of code that, for me, doubles the usefulness of SQL Prompt.

There are one or two other features that are less immediately exciting than the reformatting code but would make a big difference to me. One of these is the 'tooltips'-style help for whatever object that you rest the mouse pointer over. Wonderful. I'm an old-stager with IDEs. I like a nice clean window free from clutter, and loathe having to do incessant clicking in the Object Explorer to do stuff. This is why I reach for such old-time practices such as sp_help and sp_helptext. If SQL Prompt can help me keep the clutter away so I can remind myself of the parameters to a routine or the columns of a table without having to scrub what's in the results window, then that's a real bonus. Other new features include Object Definitions, and a new Summary view in the schema panel.

SQL Prompt is one of those product that one can love, but never unconditionally. There are so many different ways that Database Developers work and it is presumptuous to think that it could ever be possible to construct a tool that would satisfy everyone. Perhaps that is why Microsoft have taken so long to provide intellisense for SQL Server developers. It has to work for such a diverse community of users. However, by diversifying into managing layout, and providing all these nice features for even those unfortunates still wrestling with SQL Server 2000, SQL Prompt's future is assured, whatever arrives by way of intellisense in SQL Server 2008.

I found SQL Prompt irritating to use until I adopted the obvious strategy of setting the triggering to manual (this should have been the default). Now you just invoke SQL Prompt just at the point of those senior moments when you forget the name of the table, routine or parameter. Another handy feature is being able to limit the trigger words that invoke SQL Prompt. There is a bit of work to do in order to make it function the way you want but the effort is worth it.

The Red-Gate bug-hunts are good fun for the huunters, but painful for the application developers whose work is being treated the same way as a fox by foxhunters. TallyHo! I sat next to a developer who had the habit of throwing his head back and laughing in an evil gloating way every time he found a bug. I gazed in wonderment at the restraint shown by SQL Prompt's developers whose welcoming smiles only glazed over momentarily in response to the baying pack of bug-hunters, as they pushed firmly into the back of their minds any homicidal instincts towards the gleeful bug-hunters 'on the scent'. There is a certain etiquette in conveying the news to a hard-working developer that there is a tiny flaw in his masterpiece. Perhaps the pretence of amazed surprise that such a thing could possibly exist. Certainly, gloating or reproach is not an option, especially if, one day, the roles will be reversed, and it is your software being torn to shreds.

Fortunately, the software worked surprisingly well for a beta (it is now a release candidate as I write - 2nd April 2008) so we're getting it ahead of schedule. I, for one, will be pestering the developers over what I see as its little quirks and irritations, but I'm fully expecting people on The Red-Gate Product Forumpraising the very features I find irritating. That's all part of the software development process, I suppose

Coram magnificos manifeste scalpere nolis ...

Andrew Clarke — Wed, 27 Feb 2008 10:56:00 GMT

'Coram magnificos manifeste scalpere nolis
Torquendo digitos nares…'

Daniellis Becclesiensis c1200 AD

(meaning: Do not pick your nose in front of managers)

One of the delights of being Simple-Talk’s subeditor, under the watchful eye of Tony (sir), Anna and Simon, is to interact with our team of regular writers. When I use the word ‘Team’, I do so rather loosely, as writers come and go under the influences of the pressures of other work, or life events. By a strange coincidence we see the return of two regulars to the team after a gap of two years.

This issue welcomes back Amirthalingam Prasanna. Prasanna last wrote for us two years ago, and his five articles, with their clear tutorial style, have always been popular. He has returned with an update of his .NET Collection Management 08 December 2005 , called .NET Collection Management with C# We’d particularly like to cover Entity Framework on Simple-Talk, and we’re hoping that Prasanna will continue his output with a Cribsheet on the subject.

Richard Morris, being a professional journalist, does not suffer from the need for pencil-chewing and inspiration that affects most of our authors. He needs no encouragement: his articles are there, on time, every time, and are always stimulating to read. He writes best when he feels a genuine indignation about the subjects of his investigation, and in this article, where he investigates the troubling fact that almost all the British government IT contracts go to a handful of enormous multinational IT companies, the heat is palpable.

Phil Factor is not the most malleable of authors. Articles arrive with a quiet apology like ‘I know you wanted an article on maintenance plans, but this one just popped out.’ The article will be a mock pantomime, an elegy on indexes written in the style of Tennyson, or a treatise on the difficulties of running a project, written in the style of GK Chesterton. I have before me Phil’s translation of Machiavelli, rewritten very slightly and chillingly contemporary. Also Phil’s translation of the mediaeval (twelfth century) latin poem ‘Urbanus Magnus’, by Daniellis Becclesiensis, phrased for the modern office-workers, from which I have taken the quote that starts this Blog.. It means ‘Do not pick your nose in front of managers’. (literally ‘In front of grandees, do not openly evacuate your nostril by twisting your finger’). Other advice from the great work includes ‘if the wife of your manager attempts to seduce you, feign illness, as this is a no-win predicament for you.’. It is the first book of etiquette ever written in Western Europe.

Under Robyn’s restraining influence, Phil’s work is more sober, and more consistent. Robyn is now on maternity leave but we are very fortunate that she continues to produce her articles for us, somehow taming Phil’s creative spirit. ‘The Alerts, Soup to Nuts’ is their first attempt at a ‘Soup to Nuts’ article. It all seemed so easy at first, evidently, but the more they got into the subject, ran tests and so on, the more they realised how little they understood of the subject. A quick rustle through the SQL Server Administration books convinced them that few others did either. The whole subject of tokens seemed to be completely neglected in many of the books on SQL Server Administration. After a long struggle, I think that the team succeeded well.

Arthur Fuller is a stalwart ‘Friend of Red-Gate’ whose first article for us was over two years ago. The Database from Hell is his fifth. This was supposed to be the first draft, but we liked its freshness so much we popped it in without the usual tidying and editing. We apologise in advance to the irate Scotsman who dislikes it when a Red-Gate product get a mention, but it was all part of the story. The illustration we used was adapted from Eric Liberge’s ‘Tonnere Rampant’, with Eric’s permission, and we are delighted with it. He is a great illustrator, whose work deserves to be more widely known.

The Simple-Talk newsletter continues to demand more attention than it should, because it seems to be unreasonably singled out by some versions of Microsoft Outlook as spam. Conspiracy theorists might have a field day with this, but it is very likely an over-zealous algorithm. We’ll be experimenting with the layout in the next few weeks and any opinions you have will be gratefully received.

Thick and Fast

Andrew Clarke — Thu, 07 Feb 2008 13:37:00 GMT

There is a great deal of activity on the Red-Gate forums at the moment. In fact, they are a fascinating read. Often, the threads take off in ways that nobody can really predict. My particular pleasure is in reading the 'off the cuff' remarks such as 'I have servers in NYC, Los Angeles, Pakistan, and India.' or 'I imported roughly 200 servers with over a thousand databases. The servers are distributed throughout the mid-West', or 'The number of servers I will be supporting is jumping to over 200 in the next few months' that give a glimpse of life at the DBA coal-face.

One of the best, if you haven't looked, is the thread that started off on the Refactor Forum. Andras asked 'We support nearly forty options to customize laying out SQL, but is it enough?'. There were 41 responses, and the resulting thread was wildly popular. There are so many different ways of formatting SQL and there seemed to be an advocate for every one.

Here is a subject where programmers will argue for hours. For example, although I agree with the great Joe Celco (SQL Programming Style 2005) about putting reserved words in Upper Case, we had people wanting everything in lower case. You're never going to please everybody, especially not Joe.

So, is there going to be a version that includes all the different layouts? Mike O'Neil wasn't the only one wanting to know. I'm a great fan of Refactor, but I've never quite tweaked the layout exactly how I want. Wthere wasn't great encouragement about a new version despite the interest in formatting. The roadmap only goes to the end of Q1 of this year so lets' hope Q2 for a new version as this is the Red-Gate tool I rely on the most.

There is hope, though as SQL Prompt 3.8 will hopefully be available at the end of Q1. The Pro version evidently incorporates the Layout SQL functionality from SQL Refactor. It is an interesting move, since a lot of people aren't quite sure of the difference between the two products. and one wonders if the new Pro version will prove to be the logical line of development for the products

SQL Prompt 3.7 patch release came out quietly, but the product is improving the whole time. I very quickly learned to set 'triggering' to manual (Cntl+Spacebar) when using SQL Prompt, and find it very handy as it cuts down on the need to keep accessing the Object Browser.

The SQL Data compare forum had a wonderful thread where people were asking for enhancements to the product and Richard kept bobbing up and explaining that they were there already. For me it was rivetting to hear of extreme problems encountered by users such as 'tons of tables with no indexes'.

Over on the Alpha/Beta forums, the buzz around the Data Generator is dying down now as the developers start to assimilate all the feedback, and code the good ideas into the next beta. Now it is the turn of Dan and the SQL Response Beta.

As I write this, it is early days, and I suspect that there will be a huge diversity of opinion on the things that should be monitored in SQL Server to give it a health-check. In the pub, it is always a debate that ends in flushed faces and Beer-glasses getting thrown.

The Red-Gate site now has Blogs. I must admit that I had slightly mixed feelings about this, probably because someone tried to copy the existing blogs across from Simple-Talk and all the comments dropped off like autumn leaves, causing much confusion. However, Neil rallied round and started cross-posting from his wonderful blog on BusinessOfSoftware, However, the true purpose of the Red-Gate Blogs was illustrated best by David Connell in How to write a Generator for SQL Data Generator 1.0 Beta, whereas over here, Lionel showed how well a Red-Gate developer could blog on Simple-Talk with his SQL Puzzle 9