Ugh. OK, I'm in well over my head here. I'm getting a couple of errors so I'll just start at the beginning and hope it all makes sense.
I'm planning to have this stored proc run by a scheduled job once a day. It will do three things: (a) grab all the forum posts that were submitted on that day, with their respective thread titles, (b) arrange this information into a nicely formatted list along the lines of:
Thread
Post
Post
Thread
Post
Thread
Post
Post
etc, and (c) email this list to everyone registered who's opted in for this daily spamfest.
Now, what I've currently got is not a stored proc but two chunks of code that, between them, would probably do most of what I need. But I've no idea how to combine them:
This bit will loop through my users and send them all an email
DECLARE @.em1 varchar(200), @.sbj1 varchar(500), @.bdy1 varchar(5000)
declare em_cursor1 cursor for
SELECT email FROM forum_users WHERE digest='yes'
open em_cursor1
fetch next from em_cursor1
into @.em1
while @.@.FETCH_STATUS=0
begin --CREATE EMAIL
select @.sbj1='Forum Daily Digest'
select @.bdy1=('This is where my list of posts goes.')
exec master.dbo.xp_sendmail
@.recipients=@.em1,
@.message=@.bdy1,
@.subject=@.sbj1
fetch next from em_cursor1
into @.em1
end
close em_cursor1
deallocate em_cursor1
and this bit will create something not unlike a list of forum posts, neatly arranged by thread:
SELECT
forum_posts.post_date
, forum_posts.body
, forum_posts.id AS postID
, forum_threads.id
, forum_threads.subject
FROM forum_threads INNER JOIN forum_posts
ON forum_posts.thread_id = forum_threads.id
WHERE day(post_date)=day(getdate())
AND month(post_date)=month(getdate())
AND year(post_date)=year(getdate())
ORDER BY forum_threads.id ASC, forum_posts.post_date ASC
But how do I combine them? Apparently I can't just set the email body variable (in the first chunk) to be the SELECT statement in the second one, because "The ORDER BY clause is invalid in views, inline functions, derived tables, and subqueries, unless TOP is also specified" - which I don't understand at all. And I can't just run that second chunk and drop it all into a few variables that I can reuse each time I loop through the users, because "The text, ntext, and image data types are invalid for local variables", and the body of the post is, of course, stored as text.
Can anyone help?how about ...
DECLARE @.em1 varchar(200)
select @.em1 = email FROM forum_users WHERE digest='yes'
while @.em1 is not null begin--CREATE EMAIL
exec master.dbo.xp_sendmail @.recipients = @.em1
, @.query = 'SELECT forum_posts.post_date ' +
', forum_posts.body ' +
', forum_posts.id AS postID ' +
', forum_threads.id ' +
', forum_threads.subject ' +
'FROM forum_threads INNER JOIN forum_posts ' +
'ON forum_posts.thread_id = forum_threads.id ' +
'WHERE day(post_date)=day(getdate()) ' +
'AND month(post_date)=month(getdate()) ' +
'AND year(post_date)=year(getdate()) ' +
'ORDER BY forum_threads.id ASC, forum_posts.post_date ASC'
, @.subject = 'Forum Daily Digest'
select @.em1 = email FROM forum_users WHERE digest='yes' and email > @.em1
end
WARNING
This is untested and not optimized!|||Hmm, that looks good :) I get a "syntax error on line 6, near '+'", I tried:
@.query = 'SELECT forum_posts.post_date, ' +
' forum_posts.body, ' +
' forum_posts.id AS postID, ' +
' forum_threads.id, ' +
' forum_threads.subject ' +
'FROM forum_threads INNER JOIN forum_posts ' +
'ON forum_posts.thread_id = forum_threads.id ' +
'WHERE day(post_date)=day(getdate()) ' +
'AND month(post_date)=month(getdate()) ' +
'AND year(post_date)=year(getdate()) ' +
'ORDER BY forum_threads.id ASC, forum_posts.post_date ASC'
but that didn't work either. I can't see what's wrong with it.
But assuming this is just a minor syntax error, it's still going to email the results of a query to all my forum users in a fairly unfriendly layout - is there any way I can format this into some sort of pretty email body?|||Sorry that probably should have been:
, @.query = 'SELECT forum_posts.post_date
, forum_posts.body
, forum_posts.id AS postID
, forum_threads.id
, forum_threads.subject
FROM forum_threads INNER JOIN forum_posts
ON forum_posts.thread_id = forum_threads.id
WHERE day(post_date)=day(getdate())
AND month(post_date)=month(getdate())
AND year(post_date)=year(getdate())
ORDER BY forum_threads.id ASC, forum_posts.post_date ASC'
a. "is there any way I can format this into some sort of pretty email body?" you didn't mention that in your original post, is this scope creep?
b. define "pretty email body"|||Ah, that'll do it :) Nice one.
I'm trying to avoid "scope creep", sorry if I wasn't clear; by "pretty email body", I mean... well, objective (b) in my original post. The people who will receive this email are not battle-hardened DBA's, they're... well, they're nurses.
I assume that this email is going to take the form of a blandly-formatted table. I'd prefer to send them something that lists the days posts under subheaders of the thread titles, maybe with a bit of bold text throw in. But don't we then come back to the problem that I can't put text columns into local variables?
------
NB - Ah. I've just tried running that and it seems to be getting into an infinite loop. It continually returns the errors:
ODBC error 208 (42S02) Invalid object name 'forum_threads'.
ODBC error 208 (42S02) Invalid object name 'forum_posts'.
I'm definitely connected ok - I can, for example, run "SELECT * FROM forum_threads" in the same Query Analyser window without trouble. Any ideas what would be causing that?
Thanks...|||change your table references to:
<db name>.<table owner>.forum_threads
<db name>.<table owner>.forum_posts
or
set the xp_sendmail parameter '@.dbuse =' <to the db where your tables are located>.
Books Online has all of this...
as for jazzing up the output, Transact SQL wasn't explicitly designed for that. You could create a perminante table with a single, say varchar(100), attribute and process the results of your main select by build formatted text. The end result would be to select * from your single attribute table returning formatted text.|||Oops - I've just spammed my hotmail account with about 400 identical messages. :cool:
That SQL seems to get itself into an infinite loop - I don't get it. There's only one user record in the database where digest='yes'. Just to confirm, I've got:
DECLARE @.em1 varchar(200)
select @.em1 = email FROM forum_users WHERE digest='yes'
while @.em1 is not null begin--CREATE EMAIL
exec master.dbo.xp_sendmail @.recipients = @.em1
, @.query = 'SELECT his.dbo.forum_posts.post_date
, his.dbo.forum_posts.body
, his.dbo.forum_posts.id AS postID
, his.dbo.forum_threads.id
, his.dbo.forum_threads.subject
FROM his.dbo.forum_threads INNER JOIN his.dbo.forum_posts
ON his.dbo.forum_posts.thread_id = his.dbo.forum_threads.id
WHERE day(his.dbo.forum_posts.post_date)=day(getdate())
AND month(his.dbo.forum_posts.post_date)=month(getdate ())
AND year(his.dbo.forum_posts.post_date)=year(getdate() )
ORDER BY his.dbo.forum_threads.id ASC, his.dbo.forum_posts.post_date ASC'
, @.subject = 'Forum Daily Digest'
select @.em1 = email FROM forum_users WHERE digest='yes' and email > @.em1
end
I didn't understand the "WHERE digest='yes' and email > @.em1" bit - I changed that to "email <> @.em1", but it's still looping infinitely.
And about the formatting - I think I understand what you mean by using another table to temporarily store the output, but not how to drop large text field values into it?|||Again my appologies, that should be
select @.em1 = min(email) FROM forum_users WHERE digest='yes'
and
select @.em1 = min(email) FROM forum_users WHERE digest='yes' and email > @.em1
I am not sure I understand your question about large text fileds!!|||Aha, that's the one. Many thanks.
By the large text fields, I mean that one of the values in that SELECT statement - forum_posts.body - is datatype text, and I need that in a variable or something I can wrap in some sort of formatting. And it won't let me.
I think you're right, that what I'm asking exceeds the capabilities that Transact SQL was designed for. I think I'll probably be better off doing this in server-side code and simply calling that page in a scheduled task once a day - I can have one recordset holding a mailing list, another looping through the day's posts, organising them by thread title, and simply use an ASP mail component to ping it all off. But thanks very much for your help, you've been extremely patient and helped me understand a great deal.|||No problem... I think there are many ways to solve this problem. I would have used Perl, SMTP and a distrabution list in our Exchange server. The key here is to choose something that is easily to write, maintained and use in YOUR environment.
One last thought, and I am NOT sure this is possable, but would a DTS package do the trick? I know you can use VBScript, and send e-mail. Anyone with DTS experiance that can answer this one?
