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 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy