Open SQL Server Management Studio
Click New Query
Type in the Code
...
Below:
- Declare @@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
...
account:
IF @@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
END
OTHERWISE
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,
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.
Click the Execute button to run.
If the code completes without failure, The Database mail is now setup.