July 22, 2010 at 3:40 am
Hi,
i need to remove "rows affected" text from results as shown below from posted Sp. i am using set nocount on
but its not working as expected.
Sp :
IF EXISTS ( SELECT 1 FROM sysobjects WHERE name = 'ap_adm_JobActivity_EmailAlert' AND xtype = 'p')
DROP PROC dbo.ap_adm_JobActivity_EmailAlert
GO
CREATE PROC [dbo].ap_adm_JobActivity_EmailAlert
AS
DECLARE
@profile_name NVARCHAR(100),
@copy_recipients NVARCHAR(500),
@attachments NVARCHAR(200),
@recipients NVARCHAR(500),
@subject NVARCHAR(500),
@body NVARCHAR(max),
@query NVARCHAR(4000),
@CurrentDate datetime
DECLARE @check1 int,
@check2 int,
@check3 int,
@check4 int
DECLARE @query_1 NVARCHAR(2000),
@query_2 NVARCHAR(2000),
@query_3 NVARCHAR(2000),
@query_4 NVARCHAR(2000),
@Recent_failure_time datetime
DECLARE @CurrentTime DATETIME
SELECT @CurrentTime = getdate()
SET NOCOUNT ON
--------------------------------------------------------------------
-- Initializing Mail variables --
--------------------------------------------------------------------
SELECT @subject = 'Job''s Status'
SELECT @body = ''
SELECT @query = 'INSERT INTO OKS_DBA.dbo.[JOB_LIST]
(job_id,job_name,job_status,should_job_run_flag,ignore_job_failure_date_flag,last_job_run,created_date,modified_date)
SELECT job_id,name,enabled,0,0,''1900-01-01'',date_created,date_modified
FROM MSDB.dbo.sysjobs s WHERE NOT EXISTS
(SELECT 1 FROM OKS_DBA.dbo.[JOB_LIST] (NOLOCK) WHERE job_id = s.job_id);
UPDATE jl SET jl.job_status = 1
FROM OKS_DBA.dbo.[JOB_LIST] as jl
INNER JOIN MSDB.dbo.sysjobs as sj
ON sj.job_id = jl.job_id AND sj.enabled <> jl.job_status AND sj.enabled = 1;
UPDATE jl SET jl.job_status = 0
FROM OKS_DBA.dbo.[JOB_LIST] as jl
INNER JOIN MSDB.dbo.sysjobs as sj
ON sj.job_id = jl.job_id AND sj.enabled <> jl.job_status AND sj.enabled = 0;
UPDATE jl SET jl.job_status = -1
FROM OKS_DBA.dbo.[JOB_LIST] AS jl
WHERE NOT EXISTS (SELECT 1 FROM MSDB.dbo.sysjobs (NOLOCK) WHERE job_id = jl.job_id)'
EXEC(@query)
UPDATE jl
SET jl.ignore_job_failure_date_flag =
CASE WHEN last_job_run <> start_execution_date THEN 0 ELSE jl.ignore_job_failure_date_flag END,
jl.last_job_run = ISNULL(start_execution_date ,jl.last_job_run)
FROM (SELECT sv.job_id , sv.name , max(sja.job_history_id) instance_id
FROM msdb.dbo.sysjobs_view sv WITH (NOLOCK)
INNER JOIN msdb.dbo.sysjobactivity sja WITH (NOLOCK)
ON sv.job_id = sja.job_id and job_history_id IS NOT NULL
WHERE sv.enabled = 1
GROUP BY sv.job_id , sv.name
) X
INNER JOIN msdb.dbo.sysjobactivity sjh WITH (NOLOCK)
ON X.instance_id = sjh.job_history_id
INNER JOIN OKS_DBA..JOB_LIST AS jl
ON jl.job_name = X.name
INNER JOIN msdb.dbo.sysjobhistory sja WITH (NOLOCK)
ON X.instance_id = sja.instance_id
WHERE sja.run_status = 0
AND jl.ignore_job_failure_date_flag = 0 ;
SET @query = ''
SELECT @query_1 = 'SELECT ''List of Enabled And New Jobs '';
SELECT ''------------------------------------------'';
SELECT job_name FROM OKS_DBA.dbo.[JOB_LIST]
WHERE job_status = 1 AND should_job_run_flag = 0; '
SELECT @query_2 = 'SELECT CHAR(13) + CHAR(13) + ''List of Disabled And Deleted Jobs'';
SELECT ''------------------------------------------'';
SELECT job_name FROM OKS_DBA.dbo.[JOB_LIST]
WHERE job_status in (0,-1) AND should_job_run_flag = 1 ;'
SELECT @query_3 = 'SELECT CHAR(13) + CHAR(13) + ''List of Failing Jobs'';
SELECT ''------------------------------------------'';
SELECT DISTINCT Name as List_of_jobs_failing,sjh.message
FROM
(
SELECT sv.job_id job_id, sv.name , max(sja.job_history_id) instance_id , max(sja.stop_execution_date) as stop_execution_date
FROM msdb.dbo.sysjobs_view sv WITH (NOLOCK)
INNER JOIN msdb.dbo.sysjobactivity sja WITH (NOLOCK)
ON sv.job_id = sja.job_id and job_history_id IS NOT NULL
WHERE sv.enabled = 1
GROUP BY sv.job_id , sv.name
) X
INNER JOIN msdb.dbo.sysjobhistory sjh WITH (NOLOCK)
ON X.instance_id = sjh.instance_id + 1
INNER JOIN OKS_DBA..JOB_LIST AS jl
ON jl.job_id = X.job_id
WHERE run_status = 0
AND sjh.step_id = 1
AND x.stop_execution_date BETWEEN DATEADD(dd, -3, ''' + CAST(@CurrentTime as varchar(30)) + ''') AND ''' + CAST(@CurrentTime as varchar(30)) +
''' AND CONVERT (DATETIME, RTRIM(run_date))+ ( run_time * 9 + run_time % 10000 * 6 + run_time % 100 * 10 + 25 * run_duration ) / 216e4
BETWEEN DATEADD(dd, -3, ''' + CAST(@CurrentTime as varchar(30)) + ''') AND ''' + CAST(@CurrentTime as varchar(30)) +
''' AND jl.ignore_job_failure_date_flag = 0;'
SELECT @query_4 = 'SELECT CHAR(13) + CHAR(13) + ''List of Failing Jobs in last One Hour '';
SELECT ''------------------------------------------'';
SELECT DISTINCT [Name] as List_of_jobs_failing
FROM
(
SELECT sv.job_id as job_id , sv.name as name , max(sja.stop_execution_date) as stop_execution_date
FROM msdb.dbo.sysjobs_view sv WITH (NOLOCK)
INNER JOIN msdb.dbo.sysjobactivity sja WITH (NOLOCK)
ON sv.job_id = sja.job_id and job_history_id IS NOT NULL
--WHERE sv.enabled = 1
GROUP BY sv.job_id , sv.name
) X
INNER JOIN msdb.dbo.sysjobhistory sjh WITH (NOLOCK)
ON X.job_id = sjh.job_id
INNER JOIN OKS_DBA..JOB_LIST AS jl
ON jl.job_name = X.name
WHERE run_status = 0
AND sjh.step_id = 1
AND x.stop_execution_date BETWEEN DATEADD(hh, -1, ''' + CAST(@CurrentTime as varchar(30)) + ''') AND ''' + CAST(@CurrentTime as varchar(30)) +
''' AND CONVERT (DATETIME, RTRIM(run_date))+ ( run_time * 9 + run_time % 10000 * 6 + run_time % 100 * 10 + 25 * run_duration ) / 216e4
BETWEEN DATEADD(hh, -1, ''' + CAST(@CurrentTime as varchar(30)) + ''') AND ''' + CAST(@CurrentTime as varchar(30)) +
''' AND jl.ignore_job_failure_date_flag = 0;'
-----Code to check if any change occurs--------
SET @check1 = 0
SET @check2 = 0
SET @check3 = 0
SET @check4 = 0
--ap_adm_JobActivity_EmailAlert
SELECT @check1 = 1 FROM OKS_DBA.dbo.[JOB_LIST]
WHERE job_status = 1 AND should_job_run_flag = 0
SELECT @check2 = 1 FROM OKS_DBA.dbo.[JOB_LIST]
WHERE job_status in (0,-1) AND should_job_run_flag = 1
SELECT @check3 = 1 FROM
(
SELECT sv.job_id job_id, sv.name , max(sja.job_history_id) instance_id , max(sja.stop_execution_date) as stop_execution_date
FROM msdb.dbo.sysjobs_view sv WITH (NOLOCK)
INNER JOIN msdb.dbo.sysjobactivity sja WITH (NOLOCK)
ON sv.job_id = sja.job_id and job_history_id IS NOT NULL
WHERE sv.enabled = 1
GROUP BY sv.job_id , sv.name
) X
INNER JOIN msdb.dbo.sysjobhistory sjh WITH (NOLOCK)
ON X.instance_id = sjh.instance_id + 1
INNER JOIN OKS_DBA..JOB_LIST AS jl
ON jl.job_id = X.job_id
WHERE run_status = 0
AND sjh.step_id = 1
AND x.stop_execution_date BETWEEN DATEADD(hh, -1, @CurrentTime) AND @CurrentTime
AND CONVERT (DATETIME, RTRIM(run_date))+ ( run_time * 9 + run_time % 10000 * 6 + run_time % 100 * 10 + 25 * run_duration ) / 216e4
BETWEEN DATEADD(dd, -3, @CurrentTime) AND @CurrentTime
AND jl.ignore_job_failure_date_flag = 0
SELECT @check4 = 1FROM
(
SELECT sv.job_id as job_id , sv.name as name , max(sja.stop_execution_date) as stop_execution_date
FROM msdb.dbo.sysjobs_view sv WITH (NOLOCK)
INNER JOIN msdb.dbo.sysjobactivity sja WITH (NOLOCK)
ON sv.job_id = sja.job_id and job_history_id IS NOT NULL
--WHERE sv.enabled = 1
GROUP BY sv.job_id , sv.name
) X
INNER JOIN msdb.dbo.sysjobhistory sjh WITH (NOLOCK)
ON X.job_id = sjh.job_id
INNER JOIN OKS_DBA..JOB_LIST AS jl
ON jl.job_name = X.name
WHERE run_status = 0
AND sjh.step_id = 1
AND x.stop_execution_date BETWEEN DATEADD(hh, -1, @CurrentTime) AND @CurrentTime
AND CONVERT (DATETIME, RTRIM(run_date))+ ( run_time * 9 + run_time % 10000 * 6 + run_time % 100 * 10 + 25 * run_duration ) / 216e4
BETWEEN DATEADD(hh, -1, @CurrentTime) AND @CurrentTime
AND jl.ignore_job_failure_date_flag = 0
----Concatenating the queries string on conditional Basis-----------------
IF (@check1 = 0 ) SET @query_1 = '';
IF (@check2 = 0 ) SET @query_2 = '';
IF (@check3 = 0 ) SET @query_3 = '';
IF (@check4 = 0 ) SET @query_4 = ''
SET @query = @query_1 + CHAR (13) + @query_2 + CHAR (13) + @query_3 + CHAR (13) + @query_4
--Sending Mails---------------------
IF (SELECT ISNULL(@check1,0) + ISNULL(@check2,0) +ISNULL(@check3,0) + +ISNULL(@check4,0))> 0
EXEC OKS_DBA.dbo.ap_adm_Send_Notification_Mail
@to_category_name = 'Default',
@cc_category_name = 'Default-CC',
@query = @query,
@subject = @subject,
@attach_query_result_as_file = 0,
@query_result_header = 0
List of Disabled And Deleted Jobs
(1 rows affected)
------------------------------------------
(1 rows affected)
SForce_Salesforce_Trigger@Every5min
(1 rows affected)
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
July 22, 2010 at 3:49 am
set nocount on
use at the start of your query/statement
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle
July 22, 2010 at 4:09 am
can you update the SP ?
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
July 22, 2010 at 4:25 am
U dont need to make any changes in the stored proc. Use set nocount on while executing the proc... like...
set nocount on
exec dbo.ap_adm_JobActivity_EmailAlert
set nocount off
July 22, 2010 at 4:31 am
Thanks to all 🙂
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
July 22, 2010 at 4:58 am
In your code, you have:
SELECT @CurrentTime = getdate()
SET NOCOUNT ON
The select before the "SET NOCOUNT ON" is returning your 1 row(s) affected message.
I'd suggest moving "SET NOCOUNT ON" to be the first statement after the "CREATE PROC ... AS" section.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply