Advertisements

Archive

Archive for April, 2013

SQLwithManoj now on Windows 8 Store

April 20, 2013 2 comments

Dear Readers,

Today I’m very happy to announce the release of Windows 8 App for this blog and its availability on Windows 8 store.

I’ve created this Windows 8 App and published to the Windows 8 Marketplace.

SQLwithManoj Windows 8 App

SQLwithManoj Windows 8 App

This is first Windows 8 App developed by me and I faced lot of challenges while developing and deploying it to the Marketplace.

Please download this App on your Windows 8 PC and let me know your feedback.

Thanks!!!

Advertisements

SQL DBA – Disable/Enable multiple SQL Jobs at once

April 9, 2013 2 comments

Seems to be a simple topic. But yes when it comes to do these type of tasks we tend to recall the syntax and end up searching internet (bing/google) for the solution.

–> Disabling a single SQL Job can be done simply through SSMS. Right click on the SQL Job and select Disable. To enable it back simply select Enable for a disabled Job.

This can also be done by a TSQL query as shown below:

USE msdb;
GO

-- Disable a SQL Job:
EXEC dbo.sp_update_job
    @job_name = N'syspolicy_purge_history',
    @enabled = 0 ;
GO

select enabled, * from sysjobs where name = 'syspolicy_purge_history'
GO

–> Now if you’ve to Disable Multiple or All the Jobs in SQL Agent, how will you do it?

Selecting All SQL Jobs on SSMS and right clicking won’t give you the Disable option. And here it become more tricky as there is only way to do this by TSQL query. But there is no single TSQL query defined in SQL Server to Disable all SQL Jobs at once. So, we will have to create a Dynamic SQL which will create Script for all SQL Jobs dynamically to Disable each and every SQL Job. Let’s see how:

USE msdb;
GO

-- Disable Multiple SQL Jobs at once:
DECLARE @dynSql NVARCHAR(MAX) = ''

SELECT @dynSql += N'exec msdb.dbo.sp_update_job @job_name = ''' + name + N''', @enabled = 0;' + CHAR(10) + CHAR(13)
FROM msdb.dbo.sysjobs
WHERE enabled = 1
ORDER BY name;

PRINT @dynSql;
-- Here is the output of above PRINT statement:
exec msdb.dbo.sp_update_job @job_name = 'ExecuteSPuspGetBillOfMaterials', @enabled = 0;

exec msdb.dbo.sp_update_job @job_name = 'syspolicy_purge_history', @enabled = 0;

Simple Copy-Paste the the above Dynamically generated SQL Script and Execute it, it will Disable all SQL Jobs at once.