SQL Agent in SQL Server Express with Advanced Services Features

I was recently playing with my Express installation on the personal laptop and was trying to get SQL Agent to run on Windows XP and SQL Server 2008 Express with Advanced Services Features SP2. I set up an account added it to the administrators group, added this account to the instance with the sysadmin role. However, the agent would try to start, but then terminate unexpectedly. I then checked and enabled agent xp

sp_configure 'show advanced options', 1;

GO

RECONFIGURE;

GO

sp_configure 'Agent XPs', 1;

GO

RECONFIGURE

GO

As stated in http://technet.microsoft.com/en-us/library/ms178127.aspx

However the agent still would not start. After searching the event view, specifically  the Windows Application log for errors and found the following

SQLServerAgent could not be started (reason: This installation of SQL Server Agent is disabled.  The edition of SQL Server that installed this service does not support SQL Server Agent.).

Seems that if we want to have automated unattended jobs running, we will have to use power shell and Windows Task Scheduler. I am going to do some browsing and see what I can find out then have an update to this post on a workaround for those of  us who use free edition.

The documentation does state that the SQL Agent is not included, I must have missed this link, check it out

http://msdn.microsoft.com/en-us/library/ms365248.aspx

TSQL thing I didn't know SET and SELECT

It always surprises me how many characteristics in a programming language I take for granted. The things like short-circuit evaluation, handling of Null values, IF then Else loops, etc. I was recently reading my complimentary book on Defensive Database Programming with SQL Server by Alex Kuznetsov, and its chalked full of things to watch out for in code.

One of the more obvious behaviors that I came across is the slight but subtle difference between Set and Select with regards to assigning a value to a variable. I was always operating under the assumption that if a select retrieved a null value that it would assign it to the variable and life is good. Wrong answer, I must have missed that in books online.

-- some code to demonstrate the behavior

DECLARE @MyCounter INT

-- Display its initial value

SELECT @MyCounter AS 'Display its initial value'

SET @Mycounter = 1

-- Display SET assigned value

SELECT @MyCounter AS 'Display SET assigned value'

SELECT @MyCounter=1 WHERE 1=2

-- Display SELECT assigned value

SELECT @MyCounter AS 'Display SELECT assigned value'

SET @MyCounter =(SELECT 1 WHERE 1=2)

SELECT @MyCounter AS 'Display assigned value using SET and SELECT'

So, this was an interesting find and while I have not come across this in the code base I maintain I am sure I have been close a few times to getting this undesired behavior. I tried this with a NULL value and it worked correctly. The take away is we all need to have a fair amount of unit testing. I find it is best when someone else does the testing because I always overlook something, same as when you write a paper, you read it several times but usually miss something. I have not tried reading code out loud, not sure it would make as much sense.