#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

#JustLearned 2 Russian Website on .NET and SQL help

http://vb-net.ru/, this website has nice articles and help for .NET and sql server related stuff. The only thing you have to do is open this in google chrome and say translate to ENGLISH. Also you can select a language at the right hand side drop down.

#Just Learned 1 Tweet Button for Chrome

Install the Tweet Button for Chrome. Fast & simple way to share the currently viewed page on Twitter. Also displays the official Tweet Count for every web page.