#JustLearned 5 Disabling/Enabling SQL Jobs or Job schedule from remote computer

Here is the way to disbale/enable the SQL Jobs or its schedule from remote computer, The script is usning windows authentication and querying the MSDB database.

01.--Main Scripts
02.SQLCMD.EXE -S <servername> -E -dmsdb -h-1  -iQueryToRun_MSDB.sql -o output.txt
03.  
04.--QueryToRun_MSDB.sql (For Disabling SQL Jobs)
05.EXEC sp_update_job @job_name=N'S_AAAA',@enabled=0
06.  
07.--QueryToRun_MSDB.sql (For Enabling SQL Jobs Schedule)
08.EXEC sp_update_jobschedule @job_name = N'S_AAAA', @name =N'SCH_AAAA1' ,@enabled = 1
09.  
10.  
11.--Use @enabled  = 1 for enabling and @enabled  = 0 for disabling.

You can visit MSDN for more learning on this storeprocedure.

#Just Learned 4 Customised success or failure log of the SQL job in the Windows Event Viewer.

You can check the status of a SQL job and log the customised success or failure message of the job for the day in the Windows Event Viewer.

01.IF NOT EXISTS (SELECT TOP 1 run_date FROM msdb.dbo.sysjobhistory 
02.WHERE job_id=(SELECT job_id FROM msdb.dbo.sysjobs where name='S_000w') AND step_id = 3
03.AND run_status=1 AND run_date=CONVERT(VARCHAR , GETDATE(), 112) ORDER BY run_date DESC)
04.BEGIN 
05.EXEC xp_logevent 60000, 'S_000w: Daily Job not complete', ERROR 
06.END
07.ELSE 
08.BEGIN 
09.EXEC xp_logevent 60000, 'S_000w: Daily Job completed successfully', INFORMATIONAL 
10.END

Usage –xp_logevent { error_number , ‘message’ } [ , ‘severity’ ] For more information –http://msdn.microsoft.com/en-us/library/ms186244.aspx Also check the uses RAISERROR

#JustLEarned3 Identify running jobs on remote server

To identify running jobs in a remote server we can use sp_help_job command in the following way.

1.--Content of sqlQuery.sql
2.exec msdb.dbo.sp_help_job @execution_status = 0
3.--Main Code to execute.
4.sqlcmd.exe -S<servername> -U<username> -P<password> -dmsdb -h-1 -b -i sqlQuery.sql