I couldn't find the answer to the maximum length of the name of a view in SQL Server 2005 online today, although I did find someone's helfpul blog post about the maximum length of a column name in SQL Server 2005, so a quick experiment later using the following code:

CREATE VIEW [vw_1234567890_1234567890_1234567890_1234567890_1234567890_1234567890_1234567890_1234567890_1234567890_1234567890_1234567890_1234567890_1234567890_1234567890_1234567890_1234567890_1234567890_1234567890_1234567890_1234567890_1234567890_1234567890_1234567890_1234567890_1234567890_1234567890_1234567890]
AS
SELECT * FROM Address

Resulted in:
Msg 103, Level 15, State 4, Line 1
The identifier that starts with'vw_1234567890.1234567890.1234567890.1234567890.1234567890.1234567890.1234567890.1234567890.1234567890.1234567890.1234567890.1234' is too long. Maximum length is 128.

Another experiment, this time for table creation

CREATE TABLE [1234567890_1234567890_1234567890_1234567890_1234567890_1234567890_1234567890_1234567890_1234567890_1234567890_1234567890_1234567890_1234567890_1234567890_1234567890_1234567890_1234567890_1234567890_1234567890_1234567890_1234567890_1234567890_1234567890_1234567890_1234567890_1234567890_1234567890]
(
  [Id] [int] NOT NULL,
  [Line1] [varchar](50) NOT NULL,
  [Line2] [varchar](50) NULL,
  [City] [varchar](50) NOT NULL,
  CONSTRAINT [PK_Address] PRIMARY KEY CLUSTERED 
  ([Id] ASC) 
)ON [PRIMARY]

Resulted in:
Msg 103, Level 15, State 4, Line 1
The identifier that starts with '1234567890_1234567890_1234567890_1234567890_1234567890_1234567890_1234567890_1234567890_1234567890_1234567890_1234567890_1234567' is too long. Maximum length is 128.

I have a stored procedure which returns me a list of items, and I want the directional order of them to change based on a parameter. I didn't want to resort to dynamic SQL, so I had a bit of a hunt around and found the excellent blog post Dynamic/Conditional Order By Clause in SQL Server/T-SQL.  Here's my result:

CREATE Procedure [dbo].[mp_GetItems]
(
 @OldestFirst BIT
)
AS
SELECT 
  Item.ID, Item.Description
FROM 
Items
ORDER BY 
  CASE WHEN @OldestFirst = 1 THEN CreatedDate END ASC,
  CASE WHEN @OldestFirst = 0 THEN CreatedDate END DESC

GO

which works a treat. The key is, as mentioned in the blog post

Ascending and Descending actions need to be grouped into separate CASE statements, separated with a comma

My BrightonBloggers site offers an aggregated display of the most recent 50 posts spotted. It uses some php to consume an RSS file which is produced via some yahoo pipes manipulation which takes the OPML file and retrieves RSS from each listed site. Someone reported an error with this yesterday, and after a bit of research it turned out that the yahoo pipes part was timing out. My best guess was that one, or more, of the feeds listed in the OPML file were dead. A quick hunt around google failed to find me anything to do a dead link check from an OPML file, so I quickly cobbled a partial solution - a PHP script to get the RSS links and output to HTML, and then I made use of the firefox add-on LinkChecker to determine which were live or not.

A sample line from my BrightonBloggers OPML file is:

<outline type="rss" text="Jane Dallaway" htmlUrl="http://jane.dallaway.com" title="Jane Dallaway" xmlUrl="http://jane.dallaway.com/rss.xml"/>

After running the dead link checker through firefox, and then pruning or fixing dead links, the aggregated feed is working again

I needed to create a comma separated list from a series of values in a column in the database the other day, and being mindful of the affect of scalar UDFs on the query plan (after attending SQLBits VI) I looked around for an alternative method to my previous favourite using COALESCE.  A couple of blog comments mentioned using STUFF and FOR XML PATH to do this, and after a bit more research I found some examples from Kodyaz Development Resources which seemed to do the trick 

SELECT 
  STUFF(
    (
      SELECT ',' + <ColumnName>
      FROM <TableName>
      FOR XML PATH('')
    ), 1, 1, '') as CommaSeparatedList

Just a minor update to the existing macro, to determine if the current day is Friday, and to action the item for Monday rather than tomorrow

Code in full:
Sub Toodledo()
'Forward the selected email to Toodledo, updating the subject with the appropriate shortcuts and move to appropriate folder
On Error Resume Next

  Dim objMail As Outlook.MailItem
  Dim objItem As Outlook.MailItem

        Set objItem = GetCurrentItem()
  Set objMail = objItem.Forward

    Dim strWhenToAction
  objMail.To = "" 'my toodledo email address
  If (DatePart("w", Now) = vbFriday) Then
    strWhenToAction = "monday"
  Else
    strWhenToAction = "tomorrow"
  End If

    objMail.Subject = "Respond to " + objMail.Subject + " @@work #" + strWhenToAction + " *Actioned" 'Prefix with Respond to, and append context of @work, date of tomorrow, and folder of Actioned
  objMail.Send

    Call MoveMessageToFolder(objItem, "Actions") 'move it to my Actions folder

    'clean up
  Set objMail = Nothing
  Set objItem = Nothing

End Sub

Private Function GetCurrentItem() As Outlook.MailItem
'Taken from code sample provided at http://www.pcreview.co.uk/forums/thread-2798274.php
On Error Resume Next

    Dim objApp As Outlook.Application
  Set objApp = Application

    On Error Resume Next

    Select Case TypeName(objApp.ActiveWindow)
    Case "Explorer"
      Set GetCurrentItem = objApp.ActiveExplorer.Selection.Item(1)
    Case "Inspector"
      Set GetCurrentItem = objApp.ActiveInspector.CurrentItem
    Case Else
  End Select

    'clean up
  Set objApp = Nothing
End Function

Private Sub MoveMessageToFolder(objItem As Outlook.MailItem, ByVal sFolder As String)
'Loosely based on code found at http://verychewy.com/archive/2006/04/12/outlook-macro-to-move-an-email-to-fol...
On Error Resume Next

  Dim objFolder As Outlook.MAPIFolder
  Dim objInbox As Outlook.MAPIFolder
  Dim objNS As Outlook.NameSpace

  Set objNS = Application.GetNamespace("MAPI")
  Set objInbox = objNS.GetDefaultFolder(olFolderInbox)
  Set objFolder = objInbox.Folders(sFolder)

  If objFolder Is Nothing Then
    MsgBox "This folder doesn't exist!", vbOKOnly + vbExclamation, "INVALID FOLDER"
  End If

       objItem.Move objFolder

     'Clean up
  Set objFolder = Nothing
  Set objInbox = Nothing
  Set objNS = Nothing
End Sub

The previous post contains hints on signing macros etc

I've recently been having problems with a sore left wrist (not my mouse hand, my Ctrl+C,Ctrl+V hand) and have been assessing what some of my recurrent activities are.

Madgex have bought an ergonmic keyboard for me to use, and have had the lovely people from Posture People in to assess my workstation/posture (my monitors were too far away, my mouse wasn't in the right place, my seat wasn't supporting me properly and I wasn't sitting correctly (think of a seated tadasana)). My new keyboard comes with the concept of Favourites, i.e. quick launch buttons to start frequently used applications. This is a good idea, and got me thinking about other recurrent activities that I could program either a button, or a macro to do.

One of my regular activities, as I've mentioned before, is forwarding emails to toodledo for adding to my task list for tomorrow. I do this by sending the email to a unique address, and editing the subject line to include:
@@work - i.e. give the task the context of @work
#tomorrow - i.e. give the task a due date of tomorrow
*Actioned - i.e. put the task in the folder named Actioned
I then move the email to a subfolder of my Inbox named Actions

I figured that I could write a macro to do this, so with a bit of help from here and there, I got the following macro working. I've named the project "SendTo", so the macro is called as "SendTo.Toodledo" and I've added a shortcut to my menu bar to this macro. It works on a single email at a time, which suits my process - I try and read the email before deciding whether it needs to be actioned or just filing straight away.

Sub Toodledo()
'Forward the selected email to Toodledo, updating the subject with the appropriate shortcuts and move to appropriate folder
On Error Resume Next

  Dim objMail As Outlook.MailItem
  Dim objItem As Outlook.MailItem

        Set objItem = GetCurrentItem()
  Set objMail = objItem.Forward

    objMail.To = "<toodledo email address goes here>" 'my toodledo email address
  objMail.Subject = "Respond to " + objMail.Subject + " @@work #tomorrow *Actioned" 'Prefix with Respond to, and append context of @work, date of tomorrow, and folder of Actioned
  objMail.Send

    Call MoveMessageToFolder(objItem, "Actions") 'move it to my Actions folder

    'clean up
  Set objMail = Nothing
  Set objItem = Nothing

End Sub

Private Function GetCurrentItem() As Outlook.MailItem
'Taken from code sample provided at http://www.pcreview.co.uk/forums/thread-2798274.php
On Error Resume Next

    Dim objApp As Outlook.Application
  Set objApp = Application

    On Error Resume Next

    Select Case TypeName(objApp.ActiveWindow)
    Case "Explorer"
      Set GetCurrentItem = objApp.ActiveExplorer.Selection.Item(1)
    Case "Inspector"
      Set GetCurrentItem = objApp.ActiveInspector.CurrentItem
    Case Else
  End Select

    'clean up
  Set objApp = Nothing
End Function

Private Sub MoveMessageToFolder(objItem As Outlook.MailItem, ByVal sFolder As String)
'Loosely based on code found at http://verychewy.com/archive/2006/04/12/outlook-macro-to-move-an-email-to-fol...
On Error Resume Next

  Dim objFolder As Outlook.MAPIFolder
  Dim objInbox As Outlook.MAPIFolder
  Dim objNS As Outlook.NameSpace

  Set objNS = Application.GetNamespace("MAPI")
  Set objInbox = objNS.GetDefaultFolder(olFolderInbox)
  Set objFolder = objInbox.Folders(sFolder)

  If objFolder Is Nothing Then
    MsgBox "This folder doesn't exist!", vbOKOnly + vbExclamation, "INVALID FOLDER"
  End If

       objItem.Move objFolder

     'Clean up
  Set objFolder = Nothing
  Set objInbox = Nothing
  Set objNS = Nothing
End Sub

There isn't anything very clever going on here, but having written it, it may just help someone else.

Note: I then had to sign my macro to ensure that this started successfully without warnings on subsequent starts of outlook

The problem:
A table has been created which is a 1 to many relation table, but the business logic has now changed, and this is now a 1 to 1 relationship. So, we need to remove the excess rows to make the data comply with the new rule. In each case, we are going to keep the row with the lowest Child Item Id and delete the others.

The table is defined as:
CREATE TABLE [dbo].[Mapping]
(
  [ParentId] [int] NOT NULL,
  [ChildId] [int] NOT NULL
)

The data is populated as:
INSERT INTO [Mapping] ([ParentId], [ChildId])VALUES (1,1)
INSERT INTO [Mapping] ([ParentId], [ChildId])VALUES (1,3)
INSERT INTO [Mapping] ([ParentId], [ChildId])VALUES (2,1)
INSERT INTO [Mapping] ([ParentId], [ChildId])VALUES (2,4)
INSERT INTO [Mapping] ([ParentId], [ChildId])VALUES (1,5)
INSERT INTO [Mapping] ([ParentId], [ChildId])VALUES (3,1)

At the end of the deletion, the data present should be:
ParentId  ChildId
       1        1
       2        1
       3        1

What I needed to do was get a list of the items to keep, this was pretty simple
SELECT ParentId, MIN(ChildId) ChildId
FROM Mapping
GROUP BY ParentId
which produced a list which matched the data I wanted to keep. Next I wanted to get a list of the items to delete. To do this, I made use of the EXCEPT keyword, and set theory, to return me everything that was left when I'd taken my data to keep away from the total amount of data, or in SQL
SELECT ParentId, ChildId
FROM Mapping
EXCEPT
SELECT ParentId, MIN(ChildId) ChildId
FROM Mapping
GROUP BY ParentId

This produced the data I needed to delete.
Then all I needed to do was DELETE it. What I wanted to do was join the original table to the results of my set and delete them. But every time I've tried to delete based on a join I've had the following error
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near 'm'.
Msg 102, Level 15, State 1, Line 9
Incorrect syntax near 'i'.
and given up. Today, I went and read the documentation, and discovered that it was possible, but I had to specify the tablename immediately after the DELETE keyword, i.e.
DELETE FROM Mapping
FROM Mapping m
INNER JOIN
(SELECT ParentId, ChildId
FROM Mapping
EXCEPT
SELECT ParentId, MIN(ChildId) ChildId
FROM Mapping
GROUP BY ParentId) i
ON m.ParentId = i.ParentId
AND m.ChildId = i.ChildId

That works, and leaves me with the correct data in the table, but looks rather odd...

• produce a SQL script
• run it on all appropriate databases
• display the output

DECLARE @Loop int
DECLARE @DBName varchar(300)
DECLARE @SQL varchar(max)
DECLARE @tableName VARCHAR(255)

SET @Loop = 1
SET @DBName = ''

   WHILE @Loop = 1
BEGIN

   SELECT TOP 1 @DBName = d.Name
   FROM master.sys.databases d
   WHERE d.Name > @DBName
   AND d.database_id not in (1, 2, 3, 4)
   ORDER BY d.Name

      SET @Loop = @@ROWCOUNT

   IF @Loop = 0
      BREAK

   SET @SQL =
      'IF EXISTS (SELECT 1 FROM [' + @DBName + '].sys.objects WHERE [name] = ''ErrorLog'')
            BEGIN
               SELECT ''' + @@SERVERNAME + ''', ''' + @DBName + ''', ErrorMessage, ErrorDate
               FROM [' + @DBName + '].dbo.ErrorLog
            END
            ELSE
            BEGIN
               PRINT ''ErrorLog table does not exist on server: ' + @@SERVERNAME + ' and database: ' + @DBName + '''
            END '

   BEGIN TRY
      EXEC master.dbo.sp_executeSQL @SQL
   END TRY
   BEGIN CATCH
      PRINT 'Error executing ' + @SQL + ' on server: ' + @@SERVERNAME + ' for database: ' + @DBName
   END CATCH
END

So, the challenge this time is all around compression.

Take a string, compress it into a byte[], record the size, decompress it.

You can use any algorithm you chose, but it must be coded by you and not included from an external library

I have defined an Interface ICompress as follows:

public interface ICompress
{
   /// <summary>
   /// Take a string and convert it into a byte[]
   /// </summary>
   /// <param name="source">The string to be converted</param>
   /// <returns>The converted byte[]</returns>
   byte[] Compress(string source);

   /// <summary>
   /// Take the bytearray produced by the call to Compress, and decompress it
   /// </summary>
   /// <param name="compressedData">The data to be decompressed</param>
   /// <returns>The string</returns>
   string DeCompress(byte[] compressedData);
}

I have provided a solution with the interface (Interface), a console app (CodingChallenge3) and an implementation of the interface (Compressor) (which just converts from string to byte[] and back again, no compression) on the development file share.

The source string is provided through the app.config file of the CodingChallenge3 project (the console app).

What do you need to submit?
- A Compressor project that I can plug in to the provided solution
- A couple of paragraphs explaining how your algorithm works

We had 4 solutions submitted, and they were all of a really high standard. I tested them with 6 different input scenarios:
- The original provided sample - the text for "Mary had a little lamb"
- An empty string - which caused a few exceptions to be raised
- A single space
- A string of numbers
- The contents of war and peace
- The contents of the 3 musketeers in French

Each sample was run through each person's solution, and the most compressed result won that round. In the end, one person won 2 rounds (having implemented their own algorithm) and one won 4 rounds (having implemented 3 different methods and selected the one with the highest compression).

This was based around Puzzle 16: Zip me up, Buttercup from the great resource Collection Of Puzzles For Programmers

Previous coding challenges have been based loosely on LTD Puzzle 4: Finding Prime Numbers (which was Madgex coding challenge 1) and LTD Puzzle 3: ASCII Art Shapes (Madgex coding challenge 2).

I'm delighted with how these are going, and the interest and general communication that they generate - lots of discussion, hints of secrecy, and comparisons between each other - and then lots of discussion once the results have been announced, and the code is available for scrutiny. So, I'm now busy thinking up ideas for Madgex Coding Challenge 4 - if anyone has any suggestions or resources for me to use, please pop them in the comments

So, for my table Continent, and my usual SELECT of
SELECT [Name]
FROM Continent
with just a variable declaration and a use of COALESCE and hey presto, we get our required result of Africa, Antarctica, Asia, Australia and Oceania, Europe, North America, South America

The code is now:
DECLARE @List VARCHAR(1000)

SELECT @List = COALESCE(@List + ', ', '') + Name
FROM Continent

SELECT @List

Nice and simple. I like!