Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Solution - scheduling and emailing a report only if data exists

Solution - scheduling and emailing a report only if data exists

Solution - scheduling and emailing a report only if data exists

I have found a solution that I would like to share.  I needed the ability to send a report on a recurring basis to certain salespeople only if there was data to send. The best way to solve this issue was to first create a custom error within SQLServer using the sp_addmessage stored procedure. I used an error number of 60001.
Then I created a Query object using the 'if exists' command with a subquery of the data that you want to find in the database.  If it does exist then select the data.  If it does not then use the Raiserror command and raise the new 60001 error. So, you can schedule this query object to run every night and depending on whether there is data or not it will either complete successfully or it will fail.
Now there is one other thing you have to do.  That is to go into Info Administrator and create a new event. When you schedule the query object you go to the Notify tab and click on the specify button under the 'On Success' side and click on the Event and Set.  Do the same thing on the On Fail side but click on the Event and Reset. THen schedule the job.  Then you can choose a report object and under the When tab choose Program/Event, then click on this same Event and click Add.  This will ensure that the report will only run when the Event is set...


Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close