Mail database objects - via sql

by Albert Mutangiri 26. April 2010 20:54

Last month I had a complex task working with one of my friend's legacy applications. He's got a bunch of sql data transformation services configured to import data from a bunch of text files. Although data transformation services offers powerfull capabilities when dealing with bulk data imports, They are pretty complex to debug as well. So we needed a way to track the number of records that are imported and errors if any for each DTS as well as notify the administrator with this data.

So well I figured out that with Data Transformation Services, you can configure it to log it's activities in msdb system database - by default it creates logs in sysdtspackagelog table. We needed to send a view of this package log in html format - via email to the configured Administrator after each dts completes running.

With the help of the system procedure msdb.dbo.sp_send_dbmail, I've created a custom mail sender in sql which we could execute by passing parameters to it and run it in the context of DTS running as a custom sql script, it loops through the column names of the specified database object and generates html. Well this worked like a charm Smile


create proc [dbo].[send_sql_table_as_html] 
 
    @source_db    sysname,       --where the @object_name is residing
    @schema       sysname,        --Schema name eg.. dbo.
    @object_name  sysname,      --Table or view to email
    @emailAddres varchar(75),   --Mail Address
    @name nvarchar(15),         --Package Name
    @order_clause nvarchar(max)  --The order by clause eg. x, y, z
as
begin  
  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 = 'Please find the upload details for ' + @name + ' file '
    set @body = @body + cast( '</table>' as nvarchar(max) )

    set @body = '<p>File upload Details for -------' + @name + ' file'
              + ' </p>'
              + cast( @body as nvarchar(max) )

   EXEC msdb.dbo.sp_send_dbmail  @profile_name = 'Test',  --Profile configured in sql, this will be used by sp_send_dbmail to send mail

                                  @recipients = @emailAddres,
                                  @body = @body,
                                  @body_format = 'HTML',
                                  @subject = @subject

end

Tags:

Sql legacy - DTS

Comments

4/27/2010 12:25:52 AM #

Ron and Dave

Dude, Did you mean Albertoncocaine.net?

Good work though. Keep it up.

Ron and Dave Romania | Reply

4/27/2010 12:30:10 AM #

Albert Mutangiri

wahahaha lol !!

Albert Mutangiri United Kingdom | Reply

12/18/2016 4:52:09 AM #

pingback

Pingback from steroidsforsale.biz

pink dianabol

steroidsforsale.biz | Reply

Add comment


(Will show your Gravatar icon)

  Country flag

biuquote
  • Comment
  • Preview
Loading



About Me

mage.axd?picture=2012%2f4%2fbert.jpg

Hi, My name is Albert Mutangiri, I am a software developer currently interested in software design, integration, .net technologies and Java. I'm currently developing enterprise applications for business process automation using Java & .Net Technologies.

 

I Code Java

PSNetwork


bertoncaffeine

Playing
17 0 1 0 18
13 6 0 0 19
4 0 0 0 4
22 1 0 0 23
0 0 3 0 3

Tag cloud