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

After almost 2 months, I sat down with Dawn, my NVQ assessor again yesterday.  I'd had to postpone my planned meeting as I just wasn't finding the time to prepare the work necessary for unit B6 - Provide leadership in your area of responsibility during June so I ended up doing a few bits and pieces on holiday to have at least enough to show willing for this unit.  I'd emailed the work through prior to our meeting and was relieved to find out that I wasn't far off from having enough to close this module off as well.  So, only 2 to go.  Both are optional units, and after a lot of discussion the next one I'm going to tackle is  Unit D7 - Provide learning opportunities for colleagues which will be another personal statement covering both what I currently do within the Madgex environment, but also what I'd like to do given total free reign.  Learning has always been something I've been interested in, both from a personal perspective, but also from a team perspective so I'm really excited by this unit.

Whilst we were chatting, Dawn mentioned that the funding for this NVQ is part of the budget cuts from the new government, meaning that there won't be a new intake of managers using the Train to Gain scheme.  What a shame, but what a great opportunity I've had.

Following on from my post the other week about Photographs that changed the world, a petapixel tweet yesterday made me aware that the nurse featured in V-J Day in Times Square, one of the photos on the 13 photographs that changed the world article, had died at age 91 at he home in LA.

The Alfred Eisentstaedt photograph is copyrighted and is now the property of the Getty Museum, so I'll have to make do with posting a photograph taking at the same time by US Navy photo journalist Victor Jorgensen which is entitled "Kissing the war goodbye".

The people featured in this iconic photograph weren't known at the time - it wasn't until 1970 that Edith Shain, the lady who has just died, wrote to the photographer, Alfred Eisentstaedt, and claimed to be the nurse.

A quick search on flickr shows just how many people have re-enacted this image.

Today I had cause to search a text field in a SQL Server 2005 database for the phrase %3 (I had some data being url encoded incorrectly) and this wasn't something I'd tried to do before so I had to find out how to escape the % sign.  A quick google and a re-read of the LIKE syntax, and I discovered that to escape the % I had to do something like

WHERE [Value] LIKE '%!%3%' ESCAPE '!'

After talking this through with a colleague, he asked me to try some other escape characters, and here's what we found out:

Attempting to use a string as an escape phrase, i.e.

WHERE [Value] like '%WORD%1%' ESCAPE 'WORD'

doesn't work, and fails with an error of

Msg 506, Level 16, State 1, Line 1

The invalid escape character "WORD" was specified in a LIKE predicate.

Attempting to use % as the escape character, gets it quite confused 

WHERE [Value] like '%%%1%' ESCAPE '%'

returns 0 rows, but no errors

Attempting to use ' as an escape character, which needs to be escaped itself 

WHERE [Value] like '%''%1%' ESCAPE ''''

returns the correct rows