October 10, 2012 at 11:32 am
Well, I need through of a Query send Email to different recipients.
in the link below, show the Query
http://img200.imagevenue.com/img.php?image=889928666_Query1_122_432lo.JPG
in the image below show as I wanted to send the email
http://img200.imagevenue.com/img.php?image=889544477_result1_122_260lo.JPG
Please, could you help me. because I did try several ways to do as with cursor and While Loop but I get the result.
I managed to be sent to different recipients but can not get the variable @ body aceopte me to send multiple records in the body of the Email
thanks in advance
October 10, 2012 at 11:35 am
Add the link for see the Query:
http://img200.imagevenue.com/img.php?image=889928666_Query1_122_432lo.JPG
October 10, 2012 at 11:44 am
your image is blocked at my work, some filesharing violation according to our IT guys......can you paste the code you are referencing instead of an image?
Lowell
October 10, 2012 at 12:00 pm
ok this is my Query:
SELECT T3.NoEmpleado AS 'No.Empleado', LTRIM(RTRIM(T3.NombreEmpleado)) + ' ' + LTRIM(RTRIM(T3.ApellidoPaterno)) + ' ' + LTRIM(RTRIM(T3.ApellidoMaterno)) AS 'Nombre Empleado',
T2.IDArea, T2.
FROM dbo.tblCursoArea T1
INNER JOIN tblArea T2 ON T2.IDAREA = T1.IDAREA
INNER JOIN tblEmpleados T3 ON T3.IdArea = T2.IDArea
WHERE T1.PROGRAMADO = 1
The Result is:
719 JOSE ASUNCION FIGUEROA ESTRADA 2jose.a.garcia@nidec-motor.com
766 JAIME SALAZAR CORDERO 2jose.a.garcia@nidec-motor.com
10329JAVIER BRISEÑO GARCIA 2jose.a.garcia@nidec-motor.com
10342FLORINA GOMEZ CEPEDA 1osvaldo.santander@nidec-motor.com
10352SANTIAGO FIDENCIO ZUÑIGA HERNANDEZ 1osvaldo.santander@nidec-motor.com
10355MARIA TERESA CERVANTES GUTIERREZ 1osvaldo.santander@nidec-motor.com
10364EDUARDO RIVERA CRUZ 1osvaldo.santander@nidec-motor.com
10372ALVARO MANUEL MARTINEZ LOMAS 1osvaldo.santander@nidec-motor.com
10384ALBERTO FERNANDO DELGADO LOPEZ 1osvaldo.santander@nidec-motor.com
10434SERGIO ISABEL PERALES HERNANDEZ 1osvaldo.santander@nidec-motor.com
I need send by email the next:
in one email:
to: osvaldo.santander@nidec-motor.com
Subject : 'TEST',
Body:
FLORINA GOMEZ CEPEDA
SANTIAGO FIDENCIO ZUÑIGA HERNANDEZ
MARIA TERESA CERVANTES GUTIERREZ
EDUARDO RIVERA CRUZ
ALVARO MANUEL MARTINEZ LOMAS
ALBERTO FERNANDO DELGADO LOPEZ
SERGIO ISABEL PERALES HERNANDEZ
in the Other email:
to: jose.a.garcia@nidec-motor.com
Subject : 'TEST',
Body:
JOSE ASUNCION FIGUEROA ESTRADA
JAIME SALAZAR CORDERO
JAVIER BRISEÑO GARCIA
How can I make this??
October 10, 2012 at 12:20 pm
this one is tough, because i don't have the tables to test agaisnt.
this MIGHT return the email/string you want to send.
if it does, then this query is what you would use as the cursor to send individual emails.
does this return anything liek what you were thinking of sending?
WITH CTE AS
(
SELECT
DISTINCT T2.
FROM dbo.tblCursoArea T1
INNER JOIN tblArea T2
ON T2.IDAREA = T1.IDAREA
INNER JOIN tblEmpleados T3
ON T3.IdArea = T2.IDArea
WHERE T1.PROGRAMADO = 1
)
SELECT T2.,
Notes = STUFF((
SELECT CHAR(13) + CHAR(10)
+ LTRIM(RTRIM(T3.NombreEmpleado))
+ ' ' + LTRIM(RTRIM(T3.ApellidoPaterno))
+ ' '
+ LTRIM(RTRIM(T3.ApellidoMaterno))
FROM dbo.tblCursoArea T1
INNER JOIN tblArea T2
ON T2.IDAREA = T1.IDAREA
INNER JOIN tblEmpleados T3
ON T3.IdArea = T2.IDArea
WHERE ns. = c.
ORDER BY ns.NoteSeq
FOR XML PATH(''),TYPE).value('.','varchar(max)'),1,2,'') --STUFF removes the preceeding CrLf/CHAR13/10
FROM CTE c
ORDER BY ;
Lowell
October 10, 2012 at 1:20 pm
my friend, I get the next error
Msg 4104, Level 16, State 1, Line 12
The multi-part identifier "T2.EMAIL" could not be bound.
Msg 4104, Level 16, State 1, Line 24
The multi-part identifier "Notes.EMAIL" could not be bound.
Msg 4104, Level 16, State 1, Line 25
The multi-part identifier "Notes.NoteSeq" could not be bound.
Do you know why?
October 10, 2012 at 1:22 pm
i was modifying an example named "notes" to match your data, but without the actual tables, I could not test it for valid objects.
if you can fix the syntax issues...notes and T2 are probably the "c" alias...., it should produce a string and an email.
Lowell
October 10, 2012 at 1:44 pm
Well, the Query return two records, all records of the table before but concatenated.
but I don't know if there help me!!!
October 10, 2012 at 1:58 pm
well now you have the desired contents for the email; now it's just a cursor based on that query:
here's part two of the example, where you send individualized emails:
DECLARE
@isql VARCHAR(2000),
@email VARCHAR(64),
@notes VARCHAR(max)
DECLARE c1 CURSOR FOR SELECT Email,Notes From OurBigger_Query_with_FORXML
OPEN c1
FETCH NEXT FROM c1 INTO @email,@notes
WHILE @@FETCH_STATUS <> -1
BEGIN
declare @body1 varchar(4000)
set @body1 = 'This is a Simple Email Example generated on ' + CONVERT( VARCHAR( 20 ), GETDATE(), 113 ) +
' to demonstrate sending a basic notification' + @notes
--this assumes a profile was set as "default", so i don't have to explicitly specify which one to use. else you get this error:
/*
Msg 14636, Level 16, State 1, Procedure sp_send_dbmail, Line 112
No global profile is configured. Specify a profile name in the @profile_name parameter.
*/
EXEC msdb.dbo.sp_send_dbmail
--@profile_name='Stormrage DBMail',
@recipients=@email,
@subject = 'Simple Email Example',
@body = @body1,
@body_format = 'HTML'
--@body_format = 'TEXT'
FETCH NEXT FROM c1 INTO @email,@notes
END
CLOSE c1
DEALLOCATE c1
Lowell
October 10, 2012 at 2:24 pm
ok my friend,
Make sure you know that the NOTE field that returns records, repeat the result
Like this:
JOSE ASUNCION FIGUEROA ESTRADA JOSE ASUNCION FIGUEROA ESTRADA JOSE ASUNCION FIGUEROA ESTRADA JOSE ASUNCION FIGUEROA ESTRADA JOSE ASUNCION FIGUEROA ESTRADA JOSE ASUNCION FIGUEROA ESTRADA JOSE ASUNCION FIGUEROA ESTRADA JOSE ASUNCION FIGUEROA ESTRADA JOSE ASUNCION FIGUEROA ESTRADA JOSE ASUNCION FIGUEROA ESTRADA JOSE ASUNCION FIGUEROA ESTRADA JOSE ASUNCION FIGUEROA ESTRADA JOSE ASUNCION FIGUEROA ESTRADA JOSE ASUNCION FIGUEROA ESTRADA JOSE ASUNCION FIGUEROA ESTRADA JAIME SALAZAR CORDERO JAIME SALAZAR CORDERO JAIME SALAZAR CORDERO JAIME SALAZAR CORDERO JAIME SALAZAR CORDERO JAIME SALAZAR CORDERO JAIME SALAZAR CORDERO JAIME SALAZAR CORDERO JAIME SALAZAR CORDERO JAIME SALAZAR CORDERO JAIME SALAZAR CORDERO JAIME SALAZAR CORDERO JAIME SALAZAR CORDERO JAIME SALAZAR CORDERO JAIME SALAZAR CORDERO JAVIER BRISEÑO GARCIA JAVIER BRISEÑO GARCIA JAVIER BRISEÑO GARCIA JAVIER BRISEÑO GARCIA JAVIER BRISEÑO GARCIA JAVIER BRISEÑO GARCIA JAVIER BRISEÑO GARCIA JAVIER BRISEÑO GARCIA JAVIER BRISEÑO GARCIA JAVIER BRISEÑO GARCIA JAVIER BRISEÑO GARCIA JAVIER BRISEÑO GARCIA JAVIER BRISEÑO GARCIA JAVIER BRISEÑO GARCIA JAVIER BRISEÑO GARCIA
Do you know why??
October 10, 2012 at 2:35 pm
change your query to select distinct email, notes.
when you merge them with FOR XML, you often have duplicates.
Lowell
October 10, 2012 at 3:33 pm
My friend,
Now it's very goood,
Do you know as to show result of the NOTE field vertically in the Email Body?
for example as this one:
JOSE ASUNCION FIGUEROA ESTRADA
JAIME SALAZAR CORDERO
JAVIER BRISEÑO GARCIA
Thank so much!!!
October 10, 2012 at 4:21 pm
come on, you should know this.
html does not respect whitespace!
find and replace char(13)(char10) with <br />, and isnert <br /> wherever else you want new lines to appear.
sanmon_11 (10/10/2012)
My friend,Now it's very goood,
Do you know as to show result of the NOTE field vertically in the Email Body?
for example as this one:
JOSE ASUNCION FIGUEROA ESTRADA
JAIME SALAZAR CORDERO
JAVIER BRISEÑO GARCIA
Thank so much!!!
Lowell
October 11, 2012 at 6:42 am
ok my friend, 🙂
thank so much for your time and support
excellent contribution!!!
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply