The database mail configuration saved into a temp table

Question

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 VARCHAR(MAX),
        @TableHead VARCHAR(MAX),
        @TableTail VARCHAR(MAX)
    SET NoCount ON ;


    SET @TableTail = '</body></html>' ;

    SET @TableHead = '<html><head>' + '<style>'
        + 'td {border: solid black 1px;padding-left:5px;padding-right:5px;padding-top:1px;padding-bottom:1px;font-size:10pt;} '
        + '</style>' + '</head>' + '<body>'


    SELECT  @Body = ''



    SELECT  @Body = @Body + '<table cellpadding=0 cellspacing=0 border=0>'
            + '<tr><td bgcolor=#E6E6FA>Database Name </td>'
            + '<td width="100px" bgcolor=#E6E6FA>Backup Size</td>'
            + '<td width="100px" bgcolor=#E6E6FA>Time Taken</td>'
            + '<td width="100px" bgcolor=#E6E6FA>Backup Start Date</td>'
            + '<td width="100px" bgcolor=#E6E6FA><b>First LSN</b></td>'
            + '<td width="100px" bgcolor=#E6E6FA><b>Last LSN</b></td>'
            + '<td width="100px" bgcolor=#E6E6FA><b>Backup Type</b></td>'
            + '<td width="100px" bgcolor=#E6E6FA><b>Server Name</b></td>'
            + '<td width="100px" bgcolor=#E6E6FA><b>Recovery Model</b></td>'
            + '<td width="300px" bgcolor=#E6E6FA>Physical Device Name</td>   </tr>'


    -- Backup History script of AAsim Adbullah                
    -- http://blog.sqlauthority.com/2010/11/10/sql-server-get-database-backup-history-for-a-single-database/ 
    SELECT  @Body = @Body
            + ( SELECT  td = s.database_name, '',
                        td = CAST(CAST(s.backup_size / 1000000 AS INT) AS VARCHAR(14))+ ' ' + 'MB', '',
                        td = CAST(DATEDIFF(second, s.backup_start_date,
                                           s.backup_finish_date) AS VARCHAR(4)) + ' ' + 'Seconds', '',
                        td = s.backup_start_date,  '',
                        td = CAST(s.first_lsn AS VARCHAR(50)), '',
                        td = CAST(s.last_lsn AS VARCHAR(50)), '',
                        td = CASE s.[type]
                               WHEN 'D' THEN 'Full'
                               WHEN 'I' THEN 'Differential'
                               WHEN 'L' THEN 'Transaction Log'
                             END,'',
                        td = s.server_name, '',
                        td = s.recovery_model,'',
                        td = m.physical_device_name,''
                FROM    msdb.dbo.backupset s
                        INNER JOIN msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id
                WHERE   1=1
                  AND   s.backup_start_date >= DATEADD(dd, 0, DATEDIFF(dd, 0,GETDATE() - 1)) -- --- Date check for one day backup history information
                  AND   s.[type] IN ('D','I') -- ignoring the transaction log backups
                ORDER BY backup_start_date DESC,
                        backup_finish_date
              FOR
                XML PATH('tr')
              ) + '</table>'



    SET @Body = REPLACE(@Body, '_x0020_', SPACE(1))
    SET @Body = REPLACE(@Body, '_x003D_', '=')
    SET @Body = REPLACE(@Body, '<tr><TRRow>1</TRRow>', '<tr bgcolor=#C6CFFF>')
    SET @Body = REPLACE(@Body, '<TRRow>0</TRRow>', '')


    SELECT  @Body = @TableHead + @Body + @TableTail

    --select @Body

    IF ( @Body IS NULL ) 
        BEGIN 
                EXEC msdb.dbo.sp_send_dbmail @recipients='[email protected]',  -- Add Valid Email Id
                @subject = 'Backup History Information',
                @profile_name = 'DBA', -- Change Profile Name
                @body = 'No Backup History Found for past 1 Day',
                @body_format = 'HTML' ;

        END
    ELSE 
    Begin
        EXEC msdb.dbo.sp_send_dbmail @recipients='[email protected]', -- Add Valid Email Id
                @subject = 'Backup History Information',
                @profile_name = 'DBA', -- Change Profile Name
                @body = @Body,
                @body_format = 'HTML' ;
    END

Erik Darling · Accepted Answer · 2017-06-29 17:26:59Z

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!

Hannah Vernon · Accepted Answer · 2019-07-29 17:29:29Z

@Erik Darling has a great method of extracting the account name from the msdb table directly. Use that method.

However, to help you understand how you could use the INSERT INTO ... EXEC construct, I've written this little snippet of code:

DECLARE @Command nvarchar(max);
DECLARE @params nvarchar(max);
DECLARE @ProfileName sysname;
DECLARE @ColumnList nvarchar(max);
DECLARE @tsql nvarchar(max);

SET @params = '@ProfileName sysname OUTPUT';

SET @tsql = N'EXEC msdb.dbo.sysmail_help_account_sp;'

SET @Command = N'CREATE TABLE #sysmailaccount
(
    <COLUMNLIST>
);';

SET @ColumnList = '';
SET @ColumnList = STUFF((SELECT ', ' + QUOTENAME(rs.name) + ' ' + rs.system_type_name
FROM sys.dm_exec_describe_first_result_set(@tsql, NULL, NULL) rs
ORDER BY rs.column_ordinal
FOR XML PATH('')), 1, 2, '');

SET @Command = REPLACE(@Command, N'<COLUMNLIST>', @ColumnList);

SET @Command = @Command + N'
INSERT INTO #sysmailaccount
' + @tsql + N';
SELECT @ProfileName = sma.name
FROM #sysmailaccount sma;
';
EXEC sys.sp_executesql @Command, @params, @ProfileName = @ProfileName OUT;
SELECT ProfileName = @ProfileName;

At the end of above code, you have @ProfileName containing the name of the first database mail profile name.

The code uses the sys.dm_exec_describe_first_result_set system function to construct a temporary table with the same definition as the output of msdb.dbo.sysmail_help_account_sp. It then uses sys.sp_executesql to execute an INSERT INTO ... EXEC command to actually run the sysmail_help_account_sp stored proc, placing the output into the #sysmailaccount temp table.

Stack Exchange Network

The database mail configuration saved into a temp table

2 Answers 2

Your Answer

Not the answer you're looking for? Browse other questions tagged
sql-server
backup
sql-server-2016
dynamic-sql
database-mail
or ask your own question.

Linked

Hot Network Questions

The database mail configuration saved into a temp table

2 Answers 2

Your Answer

Sign up or log in

Post as a guest

Not the answer you're looking for? Browse other questions tagged sql-serverbackupsql-server-2016dynamic-sqldatabase-mail or ask your own question.

Linked

Related

Hot Network Questions

Not the answer you're looking for? Browse other questions tagged
sql-server
backup
sql-server-2016
dynamic-sql
database-mail
or ask your own question.