Results 1 to 10 of 10
  1. #1
    Join Date
    Jan 2002
    Posts
    189

    Unanswered: embedded loops and invalid local variables

    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
    Code:
    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:
    Code:
    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?
    Last edited by Spudhead; 09-30-02 at 11:22.

  2. #2
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    how about ...

    Code:
    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!
    Paul Young
    (Knowledge is power! Get some!)

  3. #3
    Join Date
    Jan 2002
    Posts
    189
    Hmm, that looks good I get a "syntax error on line 6, near '+'", I tried:
    Code:
    @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?

  4. #4
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    Sorry that probably should have been:
    Code:
                                , @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"
    Paul Young
    (Knowledge is power! Get some!)

  5. #5
    Join Date
    Jan 2002
    Posts
    189
    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...

  6. #6
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    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.
    Paul Young
    (Knowledge is power! Get some!)

  7. #7
    Join Date
    Jan 2002
    Posts
    189
    Oops - I've just spammed my hotmail account with about 400 identical messages.

    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:
    Code:
    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?

  8. #8
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    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!!
    Paul Young
    (Knowledge is power! Get some!)

  9. #9
    Join Date
    Jan 2002
    Posts
    189
    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.

  10. #10
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    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?
    Paul Young
    (Knowledge is power! Get some!)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •