DB mail issue - 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

General SQL Server Forums

New to SQL Server Programming

DB mail issue

Author

Topic

chris_cs
Posting Yak Master

223 Posts

Posted - 2011-05-12 : 10:17:03

I'm using the following code to try and send an email:

DECLARE @tableHTML  NVARCHAR(MAX) ;

SET @tableHTML =
    N'<H1>WIP Alert</H1>' +
    N'<table border="1">' +
    N'<tr><th>File No.</th><th>Total WIP</th></tr>' +
	CAST ( (SELECT C.IRN, SUM(W.LOCALVALUE) AS TOTALWIP
				FROM INPRO.DBO.CASES C
				INNER JOIN INPRO.DBO.CASENAME CN ON CN.CASEID = C.CASEID AND CN.NAMETYPE = 'D'
				INNER JOIN INPRO.DBO.NAME N ON N.NAMENO = CN.NAMENO
				INNER JOIN INPRO.DBO.CASEEVENT CE ON CE.CASEID = C.CASEID AND CE.EVENTNO = -4
				INNER JOIN INPRO.DBO.WORKINPROGRESS W ON W.CASEID = C.CASEID
				WHERE N.NAMECODE = 'I1889400'
				AND C.CASECATEGORY <> 'X'
				AND DATEDIFF("D", CE.EVENTDATE, W.TRANSDATE) > 30
				GROUP BY C.IRN)
				ORDER BY 2 DESC 
    AS NVARCHAR(MAX)) +
    N'</table>' ;

EXEC MSDB.DBO.SP_SEND_DBMAIL
	@RECIPIENTS = 'address@here.com',
	@BODY = @tableHTML,
	@SUBJECT = 'TEST SUBJECT'

The select statement gives me the desired results when run on it's own, but when used as the body of my email I get the message:

Msg 116, Level 16, State 1, Line 3
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

I can't see what the issue is so wondered if someone else had any ideas?

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2011-05-12 : 10:28:27

1- You cann't apply CAST for two columns i.e. CAST(C.IRN, SUM(W.LOCALVALUE) as varchar(max)) NOT ALLOWED. you can use it as CAST(C.IRN AS VARCHAR(50)), CAST(SUM(W.LOCALVALUE) AS VARCHAR(MAX))
2- Your Select statment may return more then one records which it should not, it should return only one record which can be concatinated later on with other values

--------------------------
http://connectsql.blogspot.com/

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2011-05-12 : 10:34:37

--It should look like as follow (Note:code just parse not test fully)

DECLARE @tableHTML NVARCHAR(MAX) ;

SET @tableHTML =
N'<H1>WIP Alert</H1>' +
N'<table border="1">' +
N'<tr><th>File No.</th><th>Total WIP</th></tr>' +
(SELECT TOP (1) CAST(C.IRN AS VARCHAR(50))+','+ CAST(SUM(W.LOCALVALUE) AS VARCHAR(100))
FROM INPRO.DBO.CASES C
INNER JOIN INPRO.DBO.CASENAME CN ON CN.CASEID = C.CASEID AND CN.NAMETYPE = 'D'
INNER JOIN INPRO.DBO.NAME N ON N.NAMENO = CN.NAMENO
INNER JOIN INPRO.DBO.CASEEVENT CE ON CE.CASEID = C.CASEID AND CE.EVENTNO = -4
INNER JOIN INPRO.DBO.WORKINPROGRESS W ON W.CASEID = C.CASEID
WHERE N.NAMECODE = 'I1889400'
AND C.CASECATEGORY <> 'X'
AND DATEDIFF("D", CE.EVENTDATE, W.TRANSDATE) > 30
GROUP BY CAST(C.IRN AS VARCHAR(50))
)
+
N'</table>' ;

EXEC MSDB.DBO.SP_SEND_DBMAIL
@RECIPIENTS = 'address@here.com',
@BODY = @tableHTML,
@SUBJECT = 'TEST SUBJECT'

--------------------------
http://connectsql.blogspot.com/

chris_cs
Posting Yak Master

223 Posts

Posted - 2011-05-12 : 10:36:31

Thanks for the help, but I actually managed to fix it by adding FOR XML PATH('tr'), TYPE to the end of my select statement

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources