Sending emails from database

Configure Data Base Mail in Sql Server

Step 1

Go to All Programs à Microsoft SQL Server 2008R2/2012 à Click on   SQL Server Management Studio (SSMS) 

Step 2

Enter Server name, Login, Password and select Authentication type

Click on Connect button

Step 3

In Object Explorer, Expand the Management and right click on DataBase Mail and click on Configure DataBase  Mail

Step 4

Select the radio button for Set up Database mail by performing the following tasks

Click on Next

Step 5 Specify profile name and Description and click on Add button

After click on the Add button it will open a new window .In the new window click on New Account

Step 6   Specify Account name, Description and attributes for your SMTP account

1. Separate email id for SQL Server (This is From Email ID)
2. SMTP server name
3. Port number
4. SSL feature should be enable or disable.

In Basic Authentication option enter the same email ID along with the valid password of the email ID.

Click on Ok button

Step 7   Click on Next button

Step 8 Select Default Profile Yes from the drop down box

Click on Next   button

Step 9 Click on Next button

Step 10 Click on Finish button

Click on Close button

Sql Commands

                                                                   Sql server

identity set:
DBCC CHECKIDENT('Admin', RESEED, 0)

check db mails status:

use msdb

select * from sysmail_allitems order by 1 desc

Remove all constraints of a table and then delete the data of table

exec sp_MSforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'  

exec sp_MSforeachtable 'ALTER TABLE ? DISABLE TRIGGER ALL'  

exec sp_MSforeachtable 'DELETE FROM ?'  

exec sp_MSforeachtable 'ALTER TABLE ? CHECK CONSTRAINT ALL'  

exec sp_MSforeachtable 'ALTER TABLE ? ENABLE TRIGGER ALL' 

exec sp_MSforeachtable 'IF NOT EXISTS (SELECT *

    FROM SYS.IDENTITY_COLUMNS

    JOIN SYS.TABLES ON SYS.IDENTITY_COLUMNS.Object_ID = SYS.TABLES.Object_ID

    WHERE SYS.TABLES.Object_ID = OBJECT_ID(''?'') AND SYS.IDENTITY_COLUMNS.Last_Value IS NULL)

    AND OBJECTPROPERTY(OBJECT_ID(''?''), ''TableHasIdentity'') = 1

    DBCC CHECKIDENT (''?'', RESEED, 0) WITH NO_INFOMSGS'

Cursor Declaration

DECLARE @MyCursor CURSOR

SET @MyCursor = CURSOR FAST_FORWARD

FOR

SELECT UserID,EmailID

FROM   UserInfo where isnull(EmailID,'')!=''

OPEN @MyCursor

FETCH NEXT FROM @MyCursor

INTO @UserId,@Toemail

WHILE @@FETCH_STATUS = 0

BEGIN

select @EmailNotif=EmailNotifiaction from NotificationSettings where UserID=@UserId and NotificationTypeID=3

if(@EmailNotif=1)

begin

EXEC msdb.dbo.sp_send_dbmail

 @recipients =@Toemail , 

 @profile_name ='KaiserAdmin',

 @subject ='Bulletin Board', 

 @body = @tableHTML,

 @body_format = 'HTML';

end

 

FETCH NEXT FROM @MyCursor

INTO @UserId,@Toemail

END

CLOSE @MyCursor

DEALLOCATE @MyCursor