Post reply

Database Mail Profile

  • April 19, 2010 at 4:08 am

    #219305

    Hi,

    I need to setup database mail configuration profile in more than 50 servers.It will make the job easier if i do that through script. Can you share me the proper T-Sql script or give me the link where i can find the script ?

    Thanks in Advance.

  • April 19, 2010 at 11:21 am

    #1154019

    You might want to search BOL for "Database Mail [SQL Server], configuration scripts".

    There are links to sample procs you could use/modify.


    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • April 19, 2010 at 1:33 pm

    #1154118

    You'll need to change the 'X''s appropriately with your information, but this should work for you:

    IF (SELECT is_broker_enabled FROM sys.databases WHERE name = 'msdb') = 0

    BEGIN

    ALTER DATABASE msdb

    SET ENABLE_BROKER

    WITH ROLLBACK IMMEDIATE

    END

    IF (SELECT CAST(value_in_use AS INT) FROM sys.configurations WHERE name = 'Show Advanced Options') = 0

    BEGIN

    EXEC sp_configure 'Show Advanced Options', 1

    RECONFIGURE WITH OVERRIDE

    END

    IF (SELECT CAST(value_in_use AS INT) FROM sys.configurations WHERE name = 'Database Mail XPs') = 0

    BEGIN

    EXEC sp_configure 'Database Mail XPs', 1

    RECONFIGURE WITH OVERRIDE

    END

    IF NOT EXISTS(SELECT name FROM msdb.dbo.sysmail_account WHERE name = 'X')

    BEGIN

    EXECUTE msdb.dbo.sysmail_add_account_sp

    @account_name = 'X',

    @email_address = 'X',

    @replyto_address = 'X',

    @display_name = 'X',

    @mailserver_name = 'X'

    EXECUTE msdb.dbo.sysmail_add_profile_sp

    @profile_name = 'X'

    EXECUTE msdb.dbo.sysmail_add_profileaccount_sp

    @profile_name = 'X',

    @account_name = 'X',

    @sequence_number = 1

    EXECUTE msdb.dbo.sysmail_add_principalprofile_sp

    @profile_name = 'X',

    @principal_name = 'public',

    @is_default = 1;

    END

  • April 20, 2010 at 3:54 am

    #1154427

    Hi. thanks for your reply and query.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply