List disabled Jobs and their schedule

To List the disabled Jobs and their relative schedules , you can run the following query:

USE msdb
GO
SELECT
 ss.schedule_id ,
 J.name,
 ss.name,
 J.enabled [Job Enabled ?],
 ss.enabled [Schedule ENABLED ?]
FROM sysjobs J
JOIN sysjobschedules s
 ON J.job_id = s.job_id
JOIN sysschedules ss
 ON s.schedule_id = ss.schedule_id
WHERE J.enabled = 0

The schedule_id can be used directly with sp_update_schedule, as from SQL 2008 schedules can be managed independantly of jobs.

Error formatting query, probably invalid parameters [SQLSTATE 42000] (Error 22050)

I spent 2 days searching for the above error , the situation is as follows :

1. we have 2 servers, one for dev, one for production using the same domain account both for the SQL Server service and SQLAgent service.
2. the user is not sysadmin on either server.
3. Calling sp_send_dbmail  works fine in SSMS on both servers
4. On my production server , it did not work and had the above error.

On some posts, I read that some people added the SQL Server agent account on the sysadmin group and "it worked" (sounds to me as a quick and dirty solution).

Finally noticed that job owners were different on both servers:
- on the development server , the job owner is a database user (db_owner), with DatabaseMailUserRole.
- on the production server, the job owner is our sysadmin user (not listed in DatabaseMailUserRole).

I just changed the job owner on the production server and it worked !! No sysadmin user to set up, it works with the application user.

Unable to start mail session (reason: No mail profile defined) - Message in Error Log

Message
[098] SQLServerAgent terminated (normally)
This is: the most common message that will be logged when there is no "Mail Session" defined for the SQL Server Agent Alert System.

To Fix this or to make this message disappear in the Error Log, you have to enable "Mail Session" and re-start the SQL Server Agent.
To Do this,

• Connect to the Server
• Right Click on the "SQL Server Agent" and go to "Properties"
• Then Go to "Alert System" Tab
• Make sure the check box "Enable Mail Profile" is checked
• Click OK to exit
• Re-start the SQL Server Agent Service