Skip to main content

Script out EXISTING database mail profile's settings

 Script which will script out your present SQL Server 2016 Database Mail profiler Setup and Configuration Scripts. It will give you the script to Drop and create Settings For DatabaseMail profiles.

Note: Tested on SQL 2005, to SQL Server 2016 Versions.


USE msdb 
GO 
 
Declare @TheResults varchar(max), 
        @vbCrLf CHAR(2) 
SET @vbCrLf = CHAR(13) + CHAR(10)         
SET @TheResults = ' 
use master 
go 
sp_configure ''show advanced options'',1 
go 
reconfigure with override 
go 
sp_configure ''Database Mail XPs'',1 
--go 
--sp_configure ''SQL Mail XPs'',0 
go 
reconfigure 
go 
' 
SELECT @TheResults = @TheResults  + ' 
-------------------------------------------------------------------------------------------------- 
-- BEGIN Mail Settings ' + p.name + ' 
-------------------------------------------------------------------------------------------------- 
IF NOT EXISTS(SELECT * FROM msdb.dbo.sysmail_profile WHERE  name = ''' + p.name + ''')  
  BEGIN 
    --CREATE Profile [' + p.name + '] 
    EXECUTE msdb.dbo.sysmail_add_profile_sp 
      @profile_name = ''' + p.name + ''', 
      @description  = ''' + ISNULL(p.description,'') + '''; 
  END --IF EXISTS profile 
  ' 
  + 
  ' 
  IF NOT EXISTS(SELECT * FROM msdb.dbo.sysmail_account WHERE  name = ''' + a.name + ''') 
  BEGIN 
    --CREATE Account [' + a.name + '] 
    EXECUTE msdb.dbo.sysmail_add_account_sp 
    @account_name            = ' + CASE WHEN a.name                IS NULL THEN ' NULL ' ELSE + '''' + a.name                  + '''' END + ', 
    @email_address           = ' + CASE WHEN a.email_address       IS NULL THEN ' NULL ' ELSE + '''' + a.email_address         + '''' END + ', 
    @display_name            = ' + CASE WHEN a.display_name        IS NULL THEN ' NULL ' ELSE + '''' + a.display_name          + '''' END + ', 
    @replyto_address         = ' + CASE WHEN a.replyto_address     IS NULL THEN ' NULL ' ELSE + '''' + a.replyto_address       + '''' END + ', 
    @description             = ' + CASE WHEN a.description         IS NULL THEN ' NULL ' ELSE + '''' + a.description           + '''' END + ', 
    @mailserver_name         = ' + CASE WHEN s.servername          IS NULL THEN ' NULL ' ELSE + '''' + s.servername            + '''' END + ', 
    @mailserver_type         = ' + CASE WHEN s.servertype          IS NULL THEN ' NULL ' ELSE + '''' + s.servertype            + '''' END + ', 
    @port                    = ' + CASE WHEN s.port                IS NULL THEN ' NULL ' ELSE + '''' + CONVERT(VARCHAR,s.port) + '''' END + ', 
    @username                = ' + CASE WHEN c.credential_identity IS NULL THEN ' NULL ' ELSE + '''' + c.credential_identity   + '''' END + ', 
    @password                = ' + CASE WHEN c.credential_identity IS NULL THEN ' NULL ' ELSE + '''NotTheRealPassword''' END + ',  
    @use_default_credentials = ' + CASE WHEN s.use_default_credentials = 1 THEN ' 1 ' ELSE ' 0 ' END + ', 
    @enable_ssl              = ' + CASE WHEN s.enable_ssl = 1              THEN ' 1 ' ELSE ' 0 ' END + '; 
  END --IF EXISTS  account 
  ' 
  + ' 
IF NOT EXISTS(SELECT * 
              FROM msdb.dbo.sysmail_profileaccount pa 
                INNER JOIN msdb.dbo.sysmail_profile p ON pa.profile_id = p.profile_id 
                INNER JOIN msdb.dbo.sysmail_account a ON pa.account_id = a.account_id   
              WHERE p.name = ''' + p.name + ''' 
                AND a.name = ''' + a.name + ''')  
  BEGIN 
    -- Associate Account [' + a.name + '] to Profile [' + p.name + '] 
    EXECUTE msdb.dbo.sysmail_add_profileaccount_sp 
      @profile_name = ''' + p.name + ''', 
      @account_name = ''' + a.name + ''', 
      @sequence_number = ' + CONVERT(VARCHAR,pa.sequence_number) + ' ; 
  END  
--IF EXISTS associate accounts to profiles 
--------------------------------------------------------------------------------------------------- 
-- Drop Settings For ' + p.name + ' 
-------------------------------------------------------------------------------------------------- 
/* 
IF EXISTS(SELECT * 
            FROM msdb.dbo.sysmail_profileaccount pa 
              INNER JOIN msdb.dbo.sysmail_profile p ON pa.profile_id = p.profile_id 
              INNER JOIN msdb.dbo.sysmail_account a ON pa.account_id = a.account_id   
            WHERE p.name = ''' + p.name + ''' 
              AND a.name = ''' + a.name + ''') 
  BEGIN 
    EXECUTE msdb.dbo.sysmail_delete_profileaccount_sp @profile_name = ''' + p.name + ''',@account_name = ''' + a.name + ''' 
  END  
IF EXISTS(SELECT * FROM msdb.dbo.sysmail_account WHERE  name = ''' + a.name + ''') 
  BEGIN 
    EXECUTE msdb.dbo.sysmail_delete_account_sp @account_name = ''' + a.name + ''' 
  END 
IF EXISTS(SELECT * FROM msdb.dbo.sysmail_profile WHERE  name = ''' + p.name + ''')  
  BEGIN 
    EXECUTE msdb.dbo.sysmail_delete_profile_sp @profile_name = ''' + p.name + ''' 
  END 
*/ 
  '  
FROM msdb.dbo.sysmail_profile p 
INNER JOIN msdb.dbo.sysmail_profileaccount pa ON  p.profile_id = pa.profile_id 
INNER JOIN msdb.dbo.sysmail_account a         ON pa.account_id = a.account_id  
LEFT OUTER JOIN msdb.dbo.sysmail_server s     ON a.account_id = s.account_id 
LEFT OUTER JOIN sys.credentials c    ON s.credential_id = c.credential_id 
 
   ;WITH E01(N) AS (SELECT 1 UNION ALL SELECT 1 UNION ALL   
                    SELECT 1 UNION ALL SELECT 1 UNION ALL   
                    SELECT 1 UNION ALL SELECT 1 UNION ALL   
                    SELECT 1 UNION ALL SELECT 1 UNION ALL   
                    SELECT 1 UNION ALL SELECT 1), --         10 or 10E01 rows   
         E02(N) AS (SELECT 1 FROM E01 a, E01 b),  --        100 or 10E02 rows   
         E04(N) AS (SELECT 1 FROM E02 a, E02 b),  --     10,000 or 10E04 rows   
         E08(N) AS (SELECT 1 FROM E04 a, E04 b),  --100,000,000 or 10E08 rows   
         --E16(N) AS (SELECT 1 FROM E08 a, E08 b),  --10E16 or more rows than you'll EVER need,   
         Tally(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY N) FROM E08),   
       ItemSplit(   
                 ItemOrder,   
                 Item   
                ) as (   
                      SELECT N,   
                        SUBSTRING(@vbCrLf + @TheResults + @vbCrLf,N + DATALENGTH(@vbCrLf),CHARINDEX(@vbCrLf,@vbCrLf + @TheResults + @vbCrLf,N + DATALENGTH(@vbCrLf)) - N - DATALENGTH(@vbCrLf))   
                      FROM Tally   
                      WHERE N < DATALENGTH(@vbCrLf + @TheResults)   
                      --WHERE N < DATALENGTH(@vbCrLf + @INPUT) -- REMOVED added @vbCrLf   
                        AND SUBSTRING(@vbCrLf + @TheResults + @vbCrLf,N,DATALENGTH(@vbCrLf)) = @vbCrLf --Notice how we find the delimiter   
                     )   
  select   
    row_number() over (order by ItemOrder) as ItemID,   
    Item   
  from ItemSplit

Comments

Post a Comment

Popular posts from this blog

Removing HTML Tags from Text Using SQL Server User-Defined Function

  Introduction: In this blog post, we'll explore how to create and use a SQL Server User-Defined Function (UDF) to remove HTML tags from a text string. This function can be handy when you need to extract plain text from HTML content stored in your database. Creating the Function: First, let's create the SQL Server UDF named udf_StripHTML . This function takes a VARCHAR(MAX) parameter @HTMLText , which represents the HTML content from which we want to remove the tags. It returns a VARCHAR(MAX) value, representing the text stripped of HTML tags. sql SET QUOTED_IDENTIFIER ON GO  CREATE FUNCTION [dbo].[udf_StripHTML] ( @HTMLText VARCHAR (MAX)) RETURNS VARCHAR (MAX) AS BEGIN DECLARE @Start INT   DECLARE @End INT   DECLARE @Length INT   SET @Start = CHARINDEX( '<' , @HTMLText )  SET @End = CHARINDEX( '>' , @HTMLText , CHARINDEX( '<' , @HTMLText ))  SET @Length = ( @End - @Start ) + 1   WHILE @Start > 0 AND @...
Post a Comment
Read more

Using SSRS web services to render a report as a PDF

I have been looking around the net for some decent code which would explain how I could render a report, using SSRS 2008 web services as a PDF.   The need was to extract reports sitting on a SSRS 2008 server sitting on a NT domain on a trusted network, whereas my web server was sitting in a DMZ. Where the only communication allowed by the network admin was port 80. To do this you will need to use the SSRS2008   ReportExecution2005.asmx web service. This could be accesses using the following URL assuming your SSRS server was installed using the default settings. http://YourServerIP/reportserver/reportexecution2005.asmx?wsdl 1.        Create a user on your AD domain with the least amount of privileges (say ReportUser) 2.        Give this account browse access on the reporting server for the desired reports. 3.        To get this working in visual studio 2010 (I am using t...
1 comment
Read more

Creating a Link Server to MYSQL from MSSQL

There might be situations where you need to apply CRUD  operations ( Create,   Read,   Update,   Delete ) on data residing in a MYSQL server database from SQL server. In order to do this you will need to create a link service from SQL server to MYSQL. The Steps are outlined below: Steps: 1. Install the MYSQL ODBC driver on the server with the MSSQL server installation. 2. Enter details for the MYSQL ODBC settings. 3. Create a link service to MYSQL using SSMS ( SQL Server Management Studio) 4. You will need an user account on the mysql server which can perform CRUD operations  along with enable remote connections to the MYSQL installation. (See Troubleshooting steps below) 4. Test the connection. Install MYSQL ODBC Driver  Download   (http://dev.mysql.com/downloads/connector/odbc/) and install the latest MYSQL ODBC driver from the MYSQL server site. Select the version which corresponds to your installation of MSSQL usually the 64bit version. Note This...
Post a Comment
Read more