Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Send email to different recipients Expand / Collapse
Author
Message
Posted Wednesday, October 10, 2012 11:32 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, November 30, 2012 7:51 AM
Points: 13, Visits: 34
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
Post #1371060
Posted Wednesday, October 10, 2012 11:35 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, November 30, 2012 7:51 AM
Points: 13, Visits: 34
Add the link for see the Query:



http://img200.imagevenue.com/img.php?image=889928666_Query1_122_432lo.JPG
Post #1371062
Posted Wednesday, October 10, 2012 11:44 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:33 PM
Points: 12,953, Visits: 32,483
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1371065
Posted Wednesday, October 10, 2012 12:00 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, November 30, 2012 7:51 AM
Points: 13, Visits: 34
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.[EMAIL]
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 2 jose.a.garcia@nidec-motor.com
766 JAIME SALAZAR CORDERO 2 jose.a.garcia@nidec-motor.com
10329 JAVIER BRISEÑO GARCIA 2 jose.a.garcia@nidec-motor.com
10342 FLORINA GOMEZ CEPEDA 1 osvaldo.santander@nidec-motor.com
10352 SANTIAGO FIDENCIO ZUÑIGA HERNANDEZ 1 osvaldo.santander@nidec-motor.com
10355 MARIA TERESA CERVANTES GUTIERREZ 1 osvaldo.santander@nidec-motor.com
10364 EDUARDO RIVERA CRUZ 1 osvaldo.santander@nidec-motor.com
10372 ALVARO MANUEL MARTINEZ LOMAS 1 osvaldo.santander@nidec-motor.com
10384 ALBERTO FERNANDO DELGADO LOPEZ 1 osvaldo.santander@nidec-motor.com
10434 SERGIO ISABEL PERALES HERNANDEZ 1 osvaldo.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??





Post #1371071
Posted Wednesday, October 10, 2012 12:20 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:33 PM
Points: 12,953, Visits: 32,483
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.[EMAIL]
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.[EMAIL],
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.[EMAIL] = c.[EMAIL]
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 [EMAIL];



Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1371077
Posted Wednesday, October 10, 2012 1:20 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, November 30, 2012 7:51 AM
Points: 13, Visits: 34
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?
Post #1371091
Posted Wednesday, October 10, 2012 1:22 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:33 PM
Points: 12,953, Visits: 32,483
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1371092
Posted Wednesday, October 10, 2012 1:44 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, November 30, 2012 7:51 AM
Points: 13, Visits: 34
Well, the Query return two records, all records of the table before but concatenated.

but I don't know if there help me!!!






Post #1371099
Posted Wednesday, October 10, 2012 1:58 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:33 PM
Points: 12,953, Visits: 32,483
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1371104
Posted Wednesday, October 10, 2012 2:24 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, November 30, 2012 7:51 AM
Points: 13, Visits: 34
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??
Post #1371114
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse