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.
Author |
Topic |
NguyenL71
Posting Yak Master
228 Posts |
Posted - 2010-08-02 : 13:38:19
|
Hi,We just upgrade from SQL2005 to SQL2008 and I am getting the errors below in scheduling the Job. It was ran fine in SQL 2005. I try to google but no specific resolution. I guess there was something due with @Query parameter. Does any has seems thiserror before and how to fix it.Thanks in advance.Error Message:Executed as user: MHC\SQLService. Error formatting query, probably invalid parameters [SQLSTATE 42000] (Error 22050). NOTE: The step was retried the requested number of times (1) without succeeding. The step failed.--Testing..DECLARE @AMEmailstr VARCHAR(8000), -- Asset managers. @MDEmailstr VARCHAR(8000), -- Managers 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 Kondaur Capital''s Automated email information system. Please do not reply.'SET @AMEmailstr = (SELECT STUFF((SELECT ';' + RTRIM(Email) FROM ##MailingList WHERE TypeCd = 'AM' FOR XML PATH('')),1,1,'') AS EMailString)-- Remove ; at the end. Look at the above query.SET @MDEmailstr = (SELECT STUFF((SELECT ';' + RTRIM(Email) FROM ##MailingList WHERE (TypeCd IN ('PM', 'PD')) FOR XML PATH('')),1,1,'') AS EMailString)--PRINT @AMEmailstr--PRINT @MDEmailstr EXECute msdb.dbo.sp_send_dbmail @profile_name = 'DoNotReply' ,@recipients = @AMEmailstr ,@copy_recipients = @MDEmailstr ,@blind_copy_recipients = 'JSmith@MHC.com' ,@query = 'DB1.dbo.spRpt_PortfolioValueException' ,@query_attachment_filename = 'ExceptionReportForPortfolioValues.csv' ,@subject = 'Action Required - Collateral Value Approval Needed' ,@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 |
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
NguyenL71
Posting Yak Master
228 Posts |
Posted - 2010-08-02 : 14:22:09
|
It's just a SELECT statement. I just to put EXEC but no luck.EXECute DB1.dbo.spRpt_PortfolioValueExceptionCREATE PROCedure [dbo].[spRpt_PortfolioValueException]( @UserName VARCHAR(255) = '')AS/*************************************************************************************************** Description:****** Return code: 0 -- Success.** 1 -- Failed.**** Written by:**** Written date:**** Modifications:** ----------------------------------** Date: Author: Reasons:** ------------+-----------------------------------------------------------------------***************************************************************************************************/SET nocount ON BEGIN TRY SELECT a.LoanNum AS [KCC Loan Number], CAST(a.LoanStatus AS VARCHAR(20)) AS [KCC Loand Status], CAST(a.PoolNum AS VARCHAR(40)) AS 'Pool', CAST(a.PortfolioDirector AS VARCHAR(30)) AS [Portfolio Director], CAST(a.PortfolioMgr AS VARCHAR(30)) AS [Portfolio Manager], CAST(a.AssetManager AS VARCHAR(30)) AS 'Asset Manager', CAST(a.BorrLast AS VARCHAR(25)) AS [Borrower Last Name], a.PropertyAddr1 AS 'Address', CAST(a.PropertyCity AS VARCHAR(40)) AS [Prop City], a.PropertyState AS [Prop State], a.PropertyZip AS [Prop Zip], ISNULL(CAST(b.CompleteBy AS VARCHAR(30)), '') AS [CDD Approval By], CONVERT(CHAR(10), b.CompleteDate, 101) AS [CDD Approval Date], ISNULL(CAST(REPLACE(CONVERT(VARCHAR(22), CONVERT(MONEY, b.CompleteValue), 1), '.00', '') AS VARCHAR(22)), '') AS [CDD Value], CAST(b.ApprovedBy AS VARCHAR(30)) AS [Valuation Approved By], CONVERT(CHAR(10), b.ApprovedDate, 101) AS [Valuation Date], ISNULL(CAST(REPLACE(CONVERT(VARCHAR(22), CONVERT(MONEY, b.ApprovedValue), 1), '.00', '') AS VARCHAR(22)), '') AS [Valuation Approved Value], CAST(CASE WHEN (b.KondaurChk = 'N' OR b.KondaurChk IS NULL) THEN ' ' ELSE (b.KondaurValUser) END AS VARCHAR(30)) AS [Final Approval By], COALESCE(CONVERT(CHAR(10), b.KondaurDate, 101), '') AS [Final Disposition Date], ISNULL(CAST(REPLACE(CONVERT(VARCHAR(22), CONVERT(MONEY, b.KondaurVal), 1), '.00', '') AS VARCHAR(22)), '') AS [Final Disposition Value], ISNULL(CAST(REPLACE(CONVERT(VARCHAR(22), CONVERT(MONEY, a.BPOVal), 1), '.00', '') AS VARCHAR(22)), '') AS [Collateral Value], CONVERT(CHAR(10), a.BPODate, 101) AS [Collateral Date], COALESCE(c.TotalNumberofComps, 0) AS [Number of Comps] FROM KondaurData AS a JOIN WorksheetTable AS b ON a.LoanNum = b.LoanNum 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 JOIN dbo.fn_tblUserLoans (@UserName) AS d ON (a.LoanNum = d.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','REPURCHASE PENDING')) ORDER BY a.LoanNum ASC; END TRY--------------------------------- -- Error checking. BEGIN CATCH SELECT ERROR_NUMBER() AS 'ErrorNumber' SELECT CAST(ERROR_MESSAGE() AS VARCHAR(150)) AS 'ErrorMessage' SELECT ERROR_SEVERITY() AS 'ErrorSeverity' SELECT CAST(ERROR_PROCEDURE() AS VARCHAR(40)) AS 'ErrorProcedure' SELECT ERROR_LINE() AS 'ErrorLineNumber' END CATCH SET nocount OFFquote: Originally posted by tkizer Try: @query = 'EXEC DB1.dbo.spRpt_PortfolioValueException'Does that stored procedure have input or output parameters?Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog
|
|
|
|
|
|
|
|