Prev: Help needed on 3 table query
Next: debug sql, Watch, Command Window, see cotent
From: LN on 2 Aug 2010 13:41
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(a)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
 | 
Pages: 1
Prev: Help needed on 3 table query
Next: debug sql, Watch, Command Window, see cotent