SQL xp_findnextmsg and xp_readmail

If you use SQL mail, you can read the messages in the SQL inbox stack and do stuff with them. Recently I had to use SQL scripting to find messages in the SQL inbox, read the message, determine if it is a text or html email, then add the message to a table in the database.

This can be easily achieved using the xp_findnextmsg and xp_readmail procedures. To strip the html tags from the email, I used a UDF (User Defined Function) written by the SQL guru of http://blog.sqlauthority.com. Kudos to him for sharing the function.

All SQL examples are shown below.

The xp_readmail example is written for SQL Server 2000, where there is a limit of varchar(8000) for data type varchar. In SQL Server 2005, you can use varchar(max) which extends the limit. You will see in my example I split email messages that are bigger than varchar(8000) when using xp_readmail.