Database Mail - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Database Mail

Author

Topic

skaswani
Starting Member

24 Posts

Posted - 2008-12-07 : 19:55:34

Dear Friends,

how are u all

i am using SQL SERVER 2005 + Database Mail option
====================================================

i have this code

EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'scn',
@recipients = 'scn@merakdemo.com',
@query = 'SELECT refno,name,amount FROM table1 where status = "Pending"' ,
@subject = 'Pending Status',
@attach_query_result_as_file = 1 ;

what i want to do now,

i want to achieve 2 goals,

1st, i have a table table1 which has a field call status and what i want to do is check all pending records and send email to supervisor

supervisor email is in user_info table (this table will have user_id field)

2nd, after 1/2 hour (which i'll manage by SQL agent) i want to mail branch manager and IT manager (which is in Branch_info table and it has a BR_CODE field and table Table1 also have the same field)

please help me in this matter

sample table structure is like this :-

1) Table1

refno, name, amount, user_id, br_code

2) user_info

user_id, user_name

3) branch_info

Br_code, ops_manager_email, it_manager_email

regards

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-07 : 20:40:40

you need to identify and put the required email addresses onto a temporary table. then loop through table to grab each address and then use sp_send_dbmail to sent mail to each. You might need to use dynamic sql for dynamically determining email addresses from the temporary table.

skaswani
Starting Member

24 Posts

Posted - 2008-12-08 : 07:07:20

hi visakh16 and all

i have applied this and its working fine without any error, but the email i record is empty

please guide me

thank

----------------
drop table #mail_merge
go
create table #mail_merge
(
customer_name varchar(20),
address varchar(20),
city varchar(20),
email_addy varchar(30),
email_body varchar(max)
)

go
insert #mail_merge
(
customer_name ,
address,
city ,
email_addy

)

select cust_name,cust_add,city,email from customer

go
update #mail_merge
set email_body= (select * from OpenRowset(Bulk 'F:\Test_Letter.htm', Single_Clob) as letter_template)
go
update #mail_merge
set email_body = replace(email_body,'Customer_Name',customer_name )

go
update #mail_merge
set email_body = replace(email_body,'Customer_Address',address )
go
update #mail_merge
set email_body = replace(email_body,'Customer_City',city )

declare @email_addy varchar(225)
declare @email_body nvarchar(max)
declare mail_merge cursor for select email_addy,email_body from #mail_merge

open mail_merge

fetch next from mail_merge into @email_addy, @email_body
while @@fetch_status = 0
begin

execute msdb.dbo.sp_send_dbmail
@profile_name = 'Scn',
@recipients = @email_addy,
@subject = 'customer info',
@body_format = 'HTML'

fetch next from mail_merge into @email_addy, @email_body

end

close mail_merge
deallocate mail_merge
drop table #mail_merge
go

skaswani
Starting Member

24 Posts

Posted - 2008-12-08 : 08:06:32

i dont know i am unable to delete my last reply,

i think i need to recheck and ree-write my code
then get back to u

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-08 : 08:34:20

quote:
Originally posted by skaswani

i dont know i am unable to delete my last reply,

i think i need to recheck and ree-write my code
then get back to u

yup...try rewriting it and let us know if you face any issues

skaswani
Starting Member

24 Posts

Posted - 2008-12-08 : 10:31:07

hi

i am back:)

i have modified my code as per my need

but yet i dont see any record in email

please help me

drop table branch_info
go
create table branch_info
(
br_code int,
ops_manager_email varchar(40),
it_manager_email varchar(40)
)

drop table user_info
go
create table user_info
(
user_code int,
supervisor_email varchar(40),
br_code int

)

drop table scn_details
go

create table scn_details
(
refno int,
ben_name varchar(30),
amount numeric(8,0),
status varchar(10),
br_code int,
user_code int
)

create table #mail_merge
(
refno int,
ben_name varchar(40),
amount numeric(8,0),
br_code int,
user_code int,
supervisor_email varchar(40),
email_body varchar(max)
)

go
insert #mail_merge
(
refno ,
ben_name,
amount ,
br_code,
user_code,
supervisor_email
)

select s.refno,s.ben_name,s.amount ,s.br_code, s.user_code, u.supervisor_email from scn_details s, user_info u
where s.status = 'Pending' and s.user_code=u.user_code

go

declare @supervisor_email varchar(225)
declare @email_body nvarchar(max)
declare mail_merge cursor for select supervisor_email,email_body from #mail_merge

open mail_merge

fetch next from mail_merge into @supervisor_email, @email_body
while @@fetch_status = 0
begin

execute msdb.dbo.sp_send_dbmail
@profile_name = 'Scn',
@recipients = @supervisor_email,
@subject = 'SCN - pending Records',
@body_format = 'HTML'

fetch next from mail_merge into @supervisor_email, @email_body

end

close mail_merge
deallocate mail_merge
drop table #mail_merge
go

(1 row(s) affected)

output
===========

select * from #mail_merge

150 Sajjad 15000 10 100 scn@merakdemo.com NULL

regards

skaswani
Starting Member

24 Posts

Posted - 2008-12-08 : 12:24:19

dear visakh16

i think i am near to what i want to achieve,

please last thing

create table mail_merge
(
refno int,
ben_name varchar(40),
amount numeric(8,0),
br_code int,
user_code int,
supervisor_email varchar(40),
email_body varchar(max)
)

go
insert mail_merge
(
refno ,
ben_name,
amount ,
br_code,
user_code,
supervisor_email
)

select s.refno,s.ben_name,s.amount ,s.br_code, s.user_code, u.supervisor_email from scn_details s, user_info u
where s.status = 'Pending' and s.user_code=u.user_code

go

declare @supervisor_email varchar(225)
declare @email_body nvarchar(max)
declare mail_merge cursor for select supervisor_email,email_body from mail_merge

open mail_merge

fetch next from mail_merge into @supervisor_email, @email_body
while @@fetch_status = 0
begin

execute msdb.dbo.sp_send_dbmail
@profile_name = 'Scn',
@recipients = @supervisor_email,
@subject = 'SCN - Pending Records',
@body_format = 'HTML',
@query = 'Select * from mail_merge',
@attach_query_result_as_file = 1

fetch next from mail_merge into @supervisor_email, @email_body

end

close mail_merge
deallocate mail_merge
drop table mail_merge
go

===============================

when this code execute it send all the supervisors all the pending records not the records which are related to them,

like a user have one supervisor so his entry should be shown to his supervious only to avoid the mess

i'll eally appriciate if u help me in this regard

thanks

skaswani
Starting Member

24 Posts

Posted - 2008-12-09 : 06:23:15

anyone? help me pls

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-09 : 10:38:09

You're not checking for any condition in query. i think it should be

Select * from mail_merge where {some condition here to filter on supervisor}

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources