October 9, 2009 at 8:04 am
I have a sp that generates and email and the results of a query are included.
here is my code...
DECLARE
@SITE VARCHAR(30),
@PROPID INT,
@EMAIL VARCHAR(30),
@CMD VARCHAR(500),
@SCODE VARCHAR(8)
DECLARE SITES CURSOR STATIC
FOR
SELECT DISTINCT
RTRIM(SUBGROUP6) SITE
FROM
ATTRIBUTES
WHERE
SUBGROUP6 <> ''
OPEN SITES
FETCH FROM SITES INTO @SITE
WHILE @@FETCH_STATUS = 0
BEGIN
SET @CMD = 'SELECT TOP 2
LEFT(T.SFIRSTNAME + '' '' + T.SLASTNAME, 30) RESIDENT,
LEFT(CONVERT(VARCHAR, T.DTMOVEIN, 101), 10) MOVEINDATE,
T.SPHONENUM0 HOME,
T.SPHONENUM1 WORK,
T.SPHONENUM3 CELL
FROM
RES.DBO.TENANT T
WHERE
T.DTMOVEIN BETWEEN GETDATE() - 31 AND GETDATE() - 14 AND
T.HPROPERTY IN (SELECT HPROP FROM ATTRIBUTES WHERE RTRIM(SUBGROUP6) = ''' + @SITE + ''')'
EXEC MASTER.DBO.XP_SENDMAIL
@RECIPIENTS = 'emailaddress@business.com',
@query = @cmd,
@SUBJECT = 'Move In Survey',
@MESSAGE = 'Please follow this link to the survey.',
@attach_results = 'false',
@WIDTH = 600 ;
FETCH FROM SITES INTO @SITE
END
CLOSE SITES
DEALLOCATE SITES
the problem is with the last where clause. if i remove it, all works fine. but with it in the query no emails are genereated. what is wrong with the last statement? also is there anyway to find an error log of sorts that would give me info as to why this failed?
thanks
jim
October 9, 2009 at 8:40 am
Do you mean the WHERE clause in the cursor definition or the WHERE clause being set in the @cmd variable?
I would think that an email without any records would be sent if it is in the query for the email.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 9, 2009 at 9:39 am
The where clause in the @cmd variable.
Thanks
Jim
October 7, 2010 at 10:29 am
I think you need to specify the database context for the table in the where clause. You handled this in the main query but not in the sub query.
October 7, 2010 at 11:29 am
Kris has caught the problem. I didn't notice.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
November 10, 2010 at 1:35 am
kris.womack (10/7/2010)
I think you need to specify the database context for the table in the where clause. You handled this in the main query but not in the sub query.
I also think so.
__________________
Welcome to forum!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply