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)
 SQL Error 22050. SQL2008

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 this
error 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

Posted - 2010-08-02 : 14:06:57
Try: @query = 'EXEC DB1.dbo.spRpt_PortfolioValueException'

Does that stored procedure have input or output parameters?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

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_PortfolioValueException

CREATE 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 OFF



quote:
Originally posted by tkizer

Try: @query = 'EXEC DB1.dbo.spRpt_PortfolioValueException'

Does that stored procedure have input or output parameters?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Go to Top of Page
   

- Advertisement -