amDBObj

Current component version: 1.2008.2.0
Document revision: January, 16th 2009

The provided .Net CLR assembly exports a CLR stored procedure to emulate Database Mail features missing in Microsoft SQL Server Express 2005 and 2008 editions; the very same feature was not available in MSDE 2000 as well, but a SMTP alternative was available in the form of a free exented stored procedure.

amDBObj.dll assembly includes amSMTPmail;
you have to enable CLR support for the SQLExpress instance via the script command

EXEC sp_configure 'clr enabled', '1';
RECONFIGURE;

that will use the system stored procedure sp_configure to enable the CLR support for the instance;
enable TRUSTWORTHY database option as the CLR procedure obviously requires external access, via

ALTER DATABASE [theDB] SET TRUSTWORTHY ON;

(in order to update your eventual existing registration of a previous buil you have to drop both the procedure and the assembly, and then re-register then,

-- drop exisisting previous versions
IF OBJECT_ID ( N'[dbo].[amSMTPmail]' ) IS NOT NULL
	DROP PROCEDURE [dbo].[amSMTPmail];
GO
IF  EXISTS (SELECT * FROM sys.assemblies asms WHERE asms.name = N'amDBObj')
  DROP ASSEMBLY [amDBObj];
GO
--

)

and you can later register the assembly via

CREATE ASSEMBLY [amDBObj]
	AUTHORIZATION owner_name
	FROM 'c:\..\full_path_to_the_dll.Dll'
   WITH PERMISSION_SET = EXTERNAL_ACCESS;

and finally register the actual CLR stored procedure,

CREATE PROCEDURE [dbo].[amSMTPmail]
        @Server [nvarchar](4000),
        @Sender [nvarchar](4000),
        @AddressesTO [nvarchar](4000),
        @AddressesCC [nvarchar](4000),
        @AddressesCCN [nvarchar](4000),
        @AttachFiles [nvarchar](4000),
        @Subject [nvarchar](4000),
        @MessageBody [nvarchar](4000),
        @UserName [nvarchar](4000),
        @UserPassword [nvarchar](4000),
        @ServerPort int,
        @IsHtml bit
    WITH EXECUTE AS CALLER	-- or desired security context for the execution
                        	-- of the stored procedure
      AS EXTERNAL NAME [amDBObj].[amDBObj.amDBObjectsProcedures].[amSMTPmail];

possibly define your set of permissions on the securable, and, finally, use it as desired/required, via standard
EXECUTE [owner].[amSMTPmail] ...; like

DECLARE @ret int;	
EXEC @ret = [dbo].[amSMTPmail] 
	@Server = N'yourmailserver.com',
	@Sender = N'you@yourmailserver.com',
	@AddressesTO = 'you@yourmailserver.com; other.address@othtermailserver.com',
	@AddressesCC = NULL,	-- list of CC addresses
	@AddressesCCN = NULL,	-- list of BCC addresses
	@AttachFiles = NULL,	-- 'c:\folder\file.xxx'
	@Subject = N'SMTP test',
	@MessageBody = N'SMTP test message',    -- or html text like
	/*
            '<html xmlns="http://www.w3.org/1999/xhtml">
            <head>
            <title>Document Title</title>
                <body>
                    <center><h3>This is an <b><i>html</i></b> document.</h3></center></br>
                    <p>This is an html document sent by <b>amDbObj</b>.
                    </p>
                </body>
            </html>'	
	*/
	@UserName = NULL,		-- yourUserName for authenticated SMTP transactions
	@UserPassword = NULL,	-- yourPassword for authenticated SMTP transactions
	@ServerPort = 0,		-- or NULL = default 25 port; other port for specific SMTP transactions
	@IsHtml = NULL			-- or 0 for text, 1 for Html	
	;
SELECT @ret AS [Execution result];

Messages are currently encoded depending on the message type:

Text messages are encoded using the default setting of us-ascii;

HTML messages are encoded using UTF8 encoder.

The default SMTP port 25 is used whenever 0 or NULL are provided as relative port parameter for the SMTP transaction.

This solution can even be handy if you like to mail someone the result of scheduled batches..
SQL Express does not provide SQL Server Agent features, but you can for instance replace scheduled backup via a command prompt call to SQLCmd command line tool...

Create a bat file like

REM backup database
SQLCmd -E -S(Local) -Q"BACKUP DATABASE [AdventureWorks] TO DISK = N'c:\aw.bak' WITH NOFORMAT, NOINIT, NAME = N'AdventureWorks-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10, CHECKSUM" >c:\bck.txt

REM mail results
SQLCmd -E -S(Local) -Q"EXECUTE [Test].[dbo].[amSMTPmail] 'mymailserver', 'me@mycompany.com', 'the_dba@mycompany.com; the_sysadmin@mycompany.com', NULL, NULL, 'c:\bck.txt', 'performed backup', 'included backup result file', NULL, NULL, NULL /* or 0 for default 25 port */, 0 /* plain text */"

named MyBackup.bat

Provide adeguate schedule(s) via the underlying native operating system scheduler using the Add Scheduled Task wizard from the Control Panel;
the task will be executed at the defined schedule(s) or manually by double-clicking it and a mail will be sent to the designed destination addresses including the generated output file as attachment.

The project is free to use.

At this link, you can find additional Microsoft released tools/components for SQL Server Express as well, including SQL Server Management Studio Express.

Please do not hesitate to report bugs, errors and/or suggestions.
Check for new releases as long as for further tools..