March 2008 - Posts

Ever wanted to take a table, permanent or temporary or a view and just email it straight from SQL where the query result sits in the email as a nicely formatted HTML table rather than that ugly text file attachment you get when using sp_send_dbmail? Ever hit that infinite deadlock I blogged about the other day if you use the sp_send_dbmail attachment feature? Well, this stored procedure is for you!

This is an expansion of an example in the sp_send_dbmail section in books online, all I’ve done is made it dynamically accept a table instead of you needing to hard code it.

The stored procedure is very basic and I’ll probably expand it considerably for a production environment (Monday Darren! - honest), you pass in the source database, schema and table or view name of the object you want to convert into a HTML table in your email.

Usage is simple; if you have a complex query just run it into a temporary table and pass that into the procedure.

Example

select type, cnt = count(*)

into #toemail

from sys.objects

group by type

 

exec email_object_as_html @source_db    = 'tempdb',

                          @schema       = '',

                          @object_name  = '#toemail',

                          @order_clause = 'cnt desc, type',

                          @email = 'tonyrogerson@torver.net'

 

drop table #toemail

Sends an email as follows:-

Below is the table tempdb..#toemail converted from a query into HTML for your pleasure

type cnt
S 41
U 16
IT 3
SQ 3
AF 1
P 1
PC 1

Anyway have a play, usual provisos because I’m using dynamic SQL, I’ve not put the injection protection in this because I wanted to keep the proc in its simplest form for the blog. HTML HT

create proc email_object_as_html

    @source_db    sysname,       --  Where the @object_name is resident

    @schema       sysname,       --  Schema name eg.. dbo.

    @object_name  sysname,       --  Table or view to email

    @order_clause nvarchar(max), --  The order by clause eg. x, y, z

    @email        nvarchar(max)  --  Email recipient list

as

begin

    /**

        Convert's the specified table or view into an html table and emails it.

 

        Tony Rogerson, SQL Server MVP

        28 March 2008

        http://sqlblogcasts.com/blogs/tonyrogerson

 

        You are free to use and modify this,

        just keep the above in place;

        I offer no warranties, you get this as is.

 

    **/

 

    declare @subject nvarchar(max),

               @body    nvarchar(max)

 

    --  Get columns for table headers..

    exec( '

    declare col_cur cursor for

        select name

        from ' + @source_db + '.sys.columns

        where object_id = object_id( ''' + @source_db + '.' + @schema + '.' + @object_name + ''')

        order by column_id

        ' )

 

    open col_cur

 

    declare @col_name sysname

    declare @col_list nvarchar(max)

 

    fetch next from col_cur into @col_name

 

    set @body = N'<table border=1 cellpadding=1 cellspacing=1><tr>'

 

    while @@fetch_status = 0

    begin

        set @body = cast( @body as nvarchar(max) )

                  + N'<th>' + @col_name + '</th>'

 

        set @col_list = coalesce( @col_list + ',', '' ) + ' td = ' + cast( @col_name as nvarchar(max) ) + ', '''''

 

        fetch next from col_cur into @col_name

 

    end

 

    deallocate col_cur

 

    set @body = cast( @body as nvarchar(max) )

              + '</tr>'

 

    declare @query_result nvarchar(max)

    declare @nsql nvarchar(max)

 

    --  Form the query, use XML PATH to get the HTML

    set @nsql = '

        select @qr =

               cast( ( select ' + cast( @col_list as nvarchar(max) )+ '

                       from ' + @source_db + '.' + @schema + '.' + @object_name + '

                       order by ' + @order_clause + '

                       for xml path( ''tr'' ), type

                       ) as nvarchar(max) )'

 

    exec sp_executesql @nsql, N'@qr nvarchar(max) output', @query_result output

 

    set @body = cast( @body as nvarchar(max) )

              + @query_result

 

    --  Send notification

    set @subject = 'Your table as requested'

 

    set @body = @body + cast( '</table>' as nvarchar(max) )

 

    set @body = '<p>Below is the table ' + @source_db + '.' + @schema + '.' + @object_name

              + ' converted from a query into HTML for your pleasure</p>'

              + cast( @body as nvarchar(max) )

 

    EXEC msdb.dbo.sp_send_dbmail  @profile_name = 'TORVER',

                                  @recipients = @email,

                                  @body = @body,

                                  @body_format = 'HTML',

                                  @subject = @subject

 

end

Spent a while today upgrading SQLBlogCasts.com from CS2.0 to CS2007.1 - very simple migration, a few things I'd forgotten to do but up and running now.

It's resident on one of my own servers down in Docklands running on SQL 2005 Express.

While tidying up the database bits I thought to myself, I wonder how popular the site is because I've been noticing more and more posts on the RSS feed from the site.

Total blog posts: 1,692 from 72 blogs since 5th February 2006 which is when SQLBlogCasts.com came to be.

Since inception we've had 982,358 aggregated views and 1,476,232 total views to the site - wow - and that's just blogs because we don't have forums! Personally, I'm amazed at how popular the site has become.

Out of the 72 blogs, 49 are based in the UK and the other 23 are based in other parts of the world.

Anyway, best put the backups in place and that's me - enjoy your long weekend and happy blogging folk!

Tony.

"What tastes better, Microsoft® SQL Server® 2005 or Oracle 11g? What do people think? We're taking the taste challenge to the street. Find out what people are saying about their favorite database. Tast-ay!"

Very good, very funny.

http://www.databasetastechallenge.com/

When you are waiting for that 50GB database to restore on your slow server instead of reading FHM you can play a game :)

Enjoy.

Becareful though - some subliminal marketing going on there, think it's about with Oracle you have to buy all the different bits you need instead of it all being in the one licence fee - security, BI, HA etc...

Spring is upon us here in Harpenden, UK.

Just took a couple of shots from my pond whilst having a break.

Enjoy....

(I'll give a book to the best captions (postage to UK Mainland only))

If you want a bigger side image then -> http://sqlblogcasts.com/files/folders/9368/download.aspx

Tony.

So you are using the @query parameter of sp_send_dbmail and all of a sudden you start getting blocks and your connection running sp_send_mail is the cause, your connection running sp_send_mail just sits there in a runnable state with the lastwaittype of MSQL_XP.

What is happening?

create table ##ed_volval ( acol int not null )

 

begin tran

 

insert ##ed_volval values( 1 )

 

    EXEC msdb.dbo.sp_send_dbmail

        @recipients='tonyrogerson@torver.net',

        @subject = N'Bad Idea',

        @body = N'Bad idea',

            @query = N'select * from ##ed_volval',

        @body_format = 'HTML',

        @profile_name = 'TORVER' ;

The above connection starts a transaction then inserts rows into a table; you then call sp_send_dbmail to fire off an email message.

Sp_send_dbmail actually spawns another completely separate connection into SQL Server using an extended stored procedure, because your connection has the table locked the spawned connection blocks which in turn prevents the connection that issued the sp_send_dbmail from continuing – stale mate!

To identify the spawned process you can track it through, on a real system it can be a tad problematic.

select program_name, spid, blocked, lastwaittype, waitresource

from master..sysprocesses

where blocked > 0

   or spid in ( select blocked from master..sysprocesses where blocked > 0 )


You will see two runs...

One will be the connection running sp_send_dbmail, this will have the lastwaittype of MSQL_XP; the second row will have a program_name of SQLCMD and will be blocked by the connection running sp_send_dbmail.

This should never happen if sp_send_dbmail used Service Broker properly; why it can’t just plonk the row onto the queue and let activation stored procedures pick up the task I don’t understand. It would allow the caller process to continue and the activation stored procedure would just get blocked until the transaction released.

So, if you ever get the lastwaittype MSQL_XP then look for any connections with a program_name SQLCMD and trace it through to see if it’s a sp_send_dbmail problem and if you can guarentee then use KILL on it to release the process running sp_send_dbmail.

Note – the pain here is that the connection running sp_send_dbmail (the blocker) cannot be killed!

Got round to sorting the bits out, the session I did at SQLBits Birmingham on 1st March I looked at some of the table expressions available in SQL Server 2005 - Derived Tables, Temporary Tables and Common Table Expressions. I look at the basic framework and then go on into more advanced uses like running totals and cumulative totals.

Presentation: http://sqlserverfaq.com/controls/kbase/store/downloads/SQLBits20080301MakingtheLeapintoAdvancedTSQL.ppt

Recording: http://sqlserverfaq.com/controls/kbase/store/downloads/SQLBits20080301WMV.zip

Sample DB: http://sqlserverfaq.com/controls/kbase/store/downloads/SQLBits20080301DB.zip

SQL Scripts: http://sqlserverfaq.com/controls/kbase/store/downloads/SQLBits20080301SQL.zip

Unfortunetly I had way too much content for the hour slot so over the coming weeks I'll be tayloring it, going into more depth in specific areas and removing some of the basic stuff ready for Scottish Developers Day on the 10th May.

Should also spurn a few blog posts.

As I draw breath from another busy week I take stock on what a great event last Saturday was - 350 people on the day, tons of positive feedback, throughout the day the whole atmosphere was great - can't wait until SQLBits III.

The day did start off in a panic, mine and Charles laptops only have DVI output and the projectors only took VGA so I had to run into town and pick up a convertor - thank god for Maplins! Not sure I could squeeze 120 people around my laptop screen. The Saturday night after the event a few of us piled into Birmingham for a Balti - Simon Sabin chose the restraunt and the food was cracking - one problem, because of a licence problem they couldn't serve us drink, 13 thirsty geeks all wanting refreshment - as luck would have it there was a Tesco next door so we loaded up a few baskets and brought our own - splendid :)

Thanks to all those who helped on the day including the speakers, Barry Dorrans, Phil Winstanley, Colin MacKay, Ed Vassie, Martin Cairney and Melville Thomson.

This week we have been collating presentations and putting the feedback page together which I'm about to email out - I could almost do this for a living.

Where does all the time go.

I'll post my presentation and the recording shortly.

Remember - the UK SQL Server User Group wants to put on regional meetings - just give me details of a suitable venue (30 - 120 people) and I'll do the rest.

Tony.