OpenVPN Client: Import Error Cannot Read ca Directive

I was following instructions to import an OpenVPN connection and got the following error:

IMPORT_ERROR: Profile or its references could not be read: cannot process OpenVPN file: cannot read file for OpenVPN 'ca' directive: [Errno 2] No such file or directory:

Seemed odd! As when I re-read the error message the path it was using was C:\Program Files\OpenVPN Technologies\OpenVPN Client\config and not the path it was installed under (C:\Program Files (x86)\OpenVPN Technologies\OpenVPN Client\config).

I fixed this by creating he config folder in the Program Files folder and not the Program Files (x86) folder. The connection imported fine.

SSRS: Table KeepTogether Not Working

I was recently having a problem with SQL Server Reporting Services 2005 edition trying to stop a table control from breaking over a page. The obvious solution was to set the KeepTogether property to True. Wrong! Well, not exactly. It does keep the table together and not split it over a page, but only the detail! Headers and footers are not included.

I did some extensive trawling and couldn't find an easy solution - but there is one...

Drop the table into a list control and set the KeepTogether property to True on the list.

This works beautifully for me and is really easy. The only thing you need to do is make sure that the list is the same size the table (and that's optional). Thus making sure it doesn't exceed the page width and so on.

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.