Sql-server – How to automatically deliver the SQL Server built in reports

sql serversql-server-2005sql-server-2008

When you right click on a number of items in the object explorer, it gives you the option for a reports menu. Various reports may exist depending on which type of item is selected. Yet, there does not appear to be any built-in functionality for automated delivery of these reports. It is hidden away somewhere?

My real question : Is there any way to set some of these up to be automatically delivered through email? The reports I am targeting are the "Job Steps Execution History", and "Top Jobs" reports for SQL Server agent. I am using SQL Server 2005/2008

Best Answer

No, there is no scheduling capability in SSMS for the SSMS reports. This is by design, if the limitations stated here apply to standard reports too (SQL Server Manageability Team Blog)

However, (I haven't tried this) the reports are available to use in a full SSRS install. See another page "on the same blog as above", attached zip file

Related Solutions

Sql-server – Run SQL Server Snapshot Agent automatically

I believe you are looking for sp_startpublication_snapshot. You'll want to execute this at the Publisher on the publication database.

Regarding the error: from a path could not be read, you'll want to ensure the Snapshot Agent process account has sufficient permissions to generate the snapshot. The process account needs the following permissions per BOL:

At minimum, be a member of the db_owner fixed database role in the distribution database.
Have write permissions on the snapshot share.
The account that is used to connect to the Publisher must at minimum be a member of the db_owner fixed database role in the publication database.

This is covered in Replication Agent Security Model.

How to Automatically Restart SQL Server 2008

I won't go into detail about why it's not a great idea, since you're already aware of all that. For anybody else stumbling across this answer: Try not to do this if possible!

For the occasions when I need to schedule an after-hours restart (patching, configuration changes, etc), I normally just run this in a batch file scheduled via Windows Task Scheduler:

net stop SQLSERVERAGENT
net stop MSSQLSERVER
net start MSSQLSERVER
net start SQLSERVERAGENT

Note that service names may vary if you aren't restarting the default instance. Also, the user account that the scheduled task runs under will need to have permission to restart the service.

I don't see any reason why this wouldn't work with a recurring schedule, as opposed to the one-time runs that I do.

Best Answer

Related Solutions

Sql-server – Run SQL Server Snapshot Agent automatically

How to Automatically Restart SQL Server 2008

Related Question