SEND DBMAIL USING QUARYS AND WIZARD LINK and ONE PRCEDURE EXAMPLE

BY USING QUARY TO SEND DBMAIL

---database mail
----right click on servre select facets the facet select 'surface area configuration'
    inthis options we enable true%
---- we check configure advanced options
    sp_configure

sp_configure 'show advanced options',1
reconfigure

----in advanced options we take sql mail xps and enable it

sp_configure 'SQL Mail XPs',1
reconfigure

-----dbmails done with only msdb system database
-----add profile name (any name),we select sysmail_add_profile_sp the click alt+f1 see
     the optinal parameter names and give the name

sysmail_add_profile_sp @profile_name='babji',@description='test2'

---add princepalprofile name

sysmail_add_principalprofile_sp @profile_name='babji',
@principal_name='public',
@is_default=1

---add account name ,where account name and profile name should be same,
    display name(any),replay_address(same mail_id or other valid mail id),
    mail_server(here deffinetly we will give proper mail server ,below i gave
    microsoftserver and anybody try with gamil server etc )

sysmail_add_account_sp @account_name='babji',@email_address='babji.reddy1@gmail.com'
,@display_name='junnu',@replyto_address='babji.reddy1@gmail.com',
@mailserver_name='tk3ptmsg01.parttest.extranettest.microsoft.com'

----add profile name to account name with sequence no

sysmail_add_profileaccount_sp @profile_name='babji',@account_name='babji',@sequence_number=1

----then we will send mail here body (mail body information),and subject

sp_send_dbmail @profile_name='babji',@recipients='babji.reddy1@gmail.com',
@body='vvasd',@subject='testsqlmail'

------then we check maill send or not by using msdb system detabase views

select * from dbo.sysmail_sentitems
select * from dbo.sysmail_faileditems
select * from dbo.sysmail_allitems
 select * from dbo.sysmail_event_log

BY USING WIZARD TO SEND MAIL LINK:

http://www.idevelopment.info/data/SQLServer/DBA_tips/Database_Administration/DBA_20.shtml           




SEND DBMAIL EXAMPLE:

   create table tbl_insurance(ins_id int identity ,name varchar(30), 
   vehicletype varchar(20), mail_id varchar(50),phonenu bigint,exp_date date)

insert into tbl_insurance values('chintu','lenova','chinnu@gmail.com',9652067541,'06-30-2015'),
                                ('minnu','yamaha','meganareddy.aa@gmail.com',8096955421,'09-24-2015'),
                                 ('bhavani','car','bhavanireddy.mm@gmail.com',9703435671,'06-10-2014'),
                                 ('babji','yamaha','babji.reddy1@gmail.com',7396042168,'06-20-2014')
   select * from tbl_insurance                               

  select * from tbl_insurance where exp_date between '06-01-2014' and '06-30-2014'


----- by using dbsend_mail send mail to this month exriry date candidates or from now any exripy       candidates r there then send mail
       go
        create proc usp_expdate_sendmail
         as
         begin
         declare @exp_date varchar(50)
 declare @mail_id varchar(50)
  declare @body varchar(max)
 declare @veh_type varchar(50)    
  declare @name varchar(20)
 declare @str varchar(max)
 declare c1 cursor forselect mail_id,exp_date from  tbl_insurance where exp_date                                                between getdate() and dateadd(day,30,getdate());

        open c1

fetch next from c1 into @exp_date,@mail_id

      while(@@FETCH_STATUS=0)

                 BEGIN
    set @body='ur vehicle insurence is expires on :' +@exp_date;
    exec msdb.dbo.sp_send_dbmail@profile_name='bhavani',@recipients=@mail,@body=@body
    fetch next from c1 into @exp_date,@mail_id
        end
   
close c1
deallocate c1
 end