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

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 the Premium edition)   Right click on your project and add a service reference to the above web serv
1 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 be vpc_AccessCode vpc_Merchan MIGS gateway URL:https://migs.mastercard.com.au/vpcdps The site has numerous
3 comments
Read more

How to Query SQL server for T-SQL Calendar using GetFullMonth using Pivot

After listing sql calendar date values in a month as rows, we need to display them now in a format similar to frequently used in printed calendars. The day names are listed as column names and dates are shown under the related columns. The first solution is supplied by Plamen Ratchev from SQLStudio.com using the t-sql PIVOT. Since I liked the t-sql code he coded much I want to share it with you here. SELECT [Sunday], [Monday], [Tuesday], [Wednesday], [Thursday], [Friday], [Saturday] FROM ( SELECT DATEPART(WEEK, [date]) AS week_number, [date], [days] FROM dbo.getFullmonth('20080229') ) AS Dates PIVOT ( MIN([date]) FOR [days] IN ( [Sunday], [Monday], [Tuesday], [Wednesday], [Thursday], [Friday], [Saturday] ) ) AS Pivots And the output of this sql pivot statement will return the following result set as output which is displaying sql dates in a calendar format.
2 comments
Read more