Run the script to find out if there is any job running. If not then is safe to restart it.
List Running Jobs
/*
DESCRIPTION:
List the RUNNING jobs on an instance
CONFIGURATION
None
Compatibility list:
MSSQL2005
MSSQL2008
Does not work
MSSQL2000
Legend
@execution_status=1 Running
@execution_status=4 Idle
*/
IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE ID = OBJECT_ID(N'tempdb..#JobInfo'))
BEGIN
DROP TABLE #JobInfo
END
SELECT * INTO #JobInfo
FROM OPENROWSET('sqloledb', 'server=(local);trusted_connection=yes'
, 'set fmtonly off exec msdb.dbo.sp_help_job @execution_status=1')
SELECT
-- [job_id]
[originating_server],[name],[enabled]
--,[description]
,[start_step_id]
--,[category]
,[owner]
--,[notify_level_eventlog],[notify_level_email],[notify_level_netsend],[notify_level_page],[notify_email_operator],[notify_netsend_operator],[notify_page_operator],[delete_level],[date_created],[date_modified]
--,[version_number]
,[last_run_date]
,[last_run_time]
,[last_run_outcome]
,[next_run_date]
,[next_run_time]
--,[next_run_schedule_id]
,[current_execution_status]
,[current_execution_step]
,[current_retry_attempt]
--,[has_step],[has_schedule],[has_target]
,[type]
FROM #JobInfo
When you get an error, below is the solution
Resolution:
Basically, ‘Ad hoc Distributed Queries’ are disabled by default in SQL Server due to security configuration and you cannot use either OPENROWSET or OPENDATASOURCE and if you cannot execute these two rowset functions, then you cannot access any remote data sources. So how to fix this issue?
Given below is the script to enable ‘Ad hoc Distributed Queries’.
USE master
GO
sp_configure 'show advanced options', 1;
RECONFIGURE;
GO
sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE;
GO
--OUTPUT
Configuration option ‘show advanced options’ changed from 0 to 1. Run the RECONFIGURE statement to install.
Configuration option ‘Ad hoc Distributed Queries’ changed from 0 to 1. Run the RECONFIGURE statement to install.
As you can see above, the ‘Ad hoc Distributed Queries’ setting changed from 0 to 1. Now you can easily execute any ‘Ad hoc Query’
Note : By default, this option is set to 0 and you need to change it to 1 to activate this feature.