Would you like to win a Design by Humans t-shirt?
Powered by MaxBlogPress 

Would you like to win a Design by Humans t-shirt?
Powered by MaxBlogPress 

SQL xp_findnextmsg and xp_readmail

Posted on November 15, 2009
Filed Under Database | Leave a Comment

If you've enjoyed reading this post then please subscribe to my Full Text RSS Feed.

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.

readmail

readmail2

Bookmark and Share
Sphere: Related Content

Other Related Posts:

  • No related posts


    • If you've enjoyed reading this post then why not subscribe to received updates by email.

    Enter your email address:

    Delivered by FeedBurner

    Email This Post Email This Post

    Trackback This Post

    Comments

    Leave a Reply




    CommentLuv Enabled

    Twitter

    Posting tweet...

    Powered by Twitter Tools