SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
Search:  
 
 

Theophilus

Add to Technorati Favorites Add to Google
 

Dynamically getting mail profile for sp_send_dbmail

By David Benoit in Theophilus | 01-26-2009 3:08 PM | Categories:
Rating: |  Discuss | 2,602 Reads | 125 Reads in Last 30 Days |no comments

I have found that I like to generate a script once and roll it out to multiple servers at the same time. SQL Server 2008 Management Studio makes that fairly easy by allowing one window to connect to multiple instances simultaneously. We are not going to discuss that here as there are plenty of other references to this feature on the web including the one below; 

http://www.sqlservercentral.com/articles/SQL+Server+Management+Studio/63650/

With this new feature comes the ability to create a "generic" job and distribute the job to multiple servers. Many of the jobs that I create will utilize sp_send_dbmail to email me the outputs for morning "ops" review or to provide some additional troubleshooting information for my review. A simple way to make the sp_send_dbmail generic is by passing in the @profile_name as a variable which can be populated as shown in the following example;

--Send Alert
declare   
@NumRecs int,
          
@SubjectTxt nvarchar(1000),
           @MailProfile nvarchar(100)


select
@NumRecs = (select COUNT(*) from MyErrors)

select
@SubjectTxt = 'Error Output For Server '+@@SERVERNAME

select
@MailProfile = (select top 1 name from msdb..sysmail_profile order by last_mod_datetime asc)

if
@NumRecs > 0
begin
    exec
msdb.dbo.sp_send_dbmail
        @profile_name = @MailProfile,
        @recipients=N'SomeEmail@somecompany.com',
        @query = 'SELECT * FROM MyErrors',
        @attach_query_result_as_file = 1,
        @subject = @SubjectTxt

end


The format that I use for the query to pull the mail profile just looks for the earliest profile created but you can obviously change that to suit your needs. You do need to make sure that you are only selecting one profile as there can be more than one visible on an instance and this will cause a failure due to the inability to pass more than one profile into the parameter.

Comments
There are no comments on this post
Leave a Comment
Only members of SQLServerCentral may leave comments. Register now for your free account or Sign-In if you are already a member.