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