- SQL Server

SQL Server 2005 Maintenance Plans not sending email alerts?

Wed, 30 Dec 2009 23:53:31 --0100

SQL Server 2005 does make it much easier to set up email alerts than it's predecessors, however its still very easy to set it up in such a way that emails generated by SQL Agent are not sent, despite test emails being sent without any difficulty.

If your SQL Agent jobs are not sending out emails, and you have set up mail accounts and profiles, together with specifying at least one operator with an email address, and you are getting the following errors in your error logs:

[260] Unable to start mail session (reason: No mail profile defined)

[264] An attempt was made to send an email when no email session has been established

You need to do the following:

Right click on SQL Server Agent within Management Studio
Select Properties
Select Alert System
Tick the Enable mail profile checkbox
Select Database Mail from the Mail System: select box
Select the Mail Profile you wish to use for SQL Agent emails

I believe you then need to restart SQL Server Agent - and providing your profile is set up correctly (and firewall rules permitting) SQL Agent jobs should now send out emails correctly :)

After doing this, it's probably worth checking that emails are working correctly - to do this I set up a simple SQL Agent job containing a single step with a simple select statement, and configured the job to send me an email on completion. I then ran the task manually, and checked to see the email was received successfully.

SQL Server Maintenance Plan – Rebuild Index Task Gotcha on SQL Server 2005 Standard

Wed, 30 Dec 2009 23:31:00 --0100

Here's one to watch out for if you are setting up a SQL Server 2005 Maintenance Plan on SQL Server 2005 Standard edition

SQL Server 2005 supports online rebuilding of indexes on Enterprise Edition Only

The Task wizard for setting up the Rebuild Index Task has an option Keep index online while reindexing even when connected to Standard Edition.

No warnings are given when setting up a task with this option enabled, but when the maintenance plan executes, the error Online index operations can only be performed in Enterprise edition of SQL Server will be generated for each index - and indexes will not be recreated.

As a side note, make sure you do have the Generate a text file report option ticked within Reporting and Logging settings for the job, as the information given within the SQL job History Log is limited to The job failed

As a side side note, its definitely worth checking that your SQL Agent email alerts are functioning correctly if you have them set to notify on failure, as otherwise an issue like this can go completely un-noticed (apart from performance issues), as the rest of the tasks within the plan will probably succeed (like backups being generated) - which is certainly the main thing I have always checked after setting up a maintenance plan...

I Love (SQL) views

Thu, 22 Nov 2007 22:57:00 --0100

I've always been a fan of Views within SQL Server - I've found them especially useful when creating applications which need to be able to access data from external sources, without necessarily being aware of where that data came from - or even of its precise format. One feature which I have found especially useful, is the ability to select and join to data from within a different database, from within a view. This can be done as follows: [More]

Love the View!

Thu, 22 Mar 2007 00:38:00 --0100


Create View MyView1
AS
Select L.ID as Local_ID, L.Name as Local_Name, L.Related_ID as Remote_ID,
R.Name as Remote_Name, R.Description as Remote_Description
From dbo.tbl_local L
Inner Join  RemoteDB.dbo.tbl_remote R on L.Related_ID = R.ID

Please note: table / field names are for illustration only - and not indicative of Best Practice or requirement So specifying an explicit database name in front of the schema name causes the data to be retrieved from the named DB, on the same server, rather than from the db in which the view is placed. I believe it is also possible to specify a database which is on a different server, using Linked Servers - but I've never had a requirement to check this out so far. What's great about this, is that if I reference this view rather than the tables directly within my application / stored procedures, I can change where the data is coming from without impact on the application. This has been particularly useful in the past when a system is reliant upon external data, but needs to be developed in isolation. I can create a table in my local DB which mirrors the structure of the remote data source, and point my view at that - and then when the application is ready for integration, I can update the view to point to the remote DB. [More]