How To Schedule Sending an Email with GMail

If you need to send scheduled emails from GMail  or Goolge Apps, use Boomerang. It's mega easy and you are able to send 10 a month on their free subscription. There's a mobile version of it you can access here. I haven't used the mobile version yet, but almost certainly will be.

MSSQL: Create an Audit Structure in SQL Server

I was looking at creating an audit trail for data changes in a SQL Server database. There are many mechanisms for doing this and I decided on having a duplicate table to store the audit trail in. So for example:

Base Table: User.Person
    PersonID int
    Name varchar(100)

Audit Table: Audit.Person
    AuditID int IDENTITY(1,1)
    Action char(1)
    Date datetime
    PersonID int
    Name varchar(100)

So the idea is that for each action that occurs (INSERT, UPDATE, DELETE), the data as it was prior to the action is stored into the associated audit table. Obviously in the case of an INSERT the new ID is stored as there was no data prior to the action.

Each base table has a matching TRIGGER on it. This trigger looks like this:

CREATE TRIGGER [User].[TRG_Person_AuditHandle]
   ON  [User].[Person]
   AFTER INSERT, DELETE, UPDATE
AS 
BEGIN
	IF (SELECT COUNT(1) FROM deleted) > 0 AND (SELECT COUNT(1) FROM inserted) > 0
		INSERT INTO [Audit].[Person] ([Action], [PersonID], [Name])
		SELECT 'U', [PersonID], [Name] FROM deleted
	ELSE IF (SELECT COUNT(1) FROM inserted) > 0
		INSERT INTO [Audit].[Person] ([Action], [PersonID])
		SELECT 'I', [PersonID] FROM inserted
	ELSE IF (SELECT COUNT(1) FROM deleted) > 0
		INSERT INTO [Audit].[Person] ([Action], [PersonID], [Name])
		SELECT 'D', [PersonID], [Name] FROM deleted
END

This now gives a full audit trail to the Person table.

Here's a wicked script to create all the audit tables and the triggers for the base tables:

/*
	Creates the audit data structure for all requried tables
	+ Creates the audit table to hold the changes in
	+ Creates the trigger on the base table to add to the audit table
*/
DECLARE @SQL_DROP_AUDIT_TABLE varchar(MAX),
		@SQL_CREATE_AUDIT_TABLE varchar(MAX),
		@SQL_DROP_AUDIT_TRIGGER varchar(MAX),
		@SQL_CREATE_AUDIT_TRIGGER varchar(MAX)

SET @SQL_DROP_AUDIT_TABLE =
'IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N''[DF_#TABLE_NAME#_Date]'') AND type = ''D'')
	ALTER TABLE [Audit].[#TABLE_NAME#] DROP CONSTRAINT [DF_#TABLE_NAME#_Date]
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''[Audit].[#TABLE_NAME#]'') AND type in (N''U''))
	DROP TABLE [Audit].[#TABLE_NAME#]
GO'

SET @SQL_CREATE_AUDIT_TABLE = 
'CREATE TABLE [Audit].[#TABLE_NAME#](
	[AuditID] int IDENTITY(1,1) NOT NULL,
	[Action] char(1) NOT NULL,
	[Date] datetime NOT NULL,
	#COLUMNS#
 CONSTRAINT [PK_Audit_#TABLE_NAME#] PRIMARY KEY CLUSTERED 
(
	[AuditID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [Audit].[#TABLE_NAME#] ADD  CONSTRAINT [DF_Audit_#TABLE_NAME#_Date]  DEFAULT (getdate()) FOR [Date]
GO'

SET @SQL_DROP_AUDIT_TRIGGER = 
'IF  EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N''[#SCHEMA_NAME#].[TRG_#TABLE_NAME#_AuditHandle]''))
	DROP TRIGGER [#SCHEMA_NAME#].[TRG_#TABLE_NAME#_AuditHandle]
GO'

SET @SQL_CREATE_AUDIT_TRIGGER =
'CREATE TRIGGER [#SCHEMA_NAME#].[TRG_#TABLE_NAME#_AuditHandle]
   ON  [#SCHEMA_NAME#].[#TABLE_NAME#]
   AFTER INSERT, DELETE, UPDATE
AS 
BEGIN
	IF (SELECT COUNT(1) FROM deleted) > 0 AND (SELECT COUNT(1) FROM inserted) > 0
		INSERT INTO [Audit].[#TABLE_NAME#] ([Action],#COLUMNS#)
		SELECT ''U'',#COLUMNS# FROM deleted
	ELSE IF (SELECT COUNT(1) FROM inserted) > 0
		INSERT INTO [Audit].[#TABLE_NAME#] ([Action], [#ID_COLUMN#])
		SELECT ''I'', [#ID_COLUMN#] FROM inserted
	ELSE IF (SELECT COUNT(1) FROM deleted) > 0
		INSERT INTO [Audit].[#TABLE_NAME#] ([Action],#COLUMNS#)
		SELECT ''D'',#COLUMNS# FROM deleted
END
GO'

-- Get all tables
DECLARE @SQL varchar(MAX), @SQLAuditTrigger varchar(MAX)
DECLARE @Schema varchar(255), @Table varchar(255), @SchemaTable varchar(255)
DECLARE cTables CURSOR FAST_FORWARD READ_ONLY
FOR
	SELECT TABLE_SCHEMA, TABLE_NAME, '[' + TABLE_SCHEMA + '].[' + TABLE_NAME + ']' AS [SCHEMA_TABLE]
	FROM INFORMATION_SCHEMA.TABLES
	WHERE TABLE_SCHEMA NOT IN ('PUT_SCHEMA_NAME_FILTERS_HERE_IF_REQUIRED')
	AND TABLE_NAME NOT IN ('PUT_TABLE_NAME_FILTERS_HERE_IF_REQUIRED')
	ORDER BY TABLE_SCHEMA, TABLE_NAME
OPEN cTables

-- Loop each table
FETCH NEXT FROM cTables INTO @Schema, @Table, @SchemaTable
WHILE (@@FETCH_STATUS <> -1)
BEGIN
	IF (@@FETCH_STATUS <> -2)
	BEGIN
		-- Used to hold the column SQL that gets built for the CREATE statement
		DECLARE @ColumnSQL varchar(MAX) = '', @TriggerColumnSQL varchar(MAX) = ''
		
		-- Get all columns for a table
		DECLARE @Column varchar(255), @Position int, @DataType varchar(255), @DataLength float
		DECLARE cColumns CURSOR FAST_FORWARD READ_ONLY
		FOR
			SELECT COLUMN_NAME, ORDINAL_POSITION, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH
			FROM INFORMATION_SCHEMA.COLUMNS
			WHERE TABLE_SCHEMA = @Schema
			AND TABLE_NAME = @Table
			ORDER BY ORDINAL_POSITION
		OPEN cColumns
		
		-- Loop each column
		FETCH NEXT FROM cColumns INTO @Column, @Position, @DataType, @DataLength
		WHILE (@@FETCH_STATUS <> -1)
		BEGIN
			IF (@@FETCH_STATUS <> -2)
			BEGIN
				-- Set up if it's the first column of a table
				IF @Position = 1
				BEGIN
					-- Compile the drop statments and execute
					SET @SQL = REPLACE(@SQL_DROP_AUDIT_TABLE, N'#TABLE_NAME#', @Table)
					PRINT @SQL
					--EXEC(@SQL)
					SET @SQL = REPLACE(REPLACE(@SQL_DROP_AUDIT_TRIGGER, N'#TABLE_NAME#', @Table), N'#SCHEMA_NAME#', @Schema)
					PRINT @SQL
					--EXEC(@SQL)
					
					-- Prepare the create statement (no columns in it at this point
					SET @SQL = REPLACE(@SQL_CREATE_AUDIT_TABLE, N'#TABLE_NAME#', @Table)
					--PRINT @SQL
					SET @SQLAuditTrigger = REPLACE(REPLACE(REPLACE(@SQL_CREATE_AUDIT_TRIGGER, N'#TABLE_NAME#', @Table), N'#SCHEMA_NAME#', @Schema), N'#ID_COLUMN#', @Column)
					--PRINT @SQLAuditTrigger
				END
				
				-- Build in the columns
				SET @TriggerColumnSQL = @TriggerColumnSQL + ' [' + @Column + '],'
				SET @ColumnSQL = @ColumnSQL + '[' + @Column + '] '
				SET @ColumnSQL = @ColumnSQL + 
					CASE
						WHEN @DataType = 'varchar' OR @DataType = 'nvarchar' OR @DataType = 'char' OR @DataType = 'nchar' THEN
							CASE
								WHEN (@DataType = 'varchar' OR @DataType = 'nvarchar') AND @DataLength = -1 THEN
									@DataType + '(MAX)'
								ELSE
									@DataType + '(' + CAST(@DataLength AS varchar(MAX)) + ')'
							END
						ELSE
							@DataType
					END
				SET @ColumnSQL = @ColumnSQL + ' NULL, '
				
				-- If it's the last column then compile and execute the CREATE statements
				IF (SELECT COUNT(1) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = @Schema AND TABLE_NAME = @Table) = @Position
				BEGIN
					SET @SQL = REPLACE(@SQL, N'#COLUMNS#', @ColumnSQL)
					PRINT @SQL
					--EXEC(@SQL)
					
					SET @SQL = REPLACE(@SQLAuditTrigger, N'#COLUMNS#', SUBSTRING(@TriggerColumnSQL, 1, LEN(@TriggerColumnSQL) - 1))
					PRINT @SQL
					--EXEC(@SQL)
				END
			END
		
			FETCH NEXT FROM cColumns INTO @Column, @Position, @DataType, @DataLength
		END
	END

	 -- Tidy columns
	CLOSE cColumns
	DEALLOCATE cColumns
	
	FETCH NEXT FROM cTables INTO @Schema, @Table, @SchemaTable
END

 -- Tidy tables
CLOSE cTables
DEALLOCATE cTables

You'll see I'm using schemas - obviously if you are not, then change it ;) It assumes that the first column in the table is the unique identifier to the row.

Insert Into a Table With Only One Identity Column

If you have a table that as only one column and that column is an identity column, then in order to INSERT into it, you need to use this statement:

INSERT INTO [MyTable] DEFAULT VALUES

This works in SQL Server 2008 and I haven't tested in earlier versions.

In SQL Server 2012, we have the introduction of a SEQUENCE. You could consider using one of those as an alternative.

UTF8 / Unicoding Body of PHP PEAR Email

Further to the previous article I wrote about unicoding an email subject in PHP, I was getting issues with the body not UTF8 encoding correctly either. When I used the Mail_mime object (as I was sending attachements), the standard Content-Type of text/html; charset=utf-8 in the headers was not enough.

I hunted around and came across a comment posted on the PEAR manual site. So courtesy of fredrik@krafftit.se I modded my code as follows and all worked well :)

$mimeParams = array(
 "text_encoding" => "8bit",
 "text_charset" => "UTF-8",
 "html_charset" => "UTF-8",
 "head_charset" => "UTF-8"
);

$body = $mime->get($mimeParams);

A Project With the Name ScriptComponent Already Exists

Rather annoyingly I recently had A project with the name 'ScriptComponent' already exists error when opening up a script component in an SSIS package. After some extensive searching I only found 1 article of any use, which didn't solve my problem.

This is the pop-up I was getting:

How did it happen?

This is truly stunning! Everything I found was related to installing SQL Server Service Pack 2. Not me! I copied a data flow task (containing a script component) from one dtsx in the solution and pasted it to another dtsx in the same solution. I then deleted the data flow task from the "old" dtsx file. Each time opened the "new" script component or in fact any script component in the solution (or any other solution I open), I got the error - brilliant!

Things I also tried...

I tried all these with no success:

1. Whatever was suggested in the above mentioned article.
2. Un-installed Integration Services and re-installed (latest service pack applied).
3. Un-installed and then re-installed Visual Studio 2005 and SQL Server 2005 (latest service packs applied (including the SP1 update for Windows Vista)).

What happened in the end?

I gave up and after spending 2 days trying to fix it, I re-installed Windows!

Moral of this story:

DON'T COPY AND PASTE A SCRIPT COMPONENT IN AN SSIS PACKAGE

MSSQL: Date Last Data Change Occurred on a Table

I needed to find out when the data was changed on a table in SQL Server. Unfortunately, I don't have the article I found it on in the mighty Stackoverflow, but here it is:

SELECT TABLENAME, LASTUPDATED
FROM (
        SELECT B.NAME AS 'TABLENAME',
               MAX(STATS_DATE (ID,INDID)) AS LASTUPDATED
        FROM SYS.SYSINDEXES AS A
        INNER JOIN SYS.OBJECTS AS B ON
                A.ID = B.OBJECT_ID
        WHERE B.TYPE = 'U'
        AND STATS_DATE (ID, INDID) IS NOT NULL 
        GROUP BY B.NAME
) AS A
ORDER BY LASTUPDATED DESC

A really useful bit SQL!

HTMLtweaks mBox & mForm with ASP.NET

I was recently looking around for a nice mootools implementation of a modal box and found many. I settled on one that I find brilliant and deserves much credit: HTMLtweaks mBox & mForm by Stephan Wagner.

I needed to use multiple features that mBox & mForm offer, but was struggling to get the correct configuration for it to all work with ASP.NET postbacks. Individually and certain combinations were working great but the not the full compliment for me. I got it working though and here's how:

Scenario
I wanted to use an mForm to make the form and it's elements sweeter, with validation and tooltips (using mBox). I also wanted to take advantage of the mForm.Submit features to get a confirmation when the user attempts to submit the page.

The Problem
The form, validation and tooltip, without the mForm.Submit confirmation options worked fine. When I introduced the confirmation, the form was not validated and submitted. As the form contents were not valid, I was getting an invalid submit.

The Solution
Some info:

• I have a JavaScript file called core.js that I have included on the page
• I am using a Master page

Firstly the markup. In the interest of keeping this concise, I have not included the full page. If you have got this far, you know all about that ;)

 


  
Email Address
  

   


Please enter a valid email address

 

  
Password
  

   


Please enter your password

 

  

  
 

This is a very basic login form (albeit the form element is not shown). I have included the data-required attributes and on the email address asp:TextBox also added the data-validate attribute. Under each element, I have added the markup to be used in the tooltip. Finally, the asp:Button has the CssClass added and the server-side OnClick event tied in. I have included the data-confirm attruibute, with a suitable message and added the a JavaScript function (confirmSubmit()) to the data-confirm-action attribute. Noticed the asp:LinkButton? More on that in a bit...

In core.js, I created a function called doTips(). This processes a JSON array of elements to use to tie the tips into.

function doTips(elements) {
    for (i = 0; i < elements.length; i++) {
        new mBox.Tooltip({
            content: elements[i].tip,
            attach: elements[i].element,
            event: 'mouseenter',
            position: {
                x: 'right',
                y: 'center'
            },
            delayOpen: 500
        });
    }
}

This gets called during the domready event on the page, as such:

doTips([
 {element: '<%=txtEmail.ClientID%>', tip: 'txtEmail_Info'},
 {element: '<%=txtPassword.ClientID%>', tip: 'txtPassword_Info'}
]);

The ClientID of the element to attach is sent, along with the element that has the tip in it.

Now to getting the form to submit with a confirmation and validation! In core.js I created a function called setUpSubmitForm(). This configures the mForm.Submit object.

frmSubmit = null;

function setUpSubmitForm() {
    frmSubmit = new mForm.Submit({
        form: 'fMaster',
        ajax: false
    });
    frmSubmit.validateOnSubmit = false;
}

The keen eye will notice the use of validateOnSubmit being set to false outside the declaration of the object. This is done for two reasons. Firstly, when the submit button is pressed, the form should not be submitted. Secondly, when the object is declared, it must have the validateOnSubmit property set to true in order for the validation to work.

This also gets called in the domready event.

Finally to tie it all together, I put the confirmSubmit() function onto the page. I did it this way to allow for custom actions prior to the validation and submit taking place.

function confirmSubmit() {
 _confirmSubmit('<%=btnLogin.UniqueID%>');
}

It quite simply calls _confirmSubmit that lives in core.js. The key thing to note here is that I am sending through the UniqueID of the login button. This is what _confirmSubmit() does:

function _confirmSubmit(elementUniqueID) {
    if (frmSubmit.validate()) {
        __doPostBack(elementUniqueID);
    }
}

I test to see if the form validates successfully and if so, fire the __doPostBack .NET created function, passing through the control that fired the event. The onClick server-side event now gets picked up.

So, what about the asp:LinkButton - what's that all about? Only certain controls will force .NET to create the __doPostBack  function on the page. By adding an asp:LinkButton, the code is added by the framework. Obvisouly if you have other controls on the page that create the function, then it doesn't need to be added.

Another Point...
Depending on your site configuration, you may get an Invalid postback or callback argument exception. There are a few ways to get round the problem and they are pretty well documented all over. Using EnableEventValidation in the page directives or web.config is one option, but a safer method is to use the Page.ClientScript.RegisterForEventValidation(myButton.UniqueID). I haven't got the ClientScript option working yet, but I'm sure I'll work it out at some point.

HttpContext.Current.Session is null

Had a funny one with an ASP.NET 4.0 C# project I am working on. I moved my session handling into a class to keep things tidy. I started getting the old reference to a null object error when trying to access a session variable.

This initially confused me a little as I was already doing an if (context.Session["myvar"] == null) on it. I then realised that it was the Session object itself that was null. I did a lot of hunting around on Google - and there are hundreds of responses (HttpContext.Current.Session is null). None of them were working for me.

I had checked that sessions were enabled and that the Session_Start event was firing in Global.asax.cs. The most useful article I came across was this one on stackoverflow.

My session handling class was using private static HttpContext context = HttpContext.Current; so that I had a shorthand to the Session object (context.Session). I swapped out the shorthand to use the full path to it (HttpContext.Current.Session) and we were off - all working!

How to Play MP4 Files on Your Xbox 360 - Part 2

Following on from my original article of playing MP4 files on your Xbox 360, I have finally found a conversion tool that works well for me, if changing the extension to avi doesn't work.

I am using XMedia Recode. It's pretty straight forward:

1. Drag the file(s) into the application that you want to be converted
2. Make sure you have the settings, as shown below, selected
3. Click the file, that was dragged in, and click Add Job
4. Click the Jobs tab to see the file(s) all listed ready to go
5. Click the Encode button

Once the conversion has completed (the speed of this is dependent on the processing power of your PC), change the file extension from mp4 to avi. Happy viewing!

JavaScript Password Generator and Encryption

I'm an avid user of Passpack and while I was killing 10 minutes, I was bouncing round their site and came across this sweet JavaScript Password and Encryption number.

It gives us AES 256-bit encryption (among others) and simple and complex password generation.

Good work Passpack, I can see it coming in quite handy :)

SQLCE: Large SQL Statements

Been dabbling quite a lot recently with SQLCE on Windows mobile devices. I came across something that was a little bizarre with a very large statement that I was executing.

I was performing a DELETE with a WHERE clause using a NOT IN. The NOT IN had approximately 1200 IDs in it. Very cunningly, each time it executed, the device hung with no exceptions. Great!

I haven't been able to find any evidence of issues with executing large statements like this, so I resorted to adding the IDs to a table and then modifying the WHERE clause to use NOT IN (SELECT [MyID] FROM [MyTable]).

This took way too long to run! So I changed it to use a NOT EXISTS instead - what a difference, took less than 1 second rather than the 5 or 6 seconds when using NOT IN!

Original:
DELETE FROM [MyTable]
WHERE [MyID]  NOT IN (1, 2, 3, 4, ...1200)

Revised:
DELETE FROM [MyTable]
WHERE [MyID]  NOT EXISTS (SELECT * FROM [MyIDTable] WHERE [MyTable].[MyID] = [MyIDTable].[MyID])

Collapse and Expand All Regions Macro for Visual Studio

I use regions really quite extensively in my .NET projects and did a hunt around for a sneaky Visual Studio macro that would collapse all my regions after I have finished with the file. I came across this article on David Yack's blog that demonstrates some keyboard shortcuts to use. Further down in the article a contributor called Dry put a comment on that was the macro I was after :)

The macro posted worked fine in Visual Studio 2005 but when I put it into 2010 the collapse function didn't stop looping. So I did a little pimp to it and all good - works in 2010 now.

I hope David and Dry don't mind, but I have posted the code here too in case it becomes unavailable...

Sub ExpandAllRegions()
 DTE.SuppressUI = True

 Dim objSelection As TextSelection
 objSelection = DTE.ActiveDocument.Selection

 objSelection.StartOfDocument()

 Do While objSelection.FindText( _
  "#Region", _
  vsFindOptions.vsFindOptionsMatchInHiddenText _
 )
  objSelection.WordRight()
 Loop

 DTE.ActiveDocument.Selection().StartOfDocument()
 DTE.SuppressUI = False
End Sub

Sub CollapseAllRegions()
 ExpandAllRegions()

 Dim objSelection As TextSelection
 objSelection = DTE.ActiveDocument.Selection

 objSelection.EndOfDocument()

 Do While objSelection.FindText( _
  "#Region", vsFindOptions.vsFindOptionsBackwards _
 )
  objSelection.StartOfLine( _
   vsStartOfLineOptions.vsStartOfLineOptionsFirstColumn _
  )
  DTE.ExecuteCommand("Edit.ToggleOutliningExpansion")

  objSelection.LineUp()
 Loop

 DTE.ActiveDocument.Selection.StartOfDocument()
End Sub

You could of course now tie the collapse region function into the EnvironmentEvents module and make it automatically collapse all regions when a document closes. Play about with the DocumentEvents_DocumentClosing(ByVal Document As EnvDTE.Document) Handles DocumentEvents.DocumentClosing event. May want to limit it to cs and vb files only...

Show All Headings in Word Contents Page

I was using a contents page in one of my Word documents and it was only showing heading items 1 to 3 and not the 4 and 5 levels. It transpires that by default Word only shows heading levels 1 to 3, but it is quite easy to change!

Click on your contents table and then press Alt+F9. This changes the view to look something like this:

Where it shows "1-3", change it to whatever heading level you want it to show. So if you want it to show up to level 5, change it to "1-5". Press Alt+F9 again and it will switch back to the view you are more accustomed to. You may need to do an Update Table... to show the new levels.

I've since found a useful site that has some more info on customising the table of contents and more specifically it's switches.

Open Source Data Recovery - TestDisk

Sometime ago, I came across a useful file recovery tool. More recently, my sister got herself into some more major mischief with her laptop hard disk. The laptop wouldn't boot and Windows wouldn't read it. I was little stuck. There was a huge amount of data on it that she wanted back (she wasn't backing up - needless to say!).

After some fairly hefty hunting around I found TestDisk. It's open-source and runs from the command prompt. I downloaded it and sure enough, within a short amount of time I had all the necessary files off it.

One thing I found and couldn't work out how to change was that when files are restored, I could only restore them to the folder where TestDisk was running from. No biggy and no doubt that can be changed - I just couldn't find it...

Once all that was over, it took over 10 hours to format it! Even after formatting it, Windows wouldn't read it and kept prompting to format for me - I didn't ;)

Word 2003: File Needs To Be Opened By Text Converter

A client of mine was getting a most annoying pop-up when trying to open a Microsoft Works 4.0 (.wps) file in Word 2003. The message was:

This file needs to be opened by the Microsoft Works 4.0 text converter, which may pose a security risk if the file you are opening is a malicious file. Choose Yes to open this file only if you are sure it is from a trusted source.

The Microsoft knowledge base article suggested I install service pack 3 to resolve the problem. It didn't! I had to apply the registry fix they suggested. Having done that, the warning is removed. This is the registry change required (taken directly from the article):

1. Quit Microsoft Word.
2. Click Start, click Run, type regedit, and then click OK.
3. Locate and then click to select the following registry key:
   HKEY_CURRENT_USER\Software\Microsoft\Office\11.0\Word\Options
4. After you select the key that is specified in step 3, point to New on the Edit menu, and then click DWORD Value.
5. Type DoNotConfirmConverterSecurity, and then press ENTER.
6. Right-click DoNotConfirmConverterSecurity, and then click Modify.
7. In the Value data box, type 1, and then click OK.
8. On the File menu, click Exit to quit Registry Editor.

How To Enable DB Mail in SQLEXPRESS 2008

By default SQL Server Express 2008 does not have DB mail enabled so you can't use the sp_send_dbmail stored procedure. You may have been getting the SQL Server blocked access to procedure 'dbo.sp_send_dbmail' error.

Quite a few articles I read, said that you can't use it - well - that's not strictly true! It's all there, it just has to be enabled :)

After I did this, SQLExpress was emailing just fine!

In Management Studio (SSMS), right click the server you want to enable DB mail on and choose Facets. In the View Facets pop-up choose the Surface Area Configuration option in the Facet dropdown. Then choose True for the DatabaseMailEnabled option.

Once you have clicked OK, you need to restart the SQL Express server (right click it and choose Restart).

The slightly more complicated part is that you now need to enter the data that other versions of SQL Server let you enter via the interface. So here goes:

1. Expand the tables > system tables in the msdb database
2. Create a mail account in sysmail_account
3. Create a profile in sysmail_profile
4. Match the new profile to the new account in sysmail_profileaccount (use the IDs from step 2 and 3)
5. Create a mail server for the new account  in sysmail_server (the server type will probably be SMTP, but check in sysmail_servertype if you are unsure)
6. Refresh the msdb DB, by right clicking the DB and choosing Refresh

Hey presto sp_send_dbmail now works :)

Validate .ics Calendar For Google Calendars

Somebody recently tried to share a calendar they have in Microsoft Outlook with me by using a webcal .ics link. Adding the calendar to Google calendars is quite easy (see the screenshot below - make sure you swap webcal:// for http://), but Google is very picky about making sure the construct of the calendar is valid - unlike Outlook!

So if you need to find out why Google calendars won't load it, use this great ics validator tool.

Adding the calendar:

Run Single Instance of .NET Compact Framework Application

A .NET compact framework application I work on, in some cases, seemed to have multiple instances of itself running at a time. Although the framework handles multiple instances from being loaded, it can't handle it if the call to load is in very quick succession of each other.

I hunted around a came across this useful (and novely narrated) video article on MSDN.

The only addition I would make to this, is that VB.NET does allow the use of the Using statement in more recent versions of the framework.

Process Different File Types with PHP in Plesk

So after having got PHP to process different files types in IIS7, the Plesk version of the site and using the .htaccess was giving me some grief. I came across this article on the parallels forum and got it work  by using a slightly modified version of the working example given.

AddHandler php-script .js


I needed PHP to process JavaScript files.

Plesk - Checking the Mail Log

Do you need to check the mail logs on a Plesk server? Then here's an article on how to: http://www.hosting.com/support/plesk/check-the-mail-log-on-plesk-server

You can get access to it here:
/usr/local/psa/var/log/maillog

How to Validate a UK Postcode using JavaScript

Here's a great link for the RegEx that is needed to validate a UK postcode using JavaScript.

In case the link doesn't work, here it is:

var regPostcode = /^([a-zA-Z]){1}([0-9][0-9]|[0-9]|[a-zA-Z][0-9][a-zA-Z]|[a-zA-Z][0-9][0-9]|[a-zA-Z][0-9]){1}([ ])([0-9][a-zA-z][a-zA-z]){1}$/;

The accepted formats are:
A9 9AA
A99 9AA
AA9 9AA 
AA99 9AA 
AA9A 9AA

Installing libmcrypt / mcrypt Module on Plesk

Had a major ball ache today getting the mcrypt module working on a plesk hosted VPS. This article was a god send!!

http://www.kelvinwong.ca/2009/12/06/install-libmcrypt-and-php-mcrypt-on-centos-5-3-without-losing-too-much-hair/

A lot less hair was lost :)

The crux of it is the that from the shell do the following:

> yum install libmcrypt-devel

If 32 bit do:

> yum install php-mcrypt

If 64 bit do:

> yum install php-mcrypt.x86_64

Using vi and Commands

A useful resource for using the vi editor and it's commands :)

http://www.cs.colostate.edu/helpdocs/vi.html

Process Different File Types with PHP in IIS7

A slight follow on from an earlier post I made about custom error pages in IIS7, I needed to get IIS to process JavaScript files in PHP. This is really useful if you need to do some pre-processing before the JS hits the browser. In order to get this to work I added the following to the Web.config file in the root of the site:

Note that I am using PHP in FastCGI mode.

Also, make sure you set scriptProcessor to the path of your php-cgi.exe. The other thing I had to do, as Chrome was reporting a Resource interpreted as Script but transferred with MIME type text/html warning when the JavaScript file was loaded, was to add header("Content-type: text/javascript; charset: UTF-8"); to the js file.

One thing you can do to speed things up is specify a specific file in the path parameter of the Web.config. For example to only process my_js_file.js use path="my_js_file.js". And of course, you can add Web.config files in sub-folders so only certain files types are processed with PHP in those folders.

Windows 7 Telnet is not Recognised

This is so annoying! You go to use telnet on Windows 7, via the command prompt, and you get 'telnet' is not recognized as an internal or external command. There are loads of articles on how to enable telnet on Windows 7 (here's one of many!).

But after having enabled it in programs and features, I was still getting the not recognised error. I checked that I had the telnet.exe file in the C:\Windows\System32 and I did. So I double clicked it and sure enough it pops up and works.

The only other difference is that I need to remember to use the o command to open a connection:

Telnet> o my_domain

Configure IIS7 for Custom Error Pages

I've recently (and finally!) moved to Windows 7 and with it IIS 7. I got PHP installed (using FastCGI) all fine, but was having major issues getting my custom 404 handlers to work with PHP. I found loads of articles all over the web that directed me in the right direction. But no one single article worked for me so this is what worked for me...

All the following settings can be done via the IIS Manager (Control Panel > Administrative Tools), but for me, I created the following Web.config file and put that in the root of the site that I wanted the 404 handler to be working on.

Note the use of errorMode="Custom" and existingResponse="Replace". Hope this works out for you :)

Test an IMAP Connection (Telnet)

Following on from post about how to test a POP3 connection using telnet, I thought I'd add a quick one about testing an IMAP connection. It's pretty much the same, but you use the 143 port number instead:

> telnet mail.mydomain.com 143
> login my_user_name my_password

Happy days :)


Update:
Recently I started getting an error when using the login command. Turns out I needed to do the following:


> 10 login my_user_name my_password


All subsequent commands needed to use 20, 30, etc. This is a good resource for more commands.

How To MD5 in SQL Server / MSSQL

If you need to MD5 a string in MSSQL then use the HASHBYTES function. You can also use other formats too, such as SHA and SHA1. It's as simple as:

PRINT HASHBYTES('MD5', 'MY_STRING')


If you want to return back a string rather than the varbinary HASHBYTES does return, then try the following:


PRINT SUBSTRING(master.dbo.fn_varbintohexstr( HASHBYTES ('MD5', ' MY_STRING ')), 3, 32)

.NET Round Date to Nearest Hour

A quick little snippet here for those needing to round a DateTime in VB.NET or C# to the nearest hour

VB.NET

Public Shared Function RoundedHour(ByVal dt As DateTime) As DateTime
 Return DateTime.Parse( _
   String.Format("{0:yyyy-MM-dd HH:00:00}", _
     IIf(dt.Minute > 30, dt.AddHours(1), dt) _
 )
End Function

C#

public static DateTime RoundedHour(ByVal dt As DateTime) {
 Return DateTime.Parse(
   String.Format("{0:yyyy-MM-dd HH:00:00}",
     (dt.Minute > 30 ? dt.AddHours(1) : dt)
 )
}

It's pretty straight forward. If the minutes of the time are over 30 then add an hour. If not, keep using the given date. Format the date to have the minutes and seconds set to 0 and parse it back as a date.

How To Remember Password in Mercurial TortoiseHg

I've just started using BitBucket as a remote repository for code sharing and how sweet it is too :) I decided to use Mercurial as the repository type and using TortoiseHg with it. A frustration I was having with it, was each time I did a commit or update I was getting prompted to enter my password - grrr!

I tried adding an [auth] entry to to the ini file, but that didn't work. I then came across this little 3 click gem:

Credit to: http://www.diigo.com/item/image/1fjyk/wry0?size=o

And now it works a treat!

How To Dial a Phone Number From a Webpage on an iPhone or Android Device

I always find it a ball ache when I am on a mobile device, see a telephone number on a webpage but it's not clickable to automatically dial the number for me. So if you need to do this on your websites, set up the anchor element as such:

<a href="tel:01234 567 8910">01234 567 8910</a>

Jobs dones! Nice and simple :)

How To Call PHP From a Cron in Plesk

Like a lot of people I needed to run a PHP script from a cron (crontab) via the Plesk server admin tool. Simply putting the path to the file (from root) does not run it. The way to do this is by calling the php processor with some parameters. So do the following:

php -q /var/www/vhosts/mywebsite.com/httpdocs/cron/my-file.php

Obviously use the path that relates to your file.

This seems to work for a lot of people, but as my script had includes in it I was getting failure to open stream errors when it was called. So to get round this, I changed the directory prior to the call. So it now looks like this:

cd /var/www/vhosts/ mywebsite.com /httpdocs/cron; php -q  my-file .php

Hey presto, it all works a treat now!

You may need to set permissions on the script to be executed. By default, the task will email the output of the script to you (click the Settings option on the Scheduled Tasks page to change the address - see image below). This can be turned off by adding 2>&1 to the end of the command. making the it look like this:

cd /var/www/vhosts/ mywebsite.com /httpdocs/cron; php -q  my-file .php /dev/null 2>&1

Credit to http://daipratt.co.uk/crontab-plesk-php/ and http://stackoverflow.com/questions/3140675/php-cron-job-including-file-not-working for helping to work this mess out ;)

PHP: Last Modified Date of Folder / Directory

It turns out it's really easy to get the the last modified date of a folder/directory using PHP. You use the filemtime() function. It returns a timestamp for us so we would do something like:

$lastModifed = filemtime($dir);
print "Last modified on ".date("Y-m-d H:i:s", $lastModifed );

Reduce Used Space on Android

I have an android HTC Desire and am constantly running out of space on it - boo! A funky trick I recently came across, via a friend, was to un-install the Google Maps app. Then go back to the market and install it again. It seems that the Maps app keeps a copy of itself on the device each time an update occurs. I can't be sure about this, but I had a whole load of space free up after the re-install.

Obviously in the first instance, make sure your have used the "Clear Data" feature on all appropriate apps. Some apps store loads of data that you may not really want.

Another funky little app I came across was Phone Space Saver. A simple little app that tells you which apps you can move to the SD card - and free :)

How to Play MP4 Files on Your Xbox 360

I recently purchased a GoPro HD2 and what a fine piece of equipment it is too! The GoPro records the video in MP4 format and by default the Xbox 360 won't play this format (even though it claims it does).

Anyway...
After numerous fails to convert the files to AVI (a format that is supported), it turns out all you need to do is change the file extension from MP4 to AVI. Hey presto, it shows up on the Xbox now!

File Transfers with Android - FTPDroid

After coming back from a great snowboarding holiday in Les Deux Alpes, I had loads of photos and videos that I needed to move from my Android tablet to my home PC.

I present to you FTPDroid.

It's a cracking little app that turns your android device into an FTP server. You connect your FTP client to it and off you go. It works a treat when you have large numbers of files to transfer and you don't have time to sit there while they move across your network.