Tags

, , , , , , ,

Error
This is my first technical blog in SQL Server. I just want to share my experience on a problem I faced last month in one of the production server. There was a Stored Procedure to pull all job details from the SQL Server and whenever this SP was executed from a scheduled job it threw error. This scheduled job had always failed and my job was to fix it. So, I started to debug the SP and found following error:
‘Msg 8164, Level 16, State 1, Procedure , Line 110 An INSERT EXEC statement cannot be nested.’

The reason for this error was due to this statement: ‘INSERT INTO #jobs_status EXEC msdb.dbo.sp_help_job’ in the SP

The statement was trying to insert job details from sp_help_job, but due to the limitation of SQL server on running two nested INSERT INTO within a SP it will through the exception. To prevent this issue I had three options:

1. Change the SP
2. Use OPENROWSET function
3. Modify the INSERT statement

1. Change the SP:
Changing the SP would solve the problem but being a system SP msdb.dbo.sp_help_job I skipped this options

2. Use OPENROWSET function:
The OPENROWSET function is an ad-hoc method of connecting and accessing remote data by using OLE DB and the purpose of OPENROWSET function is to connect and access data from a remote server. To use this function I had to enable the adhoc query and update the statement by going through following process:

sp_configure ‘show advanced options’, 1;
RECONFIGURE;
sp_configure ‘Ad Hoc Distributed Queries’, 1;
RECONFIGURE;
GO

INSERT INTO #jobs_status
SELECT * FROM
OPENROWSET(‘SQLNCLI’, ‘Server=;Database=;Uid=username;Pwd=password’,
‘EXECUTE msdb.dbo.sp_help_job’

This solved the problem in my local staging environment but there were some security concern on enabling this feature. Microsoft mentions that “Enabling the use of ad hoc names means that any authenticated login to SQL Server can access the provider”. Being a production environment, it was not right to enable this feature. Hence, I had to look for the next option.

3. Modify the INSERT statement
This is how I managed to solve the problem by using extend procedure: master.dbo.xp_sqlagent_enum_jobs. To use extended procedure Agent XPs must be enabled which in my case was already enabled. Here is the changed section to resolve the issue:

CREATE TABLE #jobs_status
(
job_id UNIQUEIDENTIFIER
, originating_server NVARCHAR(30)
, name SYSNAME
, enabled TINYINT
, description NVARCHAR(512)
, start_step_id INT
, category SYSNAME
, owner SYSNAME
, notify_level_eventlog INT
, notify_level_email INT
, notify_level_netsend INT
, notify_level_page INT
, notify_email_operator SYSNAME
, notify_netsend_operator SYSNAME
, notify_page_operator SYSNAME
, delete_level INT
, date_created DATETIME
, date_modified DATETIME
, version_number INT
, last_run_date INT
, last_run_time INT
, last_run_outcome INT
, next_run_date INT
, next_run_time INT
, next_run_schedule_id INT
, current_execution_status INT
, current_execution_step SYSNAME
, current_retry_attempt INT
–, has_step INT –not required
–, has_schedule INT –not required
–, has_target INT –not required
–, type INT –not required
)

GO

DECLARE @CurrentJobs TABLE
(
[job_id] uniqueidentifier
, [last_run_date] VARCHAR(255)
, [last_run_time] VARCHAR(255)
, [next_run_date] VARCHAR(255)
, [next_run_time] VARCHAR(255)
, [next_run_schedule_id] VARCHAR(255)
, [requested_to_run] VARCHAR(255)
, [request_source] VARCHAR(255)
, [request_source_id] VARCHAR(255)
, [running] VARCHAR(255)
, [current_execution_step] VARCHAR(255)
, [current_retry_attempt] VARCHAR(255)
, [state] VARCHAR(255)
)

INSERT INTO @CurrentJobs EXECUTE master.dbo.xp_sqlagent_enum_jobs 1,”

INSERT INTO #jobs_status
SELECT DISTINCT sj.job_id
, CAST (SERVERPROPERTY(‘ServerName’) AS NVARCHAR )as originating_server
, sj.name
, sj.enabled
, CAST (sj.description AS NVARCHAR)
, sj.start_step_id
, sj.category_id
, SUSER_SNAME(owner_sid) as owner
, sj.notify_level_eventlog
, sj.notify_level_email
, sj.notify_level_netsend
, sj.notify_level_page
, sj.notify_email_operator_id
, sj.notify_netsend_operator_id
, sj.notify_page_operator_id
, sj.delete_level
, sj.date_created
, sj.date_modified
, sj.version_number
, cj.last_run_date
, cj.last_run_time
, so.last_run_outcome
, cj.next_run_date
, cj.next_run_time
, cj.next_run_schedule_id
, cj.running as current_execution_status
, cj.current_execution_step
, cj.current_retry_attempt
FROM msdb.dbo.sysjobs sj
JOIN @CurrentJobs cj ON cj.Job_ID= sj.job_id
JOIN msdb.dbo.sysjobservers so
ON cj.Job_ID = so.job_id
AND sj.job_id = so.job_id

GO

Advertisements