December 31, 2010 at 9:58 am
Hi guys!
I´ve a table with several records;
CREATE TABLE [dbo].[correosaclientes](
[cliente] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AI NULL,
[ningreso] [varchar](max) COLLATE Modern_Spanish_CI_AS NOT NULL,
[proyecto] [varchar](max) COLLATE Modern_Spanish_CI_AS NULL,
[fingreso] [varchar](max) COLLATE Modern_Spanish_CI_AS NOT NULL,
[correocliente] [varchar](max) COLLATE Modern_Spanish_CI_AS NULL,
[fechainsert] [datetime] NULL) ON [PRIMARY]
I want to send email to every client ('cliente') with every income ('ningreso') accordin to his email ('correocliente').
I can easily send emails to client PER income but a i need to send ONE email at the end of the day with every income in de body of msdb.dbo.sp_send_dbmail.
This are records from the table.
cliente ningreso proyecto fingreso correocliente
96937580 78947 Control1 Dic 30 2010 12:00PM maila@domain1.cl
96937580 78948 Control2 Dic 30 2010 12:00PM maila@domain1.cl
86547900 78949 Control3 Dic 30 2010 2:15PM mailb@domain2.cl
86547900 78950 Control4 Dic 30 2010 2:15PM mailb@domain2.cl
So! The email for the cliente 96937580 must look like this;
'Numero Ingreso: 78947
Proyecto: control1
Numero Ingreso: 78948
Proyecto: control2'
any ideas!?!?!
December 31, 2010 at 11:39 pm
igngua (12/31/2010)
Hi guys!I´ve a table with several records;
CREATE TABLE [dbo].[correosaclientes]([cliente] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AI NULL,
[ningreso] [varchar](max) COLLATE Modern_Spanish_CI_AS NOT NULL,
[proyecto] [varchar](max) COLLATE Modern_Spanish_CI_AS NULL,
[fingreso] [varchar](max) COLLATE Modern_Spanish_CI_AS NOT NULL,
[correocliente] [varchar](max) COLLATE Modern_Spanish_CI_AS NULL,
[fechainsert] [datetime] NULL) ON [PRIMARY]
I want to send email to every client ('cliente') with every income ('ningreso') accordin to his email ('correocliente').
I can easily send emails to client PER income but a i need to send ONE email at the end of the day with every income in de body of msdb.dbo.sp_send_dbmail.
This are records from the table.
cliente ningreso proyecto fingreso correocliente
96937580 78947 Control1 Dic 30 2010 12:00PM maila@domain1.cl
96937580 78948 Control2 Dic 30 2010 12:00PM maila@domain1.cl
86547900 78949 Control3 Dic 30 2010 2:15PM mailb@domain2.cl
86547900 78950 Control4 Dic 30 2010 2:15PM mailb@domain2.cl
So! The email for the cliente 96937580 must look like this;
'Numero Ingreso: 78947
Proyecto: control1
Numero Ingreso: 78948
Proyecto: control2'
any ideas!?!?!
Absolutely. The first idea is how to get answers more quickly. Read the article at the first link in my signature line below for how to get good, coded answers more quickly. 😉
Here's what your post would look like if you read the article I just told you about...
--===== Conditionally drop the test table to make reruns easier
IF OBJECT_ID('TempDB..#correosaclientes','U') IS NOT NULL
DROP TABLE #correosaclientes
;
--===== Create the test table.
CREATE TABLE #correosaclientes
(
cliente varchar(50) COLLATE SQL_Latin1_General_CP1_CI_AI NULL,
ningreso varchar(max) COLLATE Modern_Spanish_CI_AS NOT NULL,
proyecto varchar(max) COLLATE Modern_Spanish_CI_AS NULL,
fingreso varchar(max) COLLATE Modern_Spanish_CI_AS NOT NULL,
correocliente varchar(max) COLLATE Modern_Spanish_CI_AS NULL,
fechainsert datetime NULL
)
;
--===== Populate the test table with data
INSERT INTO #correosaclientes
(cliente,ningreso,proyecto,fingreso,correocliente)
SELECT '96937580','78947','Control1','Dic 30 2010 12:00PM','maila@domain1.cl' UNION ALL
SELECT '96937580','78948','Control2','Dic 30 2010 12:00PM','maila@domain1.cl' UNION ALL
SELECT '86547900','78949','Control3','Dic 30 2010 2:15PM','mailb@domain2.cl' UNION ALL
SELECT '86547900','78950','Control4','Dic 30 2010 2:15PM','mailb@domain2.cl'
;
A word on the structure of your table... I'm curious as to why you're using CHAR(50) for the cliente column. Wouldn't a nice little INT do the trick? Same goes for ningreso... VARCHAR(MAX) looks like overkill. And overkill it is for the rest of the stuff. It just doesn't look like VARCHAR(MAX) is needed on any of that data... especially on dates. Having numeric and date data in VARCHAR columns also makes it a real PITA to sort on.
Also, you have six columns in your test table but you only populate 5 of them. Fortunately, that doesn't affect the outcome of this problem.
Here's one solution that does the trick for you. I made it so it would sort the values of ningreso numerically. Like I said, using VARCHAR for everything makes sorting both tougher and slower. 😉
--===== Declare the body variable as large as we can because we know it could get
-- much larger than VARCHAR(8000) in a hurry.
DECLARE @Body VARCHAR(MAX)
;
--===== Using an untitled XML path, concatenate the data together in the desired format
-- which includes some line-feed characters.
SELECT @Body = CAST(
(
SELECT 'Numero Ingreso : ' + ningreso + CHAR(10)
+ 'Proyecto : ' + proyecto + CHAR(10)
FROM #correosaclientes
ORDER BY CAST(ningreso AS INT)
FOR XML PATH(''),TYPE
)
AS VARCHAR(MAX))
;
--===== Display what the output will look like in the message window
PRINT @Body
;
Here's what I get for an output...
Numero Ingreso : 78947
Proyecto : Control1
Numero Ingreso : 78948
Proyecto : Control2
Numero Ingreso : 78949
Proyecto : Control3
Numero Ingreso : 78950
Proyecto : Control4
--Jeff Moden
January 1, 2011 at 7:23 pm
I appreciate all your advices and your answer.
I´ve changed the data type of the columns:
CREATE TABLE [dbo].[correosaclientes](
[cliente] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AI NOT NULL,
[ningreso] [int] NOT NULL,
[proyecto] [char](100) COLLATE Modern_Spanish_CI_AS NULL,
[fingreso] [datetime] NOT NULL,
[correocliente] [varchar](250) COLLATE Modern_Spanish_CI_AS NULL,
[fechainsert] [datetime] NULL
)
I´ve used your idea and i get the following error;
'Msg 245, Level 16, State 1, Line 7
Conversion failed when converting the varchar value 'Numero ingreso:' to data type int.'
Thanks in advance.
January 2, 2011 at 4:51 pm
igngua (1/1/2011)
I appreciate all your advices and your answer.I´ve changed the data type of the columns:
CREATE TABLE [dbo].[correosaclientes]([cliente] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AI NOT NULL,
[ningreso] [int] NOT NULL,
[proyecto] [char](100) COLLATE Modern_Spanish_CI_AS NULL,
[fingreso] [datetime] NOT NULL,
[correocliente] [varchar](250) COLLATE Modern_Spanish_CI_AS NULL,
[fechainsert] [datetime] NULL
)
I´ve used your idea and i get the following error;
'Msg 245, Level 16, State 1, Line 7
Conversion failed when converting the varchar value 'Numero ingreso:' to data type int.'
Thanks in advance.
Heh... so fix it. 😉 It's a simple conversion problem in the code which appeared simply because you changed data types.
--Jeff Moden
January 5, 2011 at 5:45 pm
Thanks jeff!!
i´ve been testing all week and i finally got what i needed.
I will post it tomorrow and hopefully you and some others will give me your advices to improve my skills.
thanks a lot.
January 6, 2011 at 7:51 am
Sendmail will send ONE email for each client (@client, cliente) with a valid email (@correocliente,correocliente).Also it will send one email to each salesperson notifying them that exist a record for a sold product without email contact.
ALTER procedure [dbo].[sendmail]
as
declare @cliente varchar (10)
declare @ningreso varchar(10)
declare @proyecto varchar(100)
declare @fingreso datetime
declare @correocliente varchar(250)
declare @correovendedor varchar (250)
declare @bodyc varchar (max)
declare @bodyf varchar (max)
declare @bodyz varchar (max)
declare @bodyq varchar (max)
declare @nombrecliente varchar(60)
declare micursor cursor for
select cliente,ningreso,proyecto,fingreso,correocliente
from correosaclientes
open micursor
fetch next from micursor
into @cliente, @ningreso, @proyecto,@fingreso,@correocliente
while @@fetch_status = 0
begin
--delete every record from before yesterday
delete FROM correosaclientes
WHERE fingreso < DATEADD(day, DATEDIFF(day, 0, GETDATE()),-1)
--evaluates if the product has a valid email
IF exists (select * from correosaclientes where correocliente is null or len(correocliente)<6)
--send one email to each salesperson
select top(1)@cliente = cliente
FROM correosaclientes where correocliente is null or len(correocliente)<6
select @nombrecliente = nomaux
from soporte.lab0708.softland.cwtauxi where @cliente = codaux
select @correovendedor = (select usrmail + ';' from hl_seg.dbo.usuario
where rolid like 'vent%' for xml path(''))--msdb.dbo.sp_send_dbmail no acepta el resultado como recipient sin esto
SELECT @Bodyz = CAST(
(
SELECT
+ 'Cliente : ' + @nombrecliente + CHAR(10)
+ 'Código Cliente: ' + cliente + CHAR(10)
+ 'Numero Ingreso : ' + ningreso + CHAR(10)
+ 'Fecha Ingreso : ' + fingreso + char(10)
+ 'Fecha Muestreo : ' + fsampled + char(10)
+ 'Proyecto : ' + proyecto + CHAR(10)
+ char(10)
FROM correosaclientes
where correocliente is null or len(correocliente)<6
ORDER BY CAST(ningreso AS INT)
FOR XML PATH(''),TYPE
)
AS VARCHAR(MAX))
---------------------------------------
set @bodyq = 'Estimado ejecutivo comercial,
Les informamos que se ha registrado el ingreso de las siguientes muestras sin contacto en su cotización.
Se les solicita actualizar la información lo antes posible, estos clientes no están recibiendo los avisos
de recepción de muestras.
@bodyz
'
--------------------------------
SET @bodyq = REPLACE(@bodyq,'@nombrecliente',@nombrecliente)
SET @bodyq = REPLACE(@bodyq,'@bodyz',@bodyz)
exec msdb.dbo.sp_send_dbmail @profile_name = 'pruebaingreso',
@recipients = @correovendedor,
@subject = 'Ingreso de muestras sin contacto en la cotización',
@body = @bodyq
delete from correosaclientes where correocliente is null or len(correocliente)<6
--Evaluates if a prodcutc has a valid email
IF exists (select * from correosaclientes where correocliente is not null or len(correocliente)>6)
--send one email to each client
select top(1)@cliente = cliente FROM correosaclientes
select @correocliente = (select top(1)correocliente + ';' from correosaclientes
for xml path(''))--msdb.dbo.sp_send_dbmail no acepta el resultado como recipient sin esto
SELECT @Bodyc = CAST(
(
SELECT
+'Numero Ingreso : ' + ningreso + CHAR(10)
+ 'Fecha Ingreso : ' + fingreso + char(10)
+ 'Fecha Muestreo : ' + fsampled + char(10)
+ 'Proyecto : ' + proyecto + CHAR(10)
+ char(10)
FROM correosaclientes
where cliente in (select top(1)cliente from correosaclientes)
ORDER BY CAST(ningreso AS INT)
FOR XML PATH(''),TYPE
)
AS VARCHAR(MAX))
----------------------
set @bodyf = 'Estimado Cliente,
Le informamos la recepción de sus muestras, indicadas en el siguiente listado:
@bodyc
'
---------------------------
SET @bodyf = REPLACE(@bodyf,'@bodyc',@bodyc)
exec msdb.dbo.sp_send_dbmail @profile_name = 'pruebaingreso',
@recipients = @correocliente,
@subject = 'Estimado cliente se han recepcionado muestras',
@body = @bodyf
delete from correosaclientes where cliente = @cliente
fetch next from micursor
into @cliente, @ningreso, @proyecto,@fingreso,@correocliente
end
close micursor
deallocate micursor
any comments ideas or suggestions are appreciated.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply