Urgent help with SP please. - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SQL Server Administration (2008)

Urgent help with SP please.

Author

Topic

NguyenL71
Posting Yak Master

228 Posts

Posted - 2010-08-04 : 11:07:31

Hi,

I scheduled the SP below to send out to users every morning 7:00 A.M. After we upgraded from SQL2005 to SQL2008, it appears to be problem sending out 4 emails each day instead of 1. Users are frustraing with the duplication messages. I tested in Dev Environment is working fine. I don't see any thing wrong with the codes except puting ; at the end. Can you please see anything wrong with the codes or SQL 2008 bugs.
Thank you in advance.

-- I don't know why is sending out 4 time. I count all the semicolon is 7 times.

Here is the SP.

ALTER PROCedure dbo.spBAT_RPT_ExceptionReportForValues
AS
/***********************************************************************************************
**
** Description:
**
**
** Return code: 0 -- Success.
** 1 -- Failed.
**
** Written by:
**
** Written date:
**
** Modifications:
** ----------------------------------
** Date: Author: Reasons:
** ------------+-----------------------------------------------------------------------
**
**
***********************************************************************************************/
SET nocount ON

TRUNCATE TABLE dbo.DailyExceptionValReport;

-- Insert into Asset Managers.
INSERT dbo.DailyExceptionValReport (FullName, EMail, UserType)
SELECT DISTINCT
CAST(a.AssetManager AS VARCHAR(35)) AS 'AssetManager',
CAST(d.Email AS VARCHAR(35)) AS 'EMail',
'AM' AS 'UserType'
FROM KondaurData AS a
JOIN WorksheetTable AS b
ON a.LoanNum = b.LoanNum
JOIN Logins AS d
ON (RTRIM(d.UserName) = RTRIM(a.AssetManager) )
LEFT JOIN ( SELECT a.LoanNum,
COUNT(a.LoanComparableId) AS 'TotalNumberofComps'
FROM LoanComparable AS a
JOIN WorksheetTable AS b
ON (a.LoanNum = b.LoanNum)
AND (a.SetName = b.SetName)
WHERE (b.ActiveSet = 1) -- Active
GROUP BY a.LoanNum ) AS c
ON c.LoanNum = b.LoanNum
WHERE (b.Complete= 'Y')
AND (b.Approved= 'Y')
AND (b.KondaurChk = 'N' OR b.KondaurChk IS NULL)
AND (b.ActiveSet = 1) -- Active
AND (a.Loanstatus IN ('PORTFOLIO', 'Assumption', 'Mod2Liq'))
ORDER BY CAST(a.AssetManager AS VARCHAR(35)) ASC;
-- Insert into PortfolioMgr Managers.
INSERT dbo.DailyExceptionValReport (FullName, EMail, UserType)
SELECT DISTINCT
CAST(a.PortfolioMgr AS VARCHAR(35)) AS 'PortfolioMgr',
CAST(d.Email AS VARCHAR(35)) AS 'EMail',
'PM' AS 'UserType' --PortfolioMgr
FROM KondaurData AS a
JOIN WorksheetTable AS b
ON (a.LoanNum = b.LoanNum)
JOIN Logins AS d
ON (RTRIM(d.UserName) = RTRIM(a.PortfolioMgr) )
LEFT JOIN ( SELECT a.LoanNum,
COUNT(a.LoanComparableId) AS 'TotalNumberofComps'
FROM LoanComparable AS a
JOIN WorksheetTable AS b
ON (a.LoanNum = b.LoanNum)
AND (a.SetName = b.SetName)
WHERE (b.ActiveSet = 1) -- Active
GROUP BY a.LoanNum ) AS c
ON c.LoanNum = b.LoanNum
WHERE (b.Complete= 'Y')
AND (b.Approved= 'Y')
AND (b.KondaurChk = 'N' OR b.KondaurChk IS NULL)
AND (b.ActiveSet = 1) -- Active
AND (a.Loanstatus IN ('PORTFOLIO', 'Assumption', 'Mod2Liq'))
ORDER BY CAST(a.PortfolioMgr AS VARCHAR(35)) ASC;

DELETE DailyExceptionValReport
WHERE FullName = 'Lou Spampinato'
AND EMail = 'lspampinato@kondaur.com';

INSERT DailyExceptionValReport (FullName, EMail, UserType)
VALUES ('Lou Spampinato', 'lspampinato@kondaur.com', 'PM') ;

-- Insert into PortfolioMgr Director.
INSERT dbo.DailyExceptionValReport (FullName, EMail, UserType)
SELECT DISTINCT
CAST(a.PortfolioDirector AS VARCHAR(35)) AS 'Portfolio Director',
CAST(d.Email AS VARCHAR(35)) AS 'EMail',
'PD' AS 'UserType' -- Portfolio Director
FROM KondaurData AS a
JOIN WorksheetTable AS b
ON (a.LoanNum = b.LoanNum)
JOIN Logins AS d
ON (RTRIM(d.UserName) = RTRIM(a.PortfolioDirector) )
LEFT JOIN ( SELECT a.LoanNum,
COUNT(a.LoanComparableId) AS 'TotalNumberofComps'
FROM LoanComparable AS a
JOIN WorksheetTable AS b
ON (a.LoanNum = b.LoanNum)
AND (a.SetName = b.SetName)
WHERE (b.ActiveSet = 1) -- Active
GROUP BY a.LoanNum ) AS c
ON c.LoanNum = b.LoanNum
WHERE (b.Complete= 'Y')
AND (b.Approved= 'Y')
AND (b.KondaurChk = 'N' OR b.KondaurChk IS NULL)
AND (b.ActiveSet = 1) -- Active
AND (a.Loanstatus IN ('PORTFOLIO', 'Assumption', 'Mod2Liq'))
ORDER BY CAST(a.PortfolioDirector AS VARCHAR(35)) ASC;

---------------------------------------------------------------------------------
-- Below is a section to send out EMails.

DECLARE @AMEmailstr VARCHAR(8000), -- Asset managers.
@PMPDEMailstr VARCHAR(8000), -- Portfolio Managers and Portfolio Directors.
@tab CHAR(1),
@Body VARCHAR(2000)

SET @tab = CHAR(9)
SET @body = 'The Valuation Team has updated value but the loan assigned to you in the attached report does not have a Final Approval. Please review the loan assigned to you to make the appropriate Final Approval or Review the value with your Portfolio Manager and/or the Valuations Group.

- Sent from test Capital''s Automated email information system. Please do not reply.'

SET @AMEmailstr = (SELECT STUFF((SELECT ';' + RTRIM(EMail) FROM DailyExceptionValReport WHERE UserType = 'AM' FOR XML PATH('')),1,1,'') AS EMailString)
-- Remove ; at the end. Look at the above query.
SET @PMPDEMailstr = (SELECT STUFF((SELECT ';' + RTRIM(EMail) FROM DailyExceptionValReport WHERE (UserType IN ('PM', 'PD')) FOR XML PATH('')),1,1,'') AS EMailString)

--PRINT @AMEmailstr
--PRINT @PMPDEMailstr

EXECute msdb.dbo.sp_send_dbmail
@profile_name = 'DoNotReply'
,@recipients = @AMEmailstr
--,@recipients = 'PSmith@mhcab.com'
,@copy_recipients = @PMPDEMailstr
,@blind_copy_recipients = 'PSmith@mhcab.com'
,@query = 'EXECute MyDB1.dbo.spRpt_PortfolioValueException'
,@query_attachment_filename = 'ExceptionReportForPortfolioValues.csv'
,@subject = 'Action Required - test...'
,@body = @body
,@attach_query_result_as_file = 1
,@query_result_header = 1
,@query_result_separator = @tab
,@query_result_width = 8000
,@query_result_no_padding = 1;

go

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-08-04 : 11:11:39

The semicolon is not the fault of this. If the procedure sends out 4 emails instead of one, it means the procedure is run 4 times!
Or (a long-shot) the job is set to retry 3 times before failing.

N 56°04'39.26"
E 12°55'05.63"

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-08-04 : 11:19:55

Try to run all your select statements which are used to insert into dbo.DailyExceptionValReport.
In produktion system maybe there are different data than in dev system.

Once you have seen the output I can imagine there are duplicate entries because of some joins or so...

If that isn't the problem then look if the job is running more than one time a day...

No, you're never too old to Yak'n'Roll if you're too young to die.

NguyenL71
Posting Yak Master

228 Posts

Posted - 2010-08-04 : 11:25:18

Thank you both for your reply, I have check duplication records (No dup because select DISTINCT), no set retry, even delete the job and recreate again. Job runs only once a day, I have check everything that I can think of.

Thanks.

quote:
Originally posted by webfred

Try to run all your select statements which are used to insert into dbo.DailyExceptionValReport.
In produktion system maybe there are different data than in dev system.

Once you have seen the output I can imagine there are duplicate entries because of some joins or so...

If that isn't the problem then look if the job is running more than one time a day...

No, you're never too old to Yak'n'Roll if you're too young to die.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-08-04 : 11:35:18

Post the job creation code.

N 56°04'39.26"
E 12°55'05.63"

NguyenL71
Posting Yak Master

228 Posts

Posted - 2010-08-04 : 13:54:14

Here is the Job creation.

USE [msdb]
GO

/****** Object: Job [Daily Exception Report Portfolio Values-6 A.M] Script Date: 08/04/2010 10:51:32 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object: JobCategory [[Uncategorized (Local)]]] Script Date: 08/04/2010 10:51:32 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'Daily Exception Report Portfolio Values-6 A.M',
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'No description available.',
@category_name=N'[Uncategorized (Local)]',
@owner_login_name=N'MyDomain\abcsmith', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [step1_dailyreport] Script Date: 08/04/2010 10:51:32 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'step1_dailyreport',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=4,
@on_fail_step_id=2,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'
EXECute dbo.spBAT_RPT_ExceptionReportForValues;',
@database_name=N'TestDB1',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Failure_EMail] Script Date: 08/04/2010 10:51:32 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Failure_EMail',
@step_id=2,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'USE msdb;

EXECute msdb..sp_send_dbmail @profile_name = ''MySQLMaile''
,@recipients = ''ABCTestuser@TestCompany.com''
,@subject = ''MHC..Daily Exception Report For Portfolio Values - 6 A.M. Failure.''
,@body = ''Need Attention for job: Daily Exception Report For Portfolio Values - 6 A.M. on MHC server.'';
GO',
@database_name=N'master',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Daily - 6 A.M Exception Values Report',
@enabled=1,
@freq_type=4,
@freq_interval=1,
@freq_subday_type=1,
@freq_subday_interval=0,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20100803,
@active_end_date=99991231,
@active_start_time=60500,
@active_end_time=235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

GO

Thank you.

quote:
Originally posted by Peso

Post the job creation code.

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources