Subscription - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

Reporting Services Development

Subscription

Author

Topic

werhardt
Constraint Violating Yak Guru

270 Posts

Posted - 2011-10-12 : 10:48:20

How can I get an email not to send if there is no data in the report? I have this SP that I created. This works, but if I add a subsription it still send anyway.

DECLARE @SQLQuery NVARCHAR(MAX)
DECLARE @NoOfRows INT

set deadlock_priority high;

SET @NoOfRows = (Select Count(*)
FROM impact.dbo.pcs
WHERE
pcs_spec1 IN ('FAM','PED','GEN') and pcs_xtyp <> 'PRIM')

SET @SQLQuery = 'Select pcs_id1 as PCS#, pcs_lname as Last, pcs_fname as First, pcs_minit as Middle,
pcs_degree as Degree, pcs_xtyp as Type, pcs_spec1 as Spec1, pcs_spec2 as Spec2, pcs_chgby
FROM impact.dbo.pcs
WHERE
pcs_spec1 IN ("FAM","PED","GEN") and pcs_xtyp <> "PRIM"
order by pcs_degree'

DECLARE @MsgBody VARCHAR(MAX)

SET @MsgBody = 'Please see the attached exception report that lists ' + STR(@NoOfRows) + ' ' + 'instance(s) of Bad Types for specialites (FAM,PED,GEN),
please fix the following records.'

print @NoOfRows
IF @NoOfRows > 0
begin

EXEC msdb.dbo.sp_send_dbmail @recipients= 'jsmith@dg.com',

@subject = 'WARNING! Bad Types for specialites (FAM,PED,GEN)',
@query = @SQLQuery,
@attach_query_result_as_file = 1,
@query_attachment_filename = 'Exception_Report.txt',
@Body = @MsgBody,
@body_format = 'HTML',
@importance = 'HIGH',
@query_result_width = 256,
@query_result_separator = '|'

if exists (
select *
from dbo.sysobjects
where id = object_id(N'[dbo].[Incorrect_Bad_Xtypes_FAM_PED_GEN]')
and OBJECTPROPERTY(id, N'IsTable') = 1)
drop table [dbo].[Incorrect_Bad_Xtypes_FAM_PED_GEN]

Create Table [Incorrect_Bad_Xtypes_FAM_PED_GEN](

[PCS#] [varchar](8) NULL,
[Last Name] [varchar](20) NULL,
[First Name] [varchar](15) NULL,
[Middle Name] [varchar](1) NULL,
[Degree] [varchar](6) NULL,
[Type] [varchar](4) NULL,
[Specialty 1] [varchar](3) NULL,
[Specialty 2] [varchar](3) NULL,
[Specialty 3] [varchar](3) NULL,
[Specialty 4] [varchar](3) NULL,
[Changed By] [varchar](6) NULL)

Insert into [Incorrect_Bad_Xtypes_FAM_PED_GEN]([PCS#], [Last Name],[First Name],[Middle Name],[Degree],
[Type],[Specialty 1],[Specialty 2],[Specialty 3],[Specialty 4],[Changed By])

Select
pcs_id1 as PCS#,
pcs_lname as [Last Name],
pcs_fname as [First Name],
pcs_minit as [Middle Name],
pcs_degree as Degree,
pcs_xtyp as Type,
pcs_spec1 as [Specialty 1],
pcs_spec2 as [Specialty 2],
pcs_spec2 as [Specialty 3],
pcs_spec2 as [Specialty 4],
pcs_chgby as [Changed By]

FROM impact.dbo.pcs
WHERE
pcs_spec1 IN ('FAM','PED','GEN') and pcs_xtyp <> 'PRIM'
order by pcs_degree

Select Distinct* from [Incorrect_Bad_Xtypes_FAM_PED_GEN]

END

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources