Urgent help with SP please.

Last Post 04 Aug 2010 06:21 AM by NguyenL. 0 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages Not Resolved
NguyenL
New Member
New Member

--
04 Aug 2010 06:21 AM
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[red];[/red] -- 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[red];[/red] -- 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[red];[/red] DELETE DailyExceptionValReport WHERE FullName = 'Lou Spampinato' AND EMail = 'lspampinato@kondaur.com'[red];[/red] INSERT DailyExceptionValReport (FullName, EMail, UserType) VALUES ('Lou Spampinato', 'lspampinato@kondaur.com', 'PM') [red];[/red] -- 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[red];[/red] --------------------------------------------------------------------------------- -- 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[red];[/red] go --end
You are not authorized to post a reply.

Acceptable Use Policy