Column Widths When Attaching Data in xp_sendmail

A colleague asked me for some help with a problem she was having whilst using the extended procedure xp_sendmail. She wanted to use xp_sendmail to mail the results of the system stored procedure sp_helpindex so that certain people could be aware of what indices were present on a table. The problem was that the data coming out had enormous amounts of white space after it no matter how many LTRIMS or RTRIM's you used which meant that it wouldn't present very well in an email. Now, although I use xp_sendmail a fair bit, I rarely use it for executing queries and sending back data. In the end, I came up with a dodgy looking hack that more or less sorted us out for the time being but is distinctly inelegant.

Basically, I created a wrapper SP for sp_helpindex like so:

CREATE PROC dbo.MyProc


AS

    SET NOCOUNT ON


    CREATE TABLE ##idx_temp (
        [index_name] SYSNAME,
        [index_description] VARCHAR(210),
        [index_keys] NVARCHAR(2078)
    )


    INSERT INTO ##idx_temp

    EXEC sp_helpindex 'tablename'

    SELECT CONVERT(VARCHAR(100), index_name), CONVERT(VARCHAR(100), index_description), CONVERT(VARCHAR(100), index_keys)

    FROM ##idx_temp


    DROP TABLE ##idx_temp


    SET NOCOUNT OFF


GO

It's not pretty: is simply limits the column widths to 100 characters (orwhatever your VARCHAR width is set to) but it was an improvement. If anyone knows of a better way of doing this (and I refuse to believe that there isn't) then please, let me know!

Geeky Developer Tips You Might Find Useful

Or you might not. I'm not bothered either way, but in the course of my daily grind as a developer working with SQL Server, VB6, VB.Net, ASP and the like, I often come accross oddities and sometimes a frustrating lack of any information on problems I'm having writing a piece of code or installing something. It occurred to me that I ought to document them, mainly to help myself, but also, in the spirit of fraternal brotherhood, to help all those other poor buggers who are having the same or a similar problem.

We're going to start with two nice little beauties, one of which didn't get entirely solved to my xsatisfaction (but was close enough that I couldn't justify any more time on it) and another which took me 2 days of buggering about to sort out because I couldn't find any clear explanataions of what I was trying to do.

If this helps only one person then at least it has done something useful. If you have any tips you want to submit then leave a comment and I'll see what I can do.

Cheers

Monkey Boy