Definitions More Info.
Definition ID	774
Title	SQL
Category	NOTES
Definition	Job status gosteren sp
Definition Description	-- https://blogs.msdn.microsoft.com/sqlagent/2012/07/12/workaround-sql-server-2012-openrowset-on-sp_help_job-throws-the-metadata-could-not-be-determined/ USE MSDB GO PRINT '' PRINT 'Creating procedure sp_help_job_with_results...' go IF (EXISTS (SELECT * FROM msdb.dbo.sysobjects WHERE (name = N'sp_help_job_with_results') AND (type = 'P'))) DROP PROCEDURE sp_help_job_with_results go CREATE PROCEDURE sp_help_job_with_results @job_id UNIQUEIDENTIFIER = NULL, @job_name SYSNAME = NULL, @job_aspect VARCHAR(9) = NULL, @job_type VARCHAR(12) = NULL, @owner_login_name SYSNAME = NULL, @subsystem NVARCHAR(40) = NULL, @category_name SYSNAME = NULL, @enabled TINYINT = NULL, @execution_status INT = NULL, @date_comparator CHAR(1) = NULL, @date_created DATETIME = NULL, @date_last_modified DATETIME = NULL, @description NVARCHAR(512) = NULL AS BEGIN -- If job_id or job_name were not specified there will be only one resultset IF (@job_id IS NULL AND @job_name IS NULL) BEGIN EXEC sp_help_job @job_id, @job_name, @job_aspect, @job_type, @owner_login_name, @subsystem, @category_name, @enabled, @execution_status, @date_comparator, @date_created, @date_last_modified, @description WITH RESULT SETS ( ( 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, has_schedule INT, has_target INT, [type] INT ) ) END ELSE BEGIN -- If job_id or job_name is not null, there will be multiple resultsets EXEC sp_help_job @job_id, @job_name, @job_aspect, @job_type, @owner_login_name, @subsystem, @category_name, @enabled, @execution_status, @date_comparator, @date_created, @date_last_modified, @description WITH RESULT SETS ( ( 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, has_schedule INT, has_target INT, [type] INT ), ( step_id INT, step_name SYSNAME, subsystem NVARCHAR(40) , command NVARCHAR(max) , flags NVARCHAR(4000), cmdexec_success_code INT, on_success_action NVARCHAR(4000), on_success_step_id INT, on_fail_action NVARCHAR(4000), on_fail_step_id INT, [server] SYSNAME, database_name SYSNAME, database_user_name SYSNAME, retry_attempts INT, retry_interval INT, os_run_priority NVARCHAR(4000), output_file_name NVARCHAR(200), last_run_outcome INT, last_run_duration INT, last_run_retries INT, last_run_date INT, last_run_time INT, proxy_id INT ), ( schedule_id INT, schedule_name SYSNAME, [enabled] INT, freq_type INT, freq_interval INT, freq_subday_type INT, freq_subday_interval INT, freq_relative_interval INT, freq_recurrence_factor INT, active_start_date INT, active_end_date INT, active_start_time INT, active_end_time INT, date_created DATETIME, schedule_description NVARCHAR(4000) , next_run_date INT, next_run_time INT, schedule_uid UNIQUEIDENTIFIER, job_count INT ), ( server_id INT, server_name NVARCHAR(30), enlist_date DATETIME, last_poll_date DATETIME, last_run_date INT, last_run_time INT, last_run_duration INT, last_run_outcome TINYINT, last_outcome_message NVARCHAR(1024) ) ) END END GO SET FMTONLY OFF EXEC sp_describe_first_result_set @tsql = N'exec msdb.dbo.sp_help_job_with_results' GO sp_configure 'Ad Hoc Distributed Queries',1 reconfigure SELECT * FROM OPENROWSET ('SQLOLEDB','Server=(local);Trusted_Connection=yes','SET FMTONLY OFF EXEC msdb.dbo.sp_help_job_with_results @execution_status=1') sp_configure 'Ad Hoc Distributed Queries',0 reconfigure
RecordBy	cunay
Record Date	01-02-2017 12:29:39
Düzenle
Kopyala
Sil