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

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?

Emailing Errors

I'm sort of new to SSIS so I apologize if this is a trivial question:

I setup a package and want it to email me if there is any errors. Right now I got it working to send me a pre-written message about the failure, but I want to load the full task failure message into variable and email it on the email body.

But so far I have not found a way to save the error messages into variables so I can email them. Anyone can help with that one?

As far as I know there is no one system variable that stores the error message. So, you may not be able to use expressions with variables.

Also, keep in mind that you could get multiple errors per package. So, a single variable won't do the job.

An error report is what you basically need.

Assuming you are setting the "OnError" event handler, the error message, container name, etc. are logged in the sysdtslog90 table. You can query the table using the execution guid, which is available as a system variable to the package.

You can then dump the result set into an excel or a text file and email it as an attachment.

|||

How did you set up even a pre-written mail message for errors?

Right now, I have an error log and a success mail message if everything else completes, but an error mail would be awesome.

|||

sorry. i figured it out. i am new to ssis (3 months) and just noticed the event handler tab. please try to control your laughter.

|||

Under email task editor- build an Expression for "Message source"

you could then do something like:

" There was a critical error in the Your Load. Error Msg follows " + @.[System::ErrorDescription]

to bubble up the error message.

sql

Emailing Errors

I'm sort of new to SSIS so I apologize if this is a trivial question:

I setup a package and want it to email me if there is any errors. Right now I got it working to send me a pre-written message about the failure, but I want to load the full task failure message into variable and email it on the email body.

But so far I have not found a way to save the error messages into variables so I can email them. Anyone can help with that one?

As far as I know there is no one system variable that stores the error message. So, you may not be able to use expressions with variables.

Also, keep in mind that you could get multiple errors per package. So, a single variable won't do the job.

An error report is what you basically need.

Assuming you are setting the "OnError" event handler, the error message, container name, etc. are logged in the sysdtslog90 table. You can query the table using the execution guid, which is available as a system variable to the package.

You can then dump the result set into an excel or a text file and email it as an attachment.

|||

How did you set up even a pre-written mail message for errors?

Right now, I have an error log and a success mail message if everything else completes, but an error mail would be awesome.

|||

sorry. i figured it out. i am new to ssis (3 months) and just noticed the event handler tab. please try to control your laughter.

|||

Under email task editor- build an Expression for "Message source"

you could then do something like:

" There was a critical error in the Your Load. Error Msg follows " + @.[System::ErrorDescription]

to bubble up the error message.

Email if errors Exists in the error tables

I am setting my error tables.What i want to do is.If i find any data within my error tables i want an email to be sent to me.

How can i achieve this?

Please let me know

How are you populating those tables? do you want to receive an email on the first/each error or do you want to receive a single email at the end?

|||

Create a SQL Task that returns a value if you want to send an email or not.

Put the returned value in a variable.

Conditionally branch to email if the variable has the found value.

The SQL could be something like SELECT COUNT(1) cnt FROM ErrorTable. Have the SQL task return a single row. In mappings put variable 0 into a variable such as FoundError.

Add an email task, connect the two. Double click the line (constraint) and set it to an expression such as

@.FoundError > 0.

Hope that helps.

|||one single email only

|||

sureshv wrote:

one single email only

Then, Larry's suggestion should work for you. Wait until all data flow are done and then use an Execute SQl task in control flow to check is there were any errors; then you can use an expression in a precedence constraint to decide whether the email should be sent or not. There are another threads in this forums that tackle the how-to-send-an-email question

|||

I have Sql task and in General Tab in the

sql statement : i type SELECT COUNT(1)
FROM dbo.hb_test_error

when i execute and see now it workes fine

result set: from none to singlerow

Then Result set Tab

Variable Name : User::FoundError

Result Name :any name

Then i connect to email task

In precedence constraint editor

Evaluation operator : expression

Expression FoundError >0

i have this error

[Execute SQL Task] Error: Executing the query "SELECT COUNT(1) FROM dbo.hb_test_error" failed with the following error: "Parameter name is unrecognized.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

How do i solve this?

|||Set Result Name to: 0 (that's a zero)