December 9, 2008 at 4:31 am
Dear All,
how are you
i am using SQL server 2005 Enterprise Edition, and require a help
below is my code
what i want to do i want to send email of pertaining records to their supervisor , but what my code is doing it simple attach a common record list to everyone
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
December 9, 2008 at 7:09 am
try to build a variable for your query
DECLARE @QueryString varchar(500)
SET @QueryString = 'Select * from mail_merge where Supervisor_Email = ' + @Supervisor_Email
Change
@query = 'Select * from mail_merge',
To
@query = @QueryString,
December 9, 2008 at 7:12 am
Change your dbmail-query !
Declare @TheQuery varchar(1000)
Set @TheQuery = 'Select * from dbo.mail_merge where supervisor_email = ''' + @supervisor_email + ''' '
execute msdb.dbo.sp_send_dbmail
@profile_name = 'Scn',
@recipients = @supervisor_email,
@subject = 'SCN - Pending Records',
@body_format = 'HTML',
@query = @TheQuery ,
@attach_query_result_as_file = 1
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 9, 2008 at 7:13 am
Joel is right, you are not providing any criteria to the query so it is doing exactly what you have asked it to do.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
December 9, 2008 at 12:12 pm
hello
thank you so much for replying
but, problem is i am getting duplicate emails , i dont know why,
can u pls check it
thanks,
---
create table mail_merge
(
refnoint,
ben_namevarchar(40),
amountnumeric(8,0),
br_codeint,
user_codeint,
supervisor_emailvarchar(40),
email_bodyvarchar(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
Declare @TheQuery varchar(1000)
Set @TheQuery = 'Select refno , ben_name, amount , br_code, user_code, supervisor_email from dbo.mail_merge where supervisor_email = ''' + @supervisor_email + ''' '
execute msdb.dbo.sp_send_dbmail
@profile_name = 'Scn',
@recipients = @supervisor_email,
@subject = 'SCN - Pending Records' ,
@body_format = 'HTML',
@query = @TheQuery ,
@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
----
December 9, 2008 at 12:21 pm
Please post some test data as well. Please see the links in my signature to see how to post the data to get better answers.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
December 9, 2008 at 3:13 pm
my Tables with sample records
=========================
user_info table structure
=====================
create table user_info
(
user_codeint,
supervisor_emailvarchar(40),
br_codeint
)
sample records
====================
insert into user_info values(100,'abc@merakdemo.com', 10)
insert into user_info values(101,'scn@merakdemo.com', 20)
table scn_details structure
===================
create table scn_details
(
refnoint,
ben_namevarchar(30),
amountnumeric(8,0),
statusvarchar(10),
br_codeint,
user_codeint
)
sample records
====================
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)
mail_merge procedure/method to send email
===================================
create table mail_merge
(
refnoint,
ben_namevarchar(40),
amountnumeric(8,0),
br_codeint,
user_codeint,
supervisor_emailvarchar(40),
email_bodyvarchar(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
Declare @TheQuery varchar(1000)
Set @TheQuery = 'Select refno , ben_name, amount , br_code, user_code, supervisor_email from dbo.mail_merge where supervisor_email = ''' + @supervisor_email + ''' '
execute msdb.dbo.sp_send_dbmail
@profile_name = 'Scn',
@recipients = @supervisor_email,
@subject = 'SCN - Pending Records' ,
@body_format = 'HTML',
@query = @TheQuery ,
@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
==== end ======
when i query this table
select * from mail_merge
results
=======
500,Sajjad,2000000,10,100,abc@merakdemo.com,NULL
501,Zeeshan,5000,20,101,scn@merakdemo.com,NULL
502,Saad,8000,10,100,abc@merakdemo.com,NULL
503,Karim,87000,20,101,scn@merakdemo.com,NULL
504,Ali,7855,20,101,scn@merakdemo.com,NULL
now, the problem
==================
====this code is creating 5 emails,===
(5 row(s) affected)
Mail queued.
Mail queued.
Mail queued.
Mail queued.
Mail queued.
(5 row(s) affected)
that mean.. for each record its sending a email
ie. email likes like this
email one
===========
refno ben_name amount br_code user_code supervisor_email
----------- ---------------------------------------- ---------- ----------- ----------- ----------------------------------------
501 Zeeshan 5000 20 101 scn@merakdemo.com
503 Karim 87000 20 101 scn@merakdemo.com
504 Ali 7855 20 101 scn@merakdemo.com
(3 rows affected)
2nd email
=========
refno ben_name amount br_code user_code supervisor_email
----------- ---------------------------------------- ---------- ----------- ----------- ----------------------------------------
501 Zeeshan 5000 20 101 scn@merakdemo.com
503 Karim 87000 20 101 scn@merakdemo.com
504 Ali 7855 20 101 scn@merakdemo.com
(3 rows affected)
3rd email
===============
refno ben_name amount br_code user_code supervisor_email
----------- ---------------------------------------- ---------- ----------- ----------- ----------------------------------------
501 Zeeshan 5000 20 101 scn@merakdemo.com
503 Karim 87000 20 101 scn@merakdemo.com
504 Ali 7855 20 101 scn@merakdemo.com
(3 rows affected)
note, since i am using outlook for one account so i have'nt downl,oad other email account
please let me know if u required more details
regards
December 10, 2008 at 2:09 am
So your goal is to only send one email per recepient ?
In that case, alter your cursor :
declare mail_merge cursor for select DISTINCT supervisor_email,email_body from mail_merge
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 10, 2008 at 3:58 am
hello
thank you much for helping me
please help me in this code also,
=====purpose ==========
send the same mail to it manager and ops manager after 1 hour if the transaction are in pending
i need to address both managers , i can either put both in TO or one in TO or other in CC
======== error ======
(5 row(s) affected)
Msg 119, Level 15, State 1, Line 16
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'.
================ code start ============
drop table branch_info
go
create table branch_info
(
br_codeint,
ops_manager_emailvarchar(40),
it_manager_emailvarchar(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')
drop table user_info
go
create table user_info
(
user_codeint,
supervisor_emailvarchar(40),
br_codeint
)
insert into user_info values(100,'abc@merakdemo.com', 10)
insert into user_info values(101,'scn@merakdemo.com', 20)
drop table scn_details
go
create table scn_details
(
refnoint,
ben_namevarchar(30),
amountnumeric(8,0),
statusvarchar(10),
br_codeint,
user_codeint
)
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)
create table mail_merge2
(
refnoint,
ben_namevarchar(40),
amountnumeric(8,0),
br_codeint,
user_codeint,
ops_manager_emailvarchar(40),
it_manager_emailvarchar(40),
email_bodyvarchar(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 @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
Declare @TheQuery varchar(1000)
Set @TheQuery = 'Select refno , ben_name, amount , br_code, user_code, ops_manager_email,it_manager_email from dbo.mail_merge 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
fetch next from mail_merge2 into @ops_manager_email,@it_manager_email, @email_body
end
close mail_merge2
deallocate mail_merge2
--drop table mail_merge2
go
======= end ============
thank you
December 10, 2008 at 5:19 am
Simplest is to concatenate both columns in your query to a format your targetusage needs (dbmail)
Declare @email_recepients varchar(225)
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 @email_recepients, @email_body
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 10, 2008 at 10:40 am
Dear when i execute i got this error
(5 row(s) affected)
Msg 137, Level 15, State 2, Line 14
Must declare the scalar variable "@ops_manager_email".
Msg 137, Level 15, State 2, Line 17
Must declare the scalar variable "@ops_manager_email".
Msg 137, Level 15, State 2, Line 24
Must declare the scalar variable "@ops_manager_email".
re-pasting the code to confirm that i am not making any mistake in code
=====================================================
create table mail_merge2
(
refnoint,
ben_namevarchar(40),
amountnumeric(8,0),
br_codeint,
user_codeint,
ops_manager_emailvarchar(40),
it_manager_emailvarchar(40),
email_bodyvarchar(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 @email_recepients varchar(225)
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 @email_recepients, @email_body
while @@fetch_status = 0
begin
Declare @TheQuery varchar(1000)
Set @TheQuery = 'Select refno , ben_name, amount , br_code, user_code, ops_manager_email,it_manager_email from dbo.mail_merge 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
fetch next from mail_merge2 into @ops_manager_email,@it_manager_email, @email_body
end
close mail_merge2
deallocate mail_merge2
--drop table mail_merge2
go
thanks
December 10, 2008 at 1:23 pm
s_kaswani (12/10/2008)
Dear when i execute i got this error(5 row(s) affected)
Msg 137, Level 15, State 2, Line 14
Must declare the scalar variable "@ops_manager_email".
Msg 137, Level 15, State 2, Line 17
Must declare the scalar variable "@ops_manager_email".
Msg 137, Level 15, State 2, Line 24
Must declare the scalar variable "@ops_manager_email".
re-pasting the code to confirm that i am not making any mistake in code
=====================================================
create table mail_merge2
(
refnoint,
ben_namevarchar(40),
amountnumeric(8,0),
br_codeint,
user_codeint,
ops_manager_emailvarchar(40),
it_manager_emailvarchar(40),
email_bodyvarchar(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 @email_recepients varchar(225)
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 @email_recepients, @email_body
while @@fetch_status = 0
begin
Declare @TheQuery varchar(1000)
Set @TheQuery = 'Select refno , ben_name, amount , br_code, user_code, ops_manager_email,it_manager_email from dbo.mail_merge 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
fetch next from mail_merge2 into @ops_manager_email,@it_manager_email, @email_body
end
close mail_merge2
deallocate mail_merge2
--drop table mail_merge2
go
thanks
Re-reading your post pointed me to the caveat ....
use this as cursor statement
declare mail_merge2 cursor for select DISTINCT ops_manager_email + ';'+ it_manager_email,email_body from mail_merge2
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 10, 2008 at 2:14 pm
same error
====================
(5 row(s) affected)
Msg 137, Level 15, State 2, Line 15
Must declare the scalar variable "@ops_manager_email".
Msg 137, Level 15, State 2, Line 18
Must declare the scalar variable "@ops_manager_email".
Msg 137, Level 15, State 2, Line 25
Must declare the scalar variable "@ops_manager_email".
changed as u suggested
can u please test my code
December 10, 2008 at 2:37 pm
i think there is some problem with this code/line
========
declare mail_merge2 cursor for select DISTINCT ops_manager_email + ';'+ it_manager_email ,email_body from mail_merge2
fetch next from mail_merge2 into @email_recepients, @email_body
============
we are decxlaring cursor with 2 variables ops_manager_email and it_manager_email
but fetching into @email_recepients
but problem will be in line : for searching/filter
========================================
Set @TheQuery = 'Select refno , ben_name, amount , br_code, user_code, ops_manager_email,it_manager_email from dbo.mail_merge where ops_manager_email = ''' + @ops_manager_email + ''' '
------
i dont know i am just throwing dice with eyes closed
December 11, 2008 at 9:03 am
please help me
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply