How to prevent email if report has no results

Question

Post reply

How to prevent email if report has no results

NineIron

SSChampion

Points: 12536
More actions
October 17, 2014 at 9:19 am

#291717

Is there a way to prevent a subscription sending out an email, if there are no results to show in the report?

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

You can try data driven subscriptions.

Here is something to look at for that.

http://msdn.microsoft.com/en-us/library/ms159150(v=sql.90).aspx

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

NineIron SSChampion Points: 12536 More actions · Answer 2

NineIron

SSChampion

Points: 12536

October 17, 2014 at 9:45 am

#1753202

Unfortunately, it's not available here.

Jack Corbett SSC Guru Points: 184394 More actions · Answer 3

There's no easy way to do it. What I've done, and it is a hack, is create a different SQL Agent job where step 1 checks for data from the report, and if step 1 doesn't error, then step 2 calls the job created by the report server. It means running the report query twice, but it does keep an "empty" email from going out.

You have to remove the schedule from the job setup by the report server.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

NineIron SSChampion Points: 12536 More actions · Answer 4

I'm either going to wait for DDS or I guess the user will be getting a few empty emails.

Thanx.

Shawn Melton SSC-Insane Points: 24685 More actions · Answer 5

I have seen one setup, although is a bit of a hack, is to add code at the end of the procedure that returns the data in the report where it validates the number of records. If no records are being returned you RAISERROR, if the report detects an error it should not send the email. I have never tried this method though.

Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton

Jack Corbett SSC Guru Points: 184394 More actions · Answer 6

Shawn Melton (10/17/2014)
I have seen one setup, although is a bit of a hack, is to add code at the end of the procedure that returns the data in the report where it validates the number of records. If no records are being returned you RAISERROR, if the report detects an error it should not send the email. I have never tried this method though.

I like that idea. I might have to try it.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 7

SQLRNNR (10/17/2014)
You can try data driven subscriptions.
Here is something to look at for that.
http://msdn.microsoft.com/en-us/library/ms159150(v=sql.90).aspx

+1000

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jack Corbett SSC Guru Points: 184394 More actions · Answer 8

Jeff Moden (10/21/2014)
SQLRNNR (10/17/2014)
You can try data driven subscriptions.
Here is something to look at for that.
http://msdn.microsoft.com/en-us/library/ms159150(v=sql.90).aspx
+1000

If you have enterprise edition.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 9

Jack Corbett (10/21/2014)
Jeff Moden (10/21/2014)
SQLRNNR (10/17/2014)
You can try data driven subscriptions.
Here is something to look at for that.
http://msdn.microsoft.com/en-us/library/ms159150(v=sql.90).aspx
+1000
If you have enterprise edition.

Ah... yeah... Don't you just love the split of functionality they provided us with?

The RAISERROR hack would definitely be a way around that. Thanks for the reminder.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)