Another xp_sendmail question - 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

SQL Server 2000 Forums

SQL Server Development (2000)

Another xp_sendmail question

Author

Topic

JJ297
Aged Yak Warrior

940 Posts

Posted - 2008-03-04 : 15:50:33

Got rid of the lines by getting committing out:
--@query = @myquery

What I want now to do is to add the title (@title) in the message section below, any ideas?

declare @requestoremail varchar(75), @myquery VARCHAR(4000), @title varchar(250)

Declare GetEmails cursor for

select requestors.requestoremail --selecting person to send email too

from spdtlibrary.dbo.libraryrequest as libraryrequest

join spdtlibrary.dbo.requestors as requestors on requestors.requestorid=libraryrequest.requestorid

join spdtlibrary.dbo.Titles as titles on Titles.Titleid = Libraryrequest.titleid

WHERE duedate < DATEADD(day, DATEDIFF(day, 0, CURRENT_TIMESTAMP) - 7, 0)

OPEN GetEmails

FETCH NEXT FROM GetEmails INTO @requestoremail

WHILE @@FETCH_STATUS >= 0

BEGIN

Print @requestoremail

set @myquery = 'select Titles.Title, convert(varchar,libraryrequest.duedate,101)as Due

from spdtlibrary.dbo.libraryrequest as libraryrequest

join spdtlibrary.dbo.requestors as requestors on requestors.requestorid=libraryrequest.requestorid

join spdtlibrary.dbo.Titles as titles on Titles.Titleid = Libraryrequest.titleid

WHERE duedate < DATEADD(day, DATEDIFF(day, 0, CURRENT_TIMESTAMP) - 7, 0) and requestoremail= ''' + @requestoremail + ''''

execute master..xp_sendmail

@recipients = @requestoremail,

@subject = 'Over Due Resource Library Item',

@message = 'Our records indicate that the item you recently borrowed (WANT TO ADD THE TITLE HERE) from ETDTs Resource Library has not yet been returned. Please FEDEX it to the address below as soon as possible so that it is available for other employees to utilize. Please place a post-it note on the item to identify yourself as the requestor. (Please check with your manager about FEDEX mailing procedures in your office.) Thank you.

--@query = @myquery

FETCH NEXT FROM GetEmails INTO @requestoremail;

END

close GetEmails

deallocate GetEmails

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-03-04 : 15:55:06

You'll need to break @message apart. SET @message = '...' + @title + '...'

And your question isn't about xp_sendmail but rather about variables and appending data.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

JJ297
Aged Yak Warrior

940 Posts

Posted - 2008-03-05 : 08:40:10

Tara,

I tried what you suggested but I get an empty email message. Here's the code.

declare @requestoremail varchar(75), @myquery VARCHAR(4000), @title varchar(250),
@message varchar(4000)

Declare GetEmails cursor for
select requestors.requestoremail --selecting person to send email too
from spdtlibrary.dbo.libraryrequest as libraryrequest
join spdtlibrary.dbo.requestors as requestors on requestors.requestorid=libraryrequest.requestorid
join spdtlibrary.dbo.Titles as titles on Titles.Titleid = Libraryrequest.titleid
WHERE duedate < DATEADD(day, DATEDIFF(day, 0, CURRENT_TIMESTAMP) - 7, 0)
OPEN GetEmails
FETCH NEXT FROM GetEmails INTO @requestoremail
WHILE @@FETCH_STATUS >= 0
BEGIN
Print @requestoremail
set @myquery = 'select Titles.Title, convert(varchar,libraryrequest.duedate,101)as Due
from spdtlibrary.dbo.libraryrequest as libraryrequest
join spdtlibrary.dbo.requestors as requestors on requestors.requestorid=libraryrequest.requestorid
join spdtlibrary.dbo.Titles as titles on Titles.Titleid = Libraryrequest.titleid
WHERE duedate < DATEADD(day, DATEDIFF(day, 0, CURRENT_TIMESTAMP) - 7, 0) and requestoremail= ''' + @requestoremail + ''''

execute master..xp_sendmail
@recipients = @requestoremail,
@subject = 'Over Due Resource Library Item'
set @message = 'Our records indicate that the item you recently borrowed' + @title + ' from ETDTs Resource Library has not yet been returned. Please FEDEX it to the address below as soon as possible so that it is available for other employees to utilize. Please place a post-it note on the item to identify yourself as the requestor. (Please check with your manager about FEDEX mailing procedures in your office.) Thank you.

Regional Training Facility
444 North 3rd St.
4th Floor
Washington, PA 19123
Attention: Tonia Porter'
--@query = @myquery

FETCH NEXT FROM GetEmails INTO @requestoremail
END
close GetEmails
deallocate GetEmails

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-03-05 : 08:50:11

sounds like @title is probably NULL ??

Em

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-03-05 : 08:55:35

...actually. where are you setting the value of @title? coz i don't see it it in your above code?

Em

JJ297
Aged Yak Warrior

940 Posts

Posted - 2008-03-05 : 09:04:15

Title does have something in the database. How do I set the value of title? Don't quite understand.

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-03-05 : 09:07:58

you've declared the variable @title, but because you haven't given it a value it's currently NULL. when you try and concatenate like this...

'some text, blah blah' + NULL + 'more text in your message'

the result will always be NULL, hence your empty message.

is it the result of the query you built for @myquery that you actually want in @title? if so, what do you want to do with the date? what if there's mulitiple records returned?

Em

JJ297
Aged Yak Warrior

940 Posts

Posted - 2008-03-05 : 09:15:30

Okay I do want to add the DueDate in the message too. I looked in the database and I do have identical duedates but different titles. How would I set this up? I've declared duedate and added it to the message:

declare @requestoremail varchar(75), @myquery VARCHAR(4000), @title varchar(255),
@message varchar(8000), @duedate (datetime)

Declare GetEmails cursor for
select requestors.requestoremail --selecting person to send email too
from spdtlibrary.dbo.libraryrequest as libraryrequest
join spdtlibrary.dbo.requestors as requestors on requestors.requestorid=libraryrequest.requestorid
join spdtlibrary.dbo.Titles as titles on Titles.Titleid = Libraryrequest.titleid
WHERE duedate < DATEADD(day, DATEDIFF(day, 0, CURRENT_TIMESTAMP) - 7, 0)
OPEN GetEmails
FETCH NEXT FROM GetEmails INTO @requestoremail
WHILE @@FETCH_STATUS >= 0
BEGIN
Print @requestoremail
set @myquery = 'select Titles.Title, convert(varchar,libraryrequest.duedate,101)as Due
from spdtlibrary.dbo.libraryrequest as libraryrequest
join spdtlibrary.dbo.requestors as requestors on requestors.requestorid=libraryrequest.requestorid
join spdtlibrary.dbo.Titles as titles on Titles.Titleid = Libraryrequest.titleid
WHERE duedate < DATEADD(day, DATEDIFF(day, 0, CURRENT_TIMESTAMP) - 7, 0) and requestoremail= ''' + @requestoremail + ''''

execute master..xp_sendmail
@message = @message,
@recipients = @requestoremail,
@subject = 'Over Due Resource Library Item'
set @message = 'Our records indicate that the item you recently borrowed' + @title + ' which should have been returned on ' + @DueDate + 'from ETDTs Resource Library has not yet been returned. Please FEDEX it to the address below as soon as possible so that it is available for other employees to utilize. Please place a post-it note on the item to identify yourself as the requestor. (Please check with your manager about FEDEX mailing procedures in your office.) Thank you.'

FETCH NEXT FROM GetEmails INTO @requestoremail
END
close GetEmails
deallocate GetEmails

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-03-05 : 09:21:10

you still need to assign values to those variables (@title and now @duedate). What about multiple values? do you just want to list them? what about different titles on different dates? a list wouldn't seem to make much sense in that context.

honestly?... if it were me, i'd keep you nice descriptive message generic and then add the titles and dues dates at the end of the message or as an attachment. but that's just me

Em

JJ297
Aged Yak Warrior

940 Posts

Posted - 2008-03-05 : 09:26:09

Okay how do I just list the items at the end of the message instead of adding it inside the message. The client put this message together I just thought I would add the title and duedate and try to bold it.

Yes I would love to list the title along with the multiple duedates in the email. Thanks.

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-03-05 : 09:31:57

actually i don't have a 2000 install to test it here and it's been a while, but check in Book OnLine for the xp_sendmail parameters. I think it's just @query (which you've already defined), also look at @attach_results / @attachments

Em

JJ297
Aged Yak Warrior

940 Posts

Posted - 2008-03-05 : 09:40:10

Okay thanks I will look into that.

JJ297
Aged Yak Warrior

940 Posts

Posted - 2008-03-05 : 14:13:41

Thanks for your help elancaster I got it to work by looking on Books Online:

I was able to instert @title and @DueDate in the message

set nocount on
declare @msg varchar(2000),
@requestoremail varchar(75),
@title varchar(255),
@DueDate datetime,
@qry varchar(1000)

declare email_cur CURSOR
for

select requestoremail, title, duedate
from spdtlibrary.dbo.libraryrequest as libraryrequest
join spdtlibrary.dbo.requestors as requestors on requestors.requestorid=libraryrequest.requestorid
join spdtlibrary.dbo.Titles as titles on Titles.Titleid = Libraryrequest.titleid
WHERE duedate < DATEADD(day, DATEDIFF(day, 0, CURRENT_TIMESTAMP) - 7, 0) and returndate is null
open email_cur
fetch next from email_cur into @requestoremail, @title, @duedate
while @@Fetch_status=0
begin

set @msg='Dear ' + @requestoremail + ',

Our records indicate that the item you recently borrowed from ETDTs Resource Library:

' + @title + ' was due back ' + convert(varchar(10), @duedate, 101) + '

has not been returned. Please FEDEX it to the address below as soon as possible so that it is available for other employees to utilize. Please place a post-it note on the item to identify yourself as the requestor. (Please check with your manager about FEDEX mailing procedures in your office.) Thank you.

Regional Training Facility
444 North 3rd St.
4th Floor
Washington, PA 19123
Attention: Tonia Porter'

set @qry = 'select Titles.Title, convert(varchar,libraryrequest.duedate,101)as DueDate
from spdtlibrary.dbo.libraryrequest as libraryrequest
join spdtlibrary.dbo.requestors as requestors on requestors.requestorid=libraryrequest.requestorid
join spdtlibrary.dbo.Titles as titles on Titles.Titleid = Libraryrequest.titleid
WHERE duedate < DATEADD(day, DATEDIFF(day, 0, CURRENT_TIMESTAMP) - 7, 0) and requestoremail= ''' + @requestoremail + ''''

exec master.dbo.xp_sendmail @recipients=@requestoremail, @message=@msg,
@subject='Over Due Item'

fetch next from email_cur into @requestoremail, @title, @Duedate

end
deallocate email_cur

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources