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

Troubleshooting Database Mail: General steps

SQL Server 2008 R2 Other Versions SQL Server "Denali" SQL Server 2008 SQL Server 2005 Troubleshooting Database Mail involves checking the following general areas of the Database Mail system. These procedures are presented in a logical order, but can be evaluated in any order. To determine if Database Mail is enabled In SQL Server Management Studio, connect to an instance of SQL Server by using a query editor window, and then execute the following code: Copy sp_configure 'show advanced', 1; GO RECONFIGURE; GO sp_configure; GO In the results pane, confirm that the run_value for Database Mail XPs is set to 1 . If the run_value is not 1 , Database Mail is not enabled. Database Mail is not automatically enabled to reduce the number of features available for attack by a malicious user. For more information, see Understanding Surface Area Configuration . If you decide that it is appropriate to enable Database Mail, execute the fo...
Post a Comment
Read more

Integrating MIGS eGate Payment gateway using C# for Oz businesses (Server-Hosted Payment method)

If you ever wanted to include the eGate payment gateway in your ASP.Net site and want to save yourself the time in creating the class, here’s how to do it. First things first you will need a merchant account with ANZ who will give you the following ·          Merchant Account ·          User name ·          password The details could be downloaded from the ANZ web site http://www.anz.com.au/australia/business/merchant/DownloadDevKit.asp I am doing this for an Aus base client thus the process might be different to your own country. Please check with the issuing bank for details. Have a read of the Merchant Admin guide. Login to the merchant account and create an operator ID, Login as the operator and obtain the access code. I will not going to the details as this is all documented in the admin guide. What you will need for the implementation would...
3 comments
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