December 2, 2014 at 1:41 am
Hi All,
I have a job below, which takes the results and send to the users in email.
But I have a question, how can I send only one email with all rows, not to send the for every row on table separated email.
DECLARE @Body VARCHAR(MAX)
DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
DECLARE @RowCount INT
DECLARE @id INT
declare @department nvarchar(30)
declare @name_surname nvarchar(30)
declare @company_name nvarchar(30)
declare @start_date datetime
declare @end_date datetime
declare @service nvarchar(30)
declare @deactivation_type nvarchar(30)
declare @email nvarchar(30)
declare @tariff nvarchar(30)
declare @createdby nvarchar(30)
SET @StartDate = GETDATE();
SET @EndDate = @StartDate;
SET @EndDate = @EndDate-1;
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[WSS_Content_SP2007].[dbo].[temp_test]') AND type in (N'U'))
begin
drop table [WSS_Content_SP2007].[dbo].[temp_test]
end
select aud.tp_ID as id, nvarchar16 as Department, nvarchar1 as NameSurname,nvarchar5 as CompanyName,datetime1 as StartDate,datetime2 as EndDate,
nvarchar22 as [Service],nvarchar23 as Tariff,nvarchar11 as DeactivationType, tp_title as CreatedBy ,tp_email as email
into [WSS_Content_SP2007].[dbo].[temp_test_vip_accounts_mgrs]
from alluserdata aud , userinfo ui
where aud.tp_Author = ui.tp_id and ui.tp_SiteID=aud.tp_SiteId
and tp_listid = '0847CAAD-7174-4D3C-89B5-C8AC31A2B855' and int2 = 2
and Convert(varchar(10), dateadd(dd,0, datediff(dd,0, aud.datetime2 )),121)= Convert(varchar(10),Dateadd(d,-1,getdate()),121)
SELECT @RowCount = COUNT(*) FROM [WSS_Content_SP2007].[dbo].[temp_test];
IF @RowCount > 0
BEGIN
DECLARE result_cursor CURSOR FAST_FORWARD FOR
SELECT id, Department, NameSurname, CompanyName, StartDate,EndDate, [Service], Tariff,DeactivationType,CreatedBy, email
from [WSS_Content_SP2007].[dbo].[temp_test_vip_accounts_mgrs]
open result_cursor
FETCH NEXT FROM result_cursor into @id,@department,@name_surname,@company_name,@start_date,@end_date,@service,@tariff,@deactivation_type,@createdby,@email
WHILE @@FETCH_STATUS = 0
BEGIN
set @body = '<table border="1" bordercolor="#CCCCCC" cellpadding="0" cellspacing="0" style="font-family:tahoma; font-size:12px"><TR><TD height="30" align="center"><strong> ID </strong></TD><TD align="center"><strong>Department</strong></TD><TD align="center"><strong>Name and Surname</strong></TD>
<TD align="center"><strong>Company name</strong></TD><TD align="center"><strong>Start Date</strong></TD><TD align="center"><strong>End Date</strong></TD> <TD align="center"><strong>Service</strong></TD>
<TD align="center"><strong>Tariff</strong></TD> <TD align="center"><strong>Deactivation type</strong></TD><TD align="center"><strong>Created By</strong></TD><TR> <TD width="150" height="20" align="center"> <a href="http://intranet/VA/Lists/Department Managers VIPTest Accounts/DispForm.aspx?ID='+Cast(isnull(@id,0) as varchar(5))+'">'+Cast(isnull(@id,0) as varchar(5))+' </a></TD><TD width="150" align="center">'+isnull(@department,'')+'</TD>
<TD width="150" align="center">'+@name_surname+'</TD><TD width="150" align="center"> '+@company_name+'</TD><TD width="150" align="center">'+isnull(CONVERT (VARCHAR,@start_date,103),'')+'</TD>
<TD width="150" align="center">'+isnull(CONVERT (VARCHAR,@end_date,103),'')+'</TD><TD width="150" align="center">'+isnull(@service,'')+'</TD><TD width="150" align="center">'+isnull(@tariff,'')+'</TD><TD width="150" align="center">'+isnull(@deactivation_type,'')+'</TD><TD width="150" align="center">'+isnull(@createdBy,'')+'</TD></TR></TABLE>'
execute msdb.dbo.sp_send_dbmail
@profile_name = 'Intranet'
,@recipients = 'someone@someone.com'
,@blind_copy_recipients = 'someone@someone.com'
,@subject = 'Test'
,@body = @body
,@body_format = 'HTML'
FETCH NEXT FROM result_cursor into @id,@department,@name_surname,@company_name,@start_date,@end_date,@service,@tariff,@deactivation_type,@createdby,@email
end --end cursor
close result_cursor
deallocate result_cursor
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[WSS_Content_SP2007].[dbo].[temp_test_vip_accounts_mgrs]') AND type in (N'U'))
begin
drop table [WSS_Content_SP2007].[dbo].[temp_test]
end
END
December 2, 2014 at 6:12 am
Try SELECT ... FOR XML
This is an example
Select
(select [@width]=150
, [@height]=20
, [@align]='center'
, [a/@href] =N'http://intranet/VA/Lists/Department Managers VIPTest Accounts/DispForm.aspx?ID='+Cast(isnull(id,0) as varchar(5))
, [a] = Cast(isnull(id,0) as varchar(5))
for xml path('TD'), type)
,(select
=150
,
='center'
,
=name_surname
for xml path(''), type)
,(select
=150
,
='center'
,
=company_name
for xml path(''), type)
from (
-- sample data
select * from
(values
(1,'surmame1','companyA')
,(100,'surmame100','companyX')
) t(id,name_surname,company_name)
) as t
for xml path('TR')
December 2, 2014 at 6:54 am
Soleved.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply