Skip to main content

Script SQL Mail Profiles when migrating databases


The following script obtains all the mail profiles, which could be used to recreate the settings on another server or test server.
.

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