December 12, 2008 at 12:33 am
As you'll see I've commented the send_dbmail , you must un-comment it.
Test it first.
drop table branch_info
go
create table branch_info
(
br_code int,
ops_manager_email varchar(40),
it_manager_email varchar(40)
)
insert into branch_info values (10,'ops10@merakdemo.com.com','it10@merakdemo.com')
insert into branch_info values (20,'ops20@merakdemo.com.com','it20@merakdemo.com')
go
drop table user_info
go
create table user_info
(
user_code int,
supervisor_email varchar(40),
br_code int
)
insert into user_info values(100,'abc@merakdemo.com', 10)
insert into user_info values(101,'scn@merakdemo.com', 20)
go
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
)
insert into scn_details values(500,'Sajjad',2000000,'Pending',10,100)
insert into scn_details values(501,'Zeeshan',5000,'Pending',20,101)
insert into scn_details values(502,'Saad',8000,'Pending',10,100)
insert into scn_details values(503,'Karim',87000,'Pending',20,101)
insert into scn_details values(504,'Ali',7855,'Pending',20,101)
go
create table mail_merge2
(
refno int,
ben_name varchar(40),
amount numeric(8,0),
br_code int,
user_code int,
ops_manager_email varchar(40),
it_manager_email varchar(40),
email_body varchar(max)
)
go
insert mail_merge2
(
refno ,
ben_name,
amount ,
br_code,
user_code,
ops_manager_email,
it_manager_email
)
select a.refno, a.ben_name, a.amount, a.br_code , a.user_code,
b.ops_manager_email, b.it_manager_email
from scn_details a, branch_info b
where a.br_code=b.br_code and a.status = 'Pending'
go
declare @ops_manager_email varchar(225)
declare @it_manager_email varchar(225)
Declare @emailrecepients varchar(255)
declare @email_body nvarchar(max)
declare mail_merge2 cursor for select DISTINCT ops_manager_email, it_manager_email, email_body from mail_merge2
open mail_merge2
fetch next from mail_merge2 into @ops_manager_email,@it_manager_email, @email_body
while @@fetch_status = 0
begin
Set @emailrecepients = @ops_manager_email + ';' + @it_manager_email
Declare @TheQuery varchar(1000)
Set @TheQuery = 'Select refno , ben_name, amount , br_code, user_code, ops_manager_email,it_manager_email from dbo.mail_merge2 where ops_manager_email = ''' + @ops_manager_email + ''' '
--execute msdb.dbo.sp_send_dbmail
--@profile_name = 'Scn',
--@recipients = @ops_manager_email,@it_manager_email,
--@subject = 'SCN - Pending Records' ,
--@body_format = 'HTML',
--@query = @TheQuery ,
--@attach_query_result_as_file = 1
print @emailrecepients
exec (@TheQuery)
fetch next from mail_merge2 into @ops_manager_email,@it_manager_email, @email_body
end
close mail_merge2
deallocate mail_merge2
go
--drop table mail_merge2
go
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
December 12, 2008 at 9:49 am
hi
tested above code, its working perfect
results
======================
(5 row(s) affected)
ops10@merakdemo.com.com;it10@merakdemo.com
refno ben_name amount br_code user_code ops_manager_email it_manager_email
----------- ---------------------------------------- --------------------------------------- ----------- ----------- ---------------------------------------- ----------------------------------------
500 Sajjad 2000000 10 100 ops10@merakdemo.com.com it10@merakdemo.com
502 Saad 8000 10 100 ops10@merakdemo.com.com it10@merakdemo.com
(2 row(s) affected)
ops20@merakdemo.com.com;it20@merakdemo.com
refno ben_name amount br_code user_code ops_manager_email it_manager_email
----------- ---------------------------------------- --------------------------------------- ----------- ----------- ---------------------------------------- ----------------------------------------
501 Zeeshan 5000 20 101 ops20@merakdemo.com.com it20@merakdemo.com
503 Karim 87000 20 101 ops20@merakdemo.com.com it20@merakdemo.com
504 Ali 7855 20 101 ops20@merakdemo.com.com it20@merakdemo.com
(3 row(s) affected)
but when i try un comment and execute sp email
error
============
(5 row(s) affected)
Must pass parameter number 3 and subsequent parameters as '@name = value'. After the form '@name = value' has been used, all subsequent parameters must be passed in the form '@name = value'.
December 14, 2008 at 2:37 am
hello ALZDBA
please help me
December 14, 2008 at 12:50 pm
Off course the @recipients parameter still needed modification ...
while @@fetch_status = 0
begin
Set @emailrecepients = @ops_manager_email + ';' + @it_manager_email
Declare @TheQuery varchar(1000)
Set @TheQuery = 'Select refno , ben_name, amount , br_code, user_code, ops_manager_email,it_manager_email from dbo.mail_merge2 where ops_manager_email = ''' + @ops_manager_email + ''' '
execute msdb.dbo.sp_send_dbmail
@profile_name = 'Scn',
@recipients = @emailrecepients,
@subject = 'SCN - Pending Records' ,
@body_format = 'HTML',
@query = @TheQuery ,
@attach_query_result_as_file = 1
print @emailrecepients
exec (@TheQuery)
fetch next from mail_merge2 into @ops_manager_email,@it_manager_email, @email_body
end
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
December 17, 2008 at 8:48 am
thank you so much, sorry my pc was out of other so i couldnot check the code
thanks!:D
Viewing 5 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply