Skip to end of metadata
Go to start of metadata

You are viewing an old version of this page. View the current version.

Compare with Current View Page History

Version 1 Next »

  1. Open SQL Server Management Studio
  2. Click New Query
  3. Type in the Code BelowDeclare @@ProfileName as varchar(50)Declare @@AccountName as varchar(50)Declare @@EmailServer as varchar(50)Declare @@EmailAddress as varchar(100)Declare @@ReplyAddress as varchar(100)Declare @@Username as varchar(100)Declare @@Password as Varchar(50) -*** Please Note that you must**** turn on SQLmail/DBMail in the Surface area configuration --**** in Facets of the SQL server* ****** You must change these Items before running to match local settings *************Set @@ProfileName = 'WB Report Scheduling'Set @@AccountName = 'WB Report Scheduling'Set @@EmailServer = '192.168.2.1'Set @@EmailAddress = 'john.doe@domain_name.com'Set @@ReplyAddress = 'john.doe@domain_name.com'** If You want annon login leave these two as they are. ***** Set @@Username = 'Username' Set @@Password = 'Password'******************************************************************************************* - Create a Database Mail profile EXECUTE msdb.dbo.sysmail_add_profile_sp @profile_name = @@ProfileName, @description = 'Workbench Report Scheduler Email Profile' ; -- Create a Database Mail accountIF @@UserName = 'Username' BEGIN EXECUTE msdb.dbo.sysmail_add_account_sp @account_name = @@AccountName , @description = 'Workbench Report Scheduler Email Account', @email_address = @@EmailAddress, @replyto_address = @@ReplyAddress, @display_name = 'Workbench Report Scheduler', @mailserver_name = @@EmailServer; ENDELSE EXECUTE msdb.dbo.sysmail_add_account_sp @account_name = @@AccountName , @description = 'Workbench Report Scheduler Email Account', @email_address = @@EmailAddress, @replyto_address = @@ReplyAddress, @display_name = 'Workbench Report Scheduler', @mailserver_name = @@EmailServer, @username = @@Username , @password = @@Password ; – Add the account to the profile EXECUTE msdb.dbo.sysmail_add_profileaccount_sp @profile_name = @@ProfileName, @account_name = @@AccountName, @sequence_number =1 ; -- Grant access to the profile to the DBMailUsers role EXECUTE msdb.dbo.sysmail_add_principalprofile_sp @profile_name = 'SQL2005_Email_ProfileName', @principal_id = 0,
  4. Make Sure you change the values in the section with the Set Statements to valid settings you can leave the first two if you like.
  5. Click the Execute button to run.
  6. If the code completes without failure Database mail is now setup
  • No labels