SQL Agent Job: Update the notification operator

I found many SQL Agent job were enabled but no any  notifications setup in case of failure.

I ran the following

use msdb
GO
select * from dbo.sysjobs

where notify_level_email = 0

and enabled = 1

--- gives the list of jobs enabled but no notification set up.

SELECT [ID], [Name], [Enabled]

FROM MSDB.dbo.sysoperators

WHERE [Enabled] = 1

ORDER BY [Name];
Go

-- Gives the list of operators. Pick the Id of the one you want to notify to use in the below script








UPDATE S

SET S.[notify_level_email] = 2,

S.[notify_email_operator_id] = 2   ---my operator

FROM MSDB.dbo.sysjobs S

WHERE S.[Notify_Level_Email] = 0

AND S.[Enabled] = 1;




GO

script: Change the owner of the sql jobs

DECLARE @name_holder VARCHAR(1000)

DECLARE My_Cursor CURSOR

FOR

SELECT [name]  FROM msdb..sysjobs 

OPEN My_Cursor

FETCH NEXT FROM My_Cursor INTO @name_holder

WHILE (@@FETCH_STATUS <> -1)

BEGIN

exec msdb..sp_update_job

        @job_name = @name_holder,

        @owner_login_name = 'sa'

FETCH NEXT FROM My_Cursor INTO @name_holder

END 

CLOSE My_Cursor

DEALLOCATE My_Cursor

Work adventure : MoveSQL Agent Jobs to another instanse

It is very easy to move (recreate) the sql agent job that you want to export to another instance of SQL server

1. Highlight the job you want o export
2. Right click
3. Choose Scrip Job as CREATE TO

The script is created and you can run that in the new instance that will create the job.

To move multiple jobs
You can also script multiple jobs to a single file. In SSMS

Click on Jobs,
hit F7 to get the 'Object Explorer Details' tab.
Highlight the jobs you want,
Script job as...

Schedule a SQL profiler to run at a specific time

I needed to reserach some external backups on the database that ran in the middle of the night. To find out the source and the status. I decided to set up a trace in sql profiler that consisted teh processID, Loginame, NTusername etc .

Steps on SQL Server 2008 R2
---

• Connect to SSMS
• Go to Tools
• Go to SQL Profiler
• Chose the appropriate trace template according to requirements ( or create your own)
• Check enable trace stop time and specify the date and time that you want it to stop
• Click on the Run

If you run the follwing query you will see our trace

select * from ::fn_trace_getinfo(0)
Immediately stop the trace

Now we want to export the create trace script and add this script as  one of the step in the schedule job

Click on File----Select Export--Select Script Trace Definition

Select the entire script and add it as a step in the schedule job.

In the script - Search for key word in the script 'InsertFileNameHere' and replace with where to store the trace file.

exec @rc = sp_trace_create @TraceID output, 0, N'E:\profilertrace\trace1.trc', @maxfilesize, @Datetime

 

Save the Job.

That's all !!! Schedule the job to run at the specific time that you would like it to run.

Permission to run a specific SQL job to a user

I added a user to the msdb database and gave him the SQLAgentUserRole but that gave him access to all the jobs, which is not what I wanted. This is the work around I followed....


USE msdb
GO
CREATE PROC dbo.Start_Job_MyJobThatAGroupControls
-- Executes as the database owner, for msdb that is 'sa'
WITH EXECUTE AS OWNER
AS
-- Hardcoding the job name limits this procedure's control
EXEC dbo.sp_start_job @job_name = 'MyProductionJobName'
GO
-- Grant Execute rights to your selected users
GRANT EXECUTE ON Start_Job_MyJobThatAGroupControls TO <usernames>
GO


This way the user will not have direct access to the job MyProductionJobName but through this wrapper sp it can run the job.

SQL Agent: Script to disable and enable SQL agent jobs

The following script disables the job scedules in the agent
BEGIN TRY

 
BEGIN CATCHSET @errMsg = 'There was an error! ' + ERROR_MESSAGE()
 
BEGIN TRY
exec msdb..sp_update_job @job_name = 'BJC_InvalidAddressFix', @enabled = 0END TRY


BEGIN TRY
exec msdb..sp_update_job @job_name = 'Update Status Closure referrals', @enabled = 0END TRY

BEGIN CATCHSET @errMsg = 'There was an error! ' + ERROR_MESSAGE()END CATCH

If @errMsg <> '' Begin
@profile_name = 'SQLMail@abc.com',
@recipients = 'dataExchange@abc.com',
@subject = 'Job Failure - to Disable jobs on srvr1,
@body = @errMsg,
@body_format ='HTML'
 

--select @rcEnd
The following script enables the job scedules in the agent
BEGIN TRY

 
BEGIN CATCHSET @errMsg = 'There was an error! ' + ERROR_MESSAGE()
 
BEGIN TRY

 
BEGIN CATCHSET @errMsg = 'There was an error! ' + ERROR_MESSAGE()END CATCH
BEGIN TRY
exec msdb..sp_update_job @job_name = 'Update Status Closure referrals', @enabled = 1END TRY
BEGIN CATCHSET @errMsg = 'There was an error! ' + ERROR_MESSAGE()
 

If @errMsg <> '' Begin
@profile_name = 'SQLMail@abc.com',
@recipients = 'dataExchange@abc.com',
@subject = 'Job Failure - to Enable jobs on srvr1,
@body = @errMsg,
@body_format ='HTML'
 


--select @rcEnd  exec @rc = msdb.dbo.sp_send_dbmailEND CATCHexec msdb..sp_update_job @job_name = 'BJC_InvalidAddressFix', @enabled = 1END TRYEND CATCHexec msdb..sp_update_job @job_name = 'BJC_CloseAccounts', @enabled = 1END TRYexec @rc = msdb.dbo.sp_send_dbmail
   END CATCHexec msdb..sp_update_job @job_name = 'BJC_CloseAccounts', @enabled = 0END TRY

SQL Agent : Schedule DTS as a SQL Job

Call the DTS from an SQL agent job as

 DTSRun /S Servername /E /N "DTSName"

Select  type as Operating System(cmdExec)  from the drop down


DTSName = Name of the DTS that you have created

Servername= Name of your server where the DTS is created