Don't send subscription if no data on report

I am fairly new to Reporting Services. That said I have created a
report that runs daily and via a subscription sends the report as
email. This works fine day in and day out, however some days there is
no data for the report. On the days when the report contains no data I
don't want a blank report sent to the subscribers, I prefer that they
get nothing. Is this out of the box functionality and I'm just not
finding it or is it coding I need to do? Much thanks to anyone willing
to point me in the right direction.
Amy Slankard
aslankard@.alumni.cofc.eduI don't think that functionality is built in but my way of handling it
is to include something in the subscription query that returns an
empty query if there is no data in the report. Nothing bad happens if
the subscription query returns no subscribers, just nothing gets sent.
It means including some form of the actual report query in the
subscription query so depending on how complex things are that may or
may not be a very practical or efficient way to go. My situation needs
some fairly heavy preprocessing that runs using the SQL Server Agent
in the early morning which among other things, sets a bit flag in a
table to say whether or not there is any data to report. My report
that runs some time later then includes that flag in its subscription
query.
Cheers
Ross
On 30 Mar 2005 06:50:27 -0800, "aslankard@.alumni.cofc.edu"
<amy.slankard@.gmail.com> wrote:
>I am fairly new to Reporting Services. That said I have created a
>report that runs daily and via a subscription sends the report as
>email. This works fine day in and day out, however some days there is
>no data for the report. On the days when the report contains no data I
>don't want a blank report sent to the subscribers, I prefer that they
>get nothing. Is this out of the box functionality and I'm just not
>finding it or is it coding I need to do? Much thanks to anyone willing
>to point me in the right direction.
>Amy Slankard
>aslankard@.alumni.cofc.edu

Don't run report straight away

When accessing a report via a url, is it possible to wait until the user
clicks the 'View Report' button, rather than running the report straight away
with whatever default parameters have been set?
FredNo. If all the parameters have defaults then the report will run. The only
thing you can do is to leave one parameter without a default.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Fred" <Fred@.discussions.microsoft.com> wrote in message
news:1333BECB-C529-4F38-8DF7-AEEBC57842FE@.microsoft.com...
> When accessing a report via a url, is it possible to wait until the user
> clicks the 'View Report' button, rather than running the report straight
away
> with whatever default parameters have been set?
>
> Fred

Dont backup if Database hasnt changed

I do weekly full backups of my SQL databases via a scheduled T-SQL job.
I noticed that I have some static databases that dont normally change,
so I dont want to back it up if it has not changed, but when it does,
then I want a backup.
Is there something in the master table, as example, that I can check
prior to running the backup that will indicate any changes?
An example is the Northwind database. I could exclude it from the
backup, but then I would not back it up if it where to change. Again
this is an example, I would not need to modify Northwind.
Thanks in advance for any ideas; they usually give me ideas to problems
yet to come...
Rob CamardaI don't think there's a generic way to tell if a database has changed
or not (assuming that you mean when data in a user table has changed).
You could run a trace which looks for INSERT/UPDATE/DELETE statements,
log the trace in a table, and then check the contents of the table in a
custom backup job, but that doesn't seem very practical.

If your concern is to reduce disk space used for backups, you could use
differential backups for your 'static' databases, and only do the full
backup once a month, or whatever interval is appropriate. But if your
concern is to simplify administration, and given how cheap disks are
relative to a DBA's time, I would consider just adding another disk and
continuing with the same backup job for all databases.

If this isn't helpful, you might want to give some more details about
your environment, especially about how big the databases are, how often
you expect updates, and what you're trying to achieve (eg. save disk
space).

Simon|||I use SQLsafe to backup and compress my data, which in turn is saved to
my backup server. My tape is HP's Surestore 6/6000 library. I have
5540GB tapes in the library.
Monday through Saturday I perform a differential backup and a full
backup on Sunday. My plan is to keep a 1 month worth of data on the
shared disk (890GB currently, upgrading to 10 300GB raid5 next year).
Once the full backup is a month old, I move it to the library and
delete the files from the disk once the backup is successful. I wont
backup the diffs to tape and will delete the prior weeks diff backups
once the full backup is complete.
This is my current plan, it will change as I get useful input and see
how it works in practice.
My full backup of some tables are not currently large; the largest
5.3GB after compression. However, I tend to look forward, and forsee
more demand on the backup server, so why backup data that hasnt
changed? The library will be backing up 3 Linux servers, 16 windows
servers, 5 SQL and 3 Sun machines with more to come. So, I wish to
maximize the storage on the Library by keeping unnecessary data off the
library.
Again, there may not be a practical solution to my question, but often
I find I learn something unrelated that may help me with a future
problem.|||Hi

If you don't back up the database(s) then the last good backup may fall off
the tape cycle. If you chose to do this then restoring the database(s) will
you having to search more tapes for the relevant backups.

If the backups don't fit onto a single tape then you may wish to use an
autochanger (if you aren't already!). It might be possible that you could
use a server that can stage the backups on disc before putting to tape at a
different time. You may also want to consider separating database backups
from other types of backups to speed up the time needed to recover.

You may want to remove the sample databases like northwind and pubs from
your live systems, they are re-creatable from the scripts which are
downloadable if necessary.

If databases are not updated or not updated in an ad-hoc way, then you may
wish to make them read-only and use a different backup cycle for them.

John

"rcamarda" <rcamarda@.cablespeed.com> wrote in message
news:1126799621.306011.34090@.o13g2000cwo.googlegro ups.com...
>I use SQLsafe to backup and compress my data, which in turn is saved to
> my backup server. My tape is HP's Surestore 6/6000 library. I have
> 5540GB tapes in the library.
> Monday through Saturday I perform a differential backup and a full
> backup on Sunday. My plan is to keep a 1 month worth of data on the
> shared disk (890GB currently, upgrading to 10 300GB raid5 next year).
> Once the full backup is a month old, I move it to the library and
> delete the files from the disk once the backup is successful. I wont
> backup the diffs to tape and will delete the prior weeks diff backups
> once the full backup is complete.
> This is my current plan, it will change as I get useful input and see
> how it works in practice.
> My full backup of some tables are not currently large; the largest
> 5.3GB after compression. However, I tend to look forward, and forsee
> more demand on the backup server, so why backup data that hasnt
> changed? The library will be backing up 3 Linux servers, 16 windows
> servers, 5 SQL and 3 Sun machines with more to come. So, I wish to
> maximize the storage on the Library by keeping unnecessary data off the
> library.
> Again, there may not be a practical solution to my question, but often
> I find I learn something unrelated that may help me with a future
> problem.