The database mail configuration saved into a temp table

I am currently dealing with a bunch of servers and they have different Database Mail Configurations.

I can see the Database Mail configuration for each of those servers.

I use the following script:

--==========================================================
-- getting the Database Mail Configuration
-- Marcelo Miorelli
-- 1-April-2014
--==========================================================

select @@servername
--SQLSALON1\STOCKALLOCATION

-- http://dba.stackexchange.com/questions/47058/how-can-i-see-the-current-database-mail-configuration

EXEC msdb.dbo.sysmail_help_configure_sp;
EXEC msdb.dbo.sysmail_help_account_sp;
EXEC msdb.dbo.sysmail_help_profile_sp;
EXEC msdb.dbo.sysmail_help_profileaccount_sp;
EXEC msdb.dbo.sysmail_help_principalprofile_sp;

EXEC msdb.dbo.sysmail_help_account_sp

Actually the procedure msdb.dbo.sysmail_help_account_sp is my favourite in this regard.

Now I would like to email the backup history for the day before (only full and differential backups) and I need to find out the profile name for each of those servers.

I would like to get this done by saving the contents of the above procedure into a temp table or table variable and just query the info from it by the time that I have all the data I need and I am about to email them.

How can I do that for those procedures, specially the sysmail_help_account_sp?

I specifically need to find out the profile name.
I have used DBA for the script below.

this is the script I am using to generate the contents of my email:

```
--=====================================================================================================================
-- sql server backups report in HTML format to be emailed
--http://dba.stackexchange.com/questions/81432/how-do-i-use-powershell-to-get-a-sql-server-backup-status

--marcelo miorelli
--19-nov-2014
--=====================================================================================================================

DECLARE @Body V

This is actually super easy, and you don't even need to go through the trouble of doing INSERT... EXEC with the stored proc.

USE msdb

EXEC sp_helptext 'sysmail_help_account_sp'

This will return the script info:

CREATE PROCEDURE dbo.sysmail_help_account_sp  
   @account_id int = NULL,  
   @account_name sysname = NULL  
AS  
   SET NOCOUNT ON  

   DECLARE @rc int  
   DECLARE @accountid int  
   exec @rc = msdb.dbo.sysmail_verify_account_sp @account_id, @account_name, 1, 0, @accountid OUTPUT  
   IF @rc <> 0  
      RETURN(1)  

   IF (@accountid IS NOT NULL)  
      SELECT a.account_id, a.name, a.description, a.email_address, a.display_name, a.replyto_address, s.servertype, s.servername, s.port, s.username, s.use_default_credentials, s.enable_ssl   
      FROM msdb.dbo.sysmail_account a, msdb.dbo.sysmail_server s  
      WHERE a.account_id = s.account_id AND a.account_id = @accountid  

   ELSE  
      SELECT a.account_id, a.name, a.description, a.email_address, a.display_name, a.replyto_address, s.servertype, s.servername, s.port, s.username, s.use_default_credentials, s.enable_ssl  
      FROM msdb.dbo.sysmail_account a, msdb.dbo.sysmail_server s  
      WHERE a.account_id = s.account_id  

   RETURN(0)

You can see that it just queries these system views in msdb:

SELECT *
FROM msdb.dbo.sysmail_account a, msdb.dbo.sysmail_server s  
WHERE a.account_id = s.account_id

Which should be easy to integrate into your process.

Hope this helps!

StackExchange Database Administrators Q#177610, answer score: 5