Category Archives: database email

Change SMTP server for all email profiles in SQL Server

Change SMTP server for all email profiles in SQL Server

It happens many times that we set SMTP server for database email profile in SQL Server instance and over the time we change email server and hence we need to change SMTP server in email profile also. We can change it manually from GUI of SSMS but what if you have multiple SQL Server instance and each SQL Server Instance is having multiple profiles? There is a chance we might miss profile while doing it manually so I would prefer to create script which can be run under each SQL Server Instance separately and update each profile under one SQL Server instance. After changing SMTP profile, we can’t keep it as it is before testing it properly so I have divided TSQL in two parts. First script will change SMTP server and second part of the script will shoot an email from each profile automatically so that we can assure our self that each profile working properly. I have used the same script for second part which I have had in “Check all database email profile in SQL Server by using sysmail_profile” article so do visit earlier article, if you haven’t read it.

Note: SMTP server given here are for demonstration purpose only, you have to use your own SMTP credentials there.

USE msdb
GO

–Part 1: Update SMTP profile from smtp to smtp1
UPDATE
sysmail_server
SET
servername = ‘smtp1.google.com’
where
servername=’smtp.google.com’

–Part 2: looping through profiles to shoot an email
DECLARE
@Total int
,@cnt int
,@ProfileName varchar(100)
,@Server varchar(500)
,@Sub varchar(4000)

SET @Total = (SELECT MAX(profile_id) FROM sysmail_profile)
SET @cnt = 1
SET @Server = @@SERVERNAME

WHILE(@cnt <= @Total) BEGIN SET @ProfileName = (SELECT name FROM sysmail_profile WHERE profile_id = @cnt) SET @Sub = @Server + ' - Profile: - ' + @ProfileName +' - ' + cast(@cnt as varchar) + ' of ' + cast(@Total as varchar) IF (@ProfileName is not null) BEGIN PRINT @ProfileName PRINT @Sub EXEC msdb.dbo.sp_send_dbmail @recipients = 'Test@gmail.com' , @subject = @Sub , @body = @Sub , @profile_name = @ProfileName , @body_format = 'HTML' ; END SET @cnt = @cnt + 1 END --Manually confirm "ServerName" is updated or not select * from sysmail_server[/sourcecode] If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Note: Microsoft Books online is a default reference of all articles.

Check all database email profile in SQL Server by using sysmail_profile

Check all database email profile in SQL Server by using sysmail_profile

I used to manage many different SQL Server Instances so I need some scripts to complete my work quickly with efficiency. Recently I had a requirement to check all email profile whether it is able to send an email or not. Well, that SQL Server instance was having approx 18 different database mail profile and send test email from each profile manually is time consuming and there is a small chance to miss any profile while manual testing so rather than choosing manual way, I have developed following TSQL script quickly which get all email profile from MSDB database (BTW, email profile is being stored in MSDB database only) and send an email from every email profile one by one in loop.

I have used “sysmail_profile” system table of MSDB database which stores information about email profile in SQL Server. Here is the script I have developed:

USE msdb
 GO

DECLARE
 @Total int
 ,@cnt int
 ,@ProfileName varchar(50)
 ,@Server varchar(50)
 ,@Sub varchar(400)

SET @Total = (SELECT MAX(profile_id) FROM sysmail_profile)
 SET @cnt = 1

SET @Server = @@SERVERNAME

WHILE(@cnt <= @Total)
 BEGIN
 SET @ProfileName = (select name from sysmail_profile where profile_id = @cnt)

SET @Sub = 'Testing Email From ' + @Server + ' from profile : ' + @ProfileName
 IF (@ProfileName is not null)
 BEGIN

PRINT @ProfileName
 PRINT @Sub

EXEC msdb.dbo.sp_send_dbmail
   @recipients = 'Test@gmail.com'
  , @subject = @Sub
  , @body = @Sub
  , @profile_name = @ProfileName
 , @body_format = 'HTML' ;
 END

SET @cnt = @cnt + 1
 END

As soon as you execute above script, it will send you an email from each database email profile from your SQL Server instance.

Please note that if you don’t have any email profile set in your SQL Server Instance, you won’t get anything.

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Note: Microsoft Books online is a default reference of all articles.