go backarticles

Articles of SQLschool.gr Team

Script generator for Database mail settings

Antonios Chatzipavlis

Overview

Το database mail στο SQL Server είναι ένα από τα features που πρέπει κάποιος να χρησιμοποιεί καθώς έτσι μπορούμε να κάνουμε το SQL Server να μας μιλάει.

Αυτό που λείπει δυστυχώς είναι η δυνατότητα να μπορεί κάποιος να κάνει scripting τα υπάρχοντα profiles και accounts είτε γιατί θέλει να τα δει αλλά κυρίως γιατί θέλει να τα αντιγράψει σε κάποιον άλλον server.

Για αυτό το λόγο έχω δημιουργήσει το παρακάτω script που κάνει scripting όλα όσα χρειάζονται για την δημιουργία τους σε κάποιο άλλο SQL Server Instance

How it works

Ο παρακάτω script generator κάνει χρήση των παρακάτω system tables για να αντληθούν οι απαραίτητες πληροφορίες.

  • msdb.dbo.sysmail_profile
  • msdb.dbo.sysmail_profileaccount
  • msdb.dbo.sysmail_account
  • msdb.dbo.sysmail_server
  • sys.credentials

Χρησιμοποιεί αυτές τις πληροφορίες στις stored procedures

  • msdb.dbo.sysmail_add_profile_sp
  • msdb.dbo.sysmail_add_account_sp
  • msdb.dbo.sysmail_add_profileaccount_sp

για να δημιουργηθούν τα profiles και τα accounts.

Επίσης προσθέτει και τις απαραίτητες ρυθμίσεις για την ενεργοποίηση του database mail στον SQL Server.

NOTE
Να επισημανθεί ότι με το generator αυτόν γίνονται script όλα τα database mail profiles και account για αυτό και το script που δημιουργείται είναι μεγάλο και δεν βολεύει η απεικόνιση του σε grid ή text output. Για αυτό και το βγάζω σε xml ώστε να είναι εύκολο στην ανάγνωση του αλλά και στο copy & paste.


DBmail settings script generator

declare @crlf char(2) = char(13)+char(10);
declare @tab char(1) = char(9);
declare @dbmailscriptout varchar(max)='';

with dbmailset as (
    select 
            p.name as profile_name
        ,    case when p.description is null then 'null' 
            else p.description 
            end as profile_desc
        ,    a.name as account_name
        ,    case when a.description is null then 'null' 
            else a.description 
            end as account_desc
        ,    a.email_address as account_email_address
        ,    case when a.display_name is null then 'nulll'
            else a.display_name
            end as account_display_name
        ,    case when a.replyto_address is null then 'null'
            else a.replyto_address 
            end as account_replyto_address
        ,    s.servername as account_servername
        ,    s.servertype as account_servertype
        ,    cast(s.port as varchar) as account_serverport
        ,    cast(s.use_default_credentials as varchar(5)) as account_use_default_credentials
        ,    cast(s.enable_ssl as varchar) as account_enable_ssl
        ,    case when c.credential_identity is null then 'null'
            else c.credential_identity
            end as account_credential_identity
        ,    case when c.credential_identity is null then 'null'
            else '*** set account password ***'
            end as account_credential_identity_password
        ,    cast(pa.sequence_number as varchar) as profile_account_sequence_number
    from msdb.dbo.sysmail_profile as p 
    inner join msdb.dbo.sysmail_profileaccount as pa on  p.profile_id = pa.profile_id 
    inner join msdb.dbo.sysmail_account as a on pa.account_id = a.account_id  
    left outer join msdb.dbo.sysmail_server as s on a.account_id = s.account_id 
    left outer join sys.credentials as c on s.credential_id = c.credential_id 
)
select 
        @dbmailscriptout+=@crlf+@crlf +'--'+REPLICATE('=',100)+@crlf
        + '-- Profile : ' + profile_name + @crlf
        + '--'+REPLICATE('=',100)+@crlf
        + @crlf
        + '--'+REPLICATE('-',100)+@crlf
        + '-- Create Profile : ' + profile_name + @crlf
        + '--'+REPLICATE('-',100)+@crlf
        + 'if not exists(select * from msdb.dbo.sysmail_profile where  name = ' + quotename(profile_name,'''') + ')' + @crlf 
        + 'begin' + @crlf
        + @tab + 'execute msdb.dbo.sysmail_add_profile_sp' + @crlf
        + replicate(@tab,3) + ' @profile_name = ' + quotename(profile_name,'''') + @crlf
        + replicate(@tab,3) + ',@description = ' + quotename(profile_desc,'''') + ';' + @crlf
        + 'end' + @crlf
        + 'go' + @crlf 
        + @crlf
        + '--'+REPLICATE('-',100)+@crlf
        + '-- Create Account : ' + account_name + @crlf
        + '--'+REPLICATE('-',100)+@crlf
        + 'if not exists(select * from msdb.dbo.sysmail_account where  name = ' + quotename(account_name,'''') + ')' + @crlf 
        + 'begin' + @crlf
        + @tab + 'execute msdb.dbo.sysmail_add_account_sp ' + @crlf
        + replicate(@tab,3) + ' @account_name            = ' + quotename(account_name,'''')  + @crlf
        + replicate(@tab,3) + ',@email_address           = ' + quotename(account_email_address,'''')  + @crlf
        + replicate(@tab,3) + ',@display_name            = ' + quotename(account_display_name,'''')  + @crlf
        + replicate(@tab,3) + ',@replyto_address         = ' + quotename(account_replyto_address,'''')  + @crlf
        + replicate(@tab,3) + ',@description             = ' + quotename(account_desc,'''')  + @crlf
        + replicate(@tab,3) + ',@mailserver_name         = ' + quotename(account_servername,'''')  + @crlf
        + replicate(@tab,3) + ',@mailserver_type         = ' + quotename(account_servertype,'''')  + @crlf
        + replicate(@tab,3) + ',@port                    = ' + account_serverport  + @crlf
        + replicate(@tab,3) + ',@username                = ' + quotename(account_credential_identity,'''')  + @crlf
        + replicate(@tab,3) + ',@password                = ' + quotename(account_credential_identity_password,'''')  + @crlf
        + replicate(@tab,3) + ',@use_default_credentials = ' + account_use_default_credentials  + @crlf
        + replicate(@tab,3) + ',@enable_ssl              = ' + account_enable_ssl  + ';' + @crlf
        + 'end ' + @crlf 
        + 'go' + @crlf 
        + @crlf
        + '--'+REPLICATE('-',100)+@crlf
        + '-- Link Account : ' + account_name + ' with Profile : ' + profile_name + @crlf
        + '--'+REPLICATE('-',100)+@crlf
        + 'if not exists(select * from msdb.dbo.sysmail_profileaccount as pa' + @crlf
        + replicate(@tab,4) + 'inner join msdb.dbo.sysmail_profile as p on pa.profile_id = p.profile_id' + @crlf
        + replicate(@tab,4) + 'inner join msdb.dbo.sysmail_account as a on pa.account_id = a.account_id' + @crlf
        + replicate(@tab,4) + 'where p.name = '+ quotename(profile_name,'''') + ' and a.name = ' + quotename(account_name,'''') +')' + @crlf
        + 'begin' + @crlf
        + @tab + 'execute msdb.dbo.sysmail_add_profileaccount_sp' + @crlf
        + replicate(@tab,3) + ' @profile_name = ' + quotename(profile_name,'''') + @crlf
        + replicate(@tab,3) + ',@account_name = ' + quotename(account_name,'''') + @crlf
        + replicate(@tab,3) + ',@sequence_number = ' + profile_account_sequence_number + ';' + @crlf
        + 'end' + @crlf
        + 'go' + @crlf 
from dbmailset

-- add database mail activation in script
set @dbmailscriptout =   
      '--'+REPLICATE('-',100)+@crlf
    + '-- database mail activation ' + @crlf
    + '--'+REPLICATE('-',100)+@crlf
    + 'use master ' + @crlf
    + 'go' + @crlf
    + 'sp_configure ''show advanced options'',1 ;'+ @crlf
    + 'go' + @crlf 
    + 'reconfigure with override ' + @crlf
    + 'go' + @crlf 
    + 'sp_configure ''database mail xps'',1 ;' + @crlf
    + 'go' + @crlf 
    + 'reconfigure' + + @crlf
    + 'go' + @crlf 
    + @dbmailscriptout

-- script final output
select @dbmailscriptout FOR XML PATH('') 

//Antonios Chatzipavlis

Antonios Chatzipavlis

Antonios Chatzipavlis

Antonios is a Data Solutions Consultant and Trainer. He has been working in IT since 1988. In his career, he has worked as senior developer, IT Manager, Solutions Architect and IT Consultant. Since 1995 he has been devoted on new technologies and software development tools, mainly by Microsoft, either by training company staff and colleagues or assisting them in design, development and implementation as a consultant or chief developer. He has focused in Databases and Data Science since 1995. He specialized in Microsoft SQL Server since version 6.0 in areas like SQL Server Internals, Database Design and Development, Business Intelligence and in 2010 he has started working with Azure Data Platform, NoSQL databases, Big Data Technologies and Machine Learning. He is an active member of many IT communities in Greece, answering colleagues' questions and writing articles in his web site. He is the owner of SQLschool.gr which is a community portal with a lot of information about Microsoft SQL Server. He has been a Microsoft Certified Trainer (MCT) since 2000. Microsoft honored him as MVP on Data Platform due to his activities in SQL Server since 2010. He holds a large number of Microsoft Certifications and Microsoft SQL Server Certifications since version 6.5.


Leave your comment

COMMENT

FULL NAME

EMAIL ADDRESS

We use Gravatar

WEB SITE



captcha


 

Newsletter

If you want to receive updates from us subscribe below with your email.
Follow us in
PASS chapter logo

The Official PASS Local Group for Greece

About us Contact us Terms of Use Privacy Sing in Register
sql school greece logo
© 2010-2020 All rights reserved

This site uses cookies for operational and analytics purposes only. By continuing to browse this site, you agree to their use.