3 part xp_sendmail question concerning sent items, queries and originator

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 Administration (2000)

3 part xp_sendmail question concerning sent items, queries and originator

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-01-24 : 09:12:45

sxt173 writes "Hi,

I've been having some problems with a few things I am trying to do.

1. How do I access the "sent items" on the SQL server. I see how to access the inbox, etc., but I need to do some cleanup (i.e. delete sent items)

2. In the sendmail code I attached below, I am trying to limit the query results sent with the email, so that it only includes items that the specific receipient has access to. It does not seem to like "@query= 'SELECT * FROM VW_MY_VIEW WHERE SM_USER_NUMBER = ' + @SENT_NUMBER"

3. How do you change the "from" part of the email going out, so that I can make the email look like it's coming from "MY COMPANY" or make it use a specific account/mailbox?

DECLARE @SENT_EMAIL VARCHAR(255)
DECLARE @SENT_SUBJECT VARCHAR(255)
DECLARE @SENT_BODY VARCHAR(5000)
DECLARE @SENT_NUMBER VARCHAR(15)

declare EMAIL_CURSOR cursor for

SELECT DISTINCT
f.SM_USER_EMAIL,
'MY COMPANY, System Notification for ' + f.SM_USER_NAME,
'
Dear Valued Supplier,
This is an automated notification...
',
f.SM_USER_NUMBER

FROM
(TE_PROCUREMENT_DEV.dbo.EMAIL_TEST AS f
INNER JOIN TE_PROCUREMENT.dbo.NAFTA_FORM AS g ON g.NAFTA_VENDOR_NUMBER = f.SM_USER_NUMBER)
INNER JOIN TE_PROCUREMENT.dbo.NAFTA_LIST AS h ON h.NAFTA_LIST_VENDOR_NUMBER = f.SM_USER_NUMBER
WHERE
g.NAFTA_AUTHORIZED_SIGNATURE = 'Y'
AND
(h.NAFTA_LIST_HS = ''
OR
h.NAFTA_LIST_CO_ORIGIN = '')

open EMAIL_CURSOR

fetch next from EMAIL_CURSOR
into @SENT_EMAIL,@SENT_SUBJECT,@SENT_BODY,@SENT_NUMBER

while @@FETCH_STATUS=0
begin
exec master.dbo.xp_sendmail
@recipients= @SENT_EMAIL,
@message= @SENT_BODY,
@subject= @SENT_SUBJECT,
@query= 'SELECT * FROM VW_NAFTA_EMAIL_MISSING_PARTS WHERE SM_USER_NUMBER = ' + @SENT_NUMBER

TE_PROCUREMENT.dbo.VW_NAFTA_EMAIL_MISSING_PARTS WHERE SM_USER_NUMBER = @SENT_NUMBER'

fetch next from EMAIL_CURSOR
into @SENT_EMAIL,@SENT_SUBJECT,@SENT_BODY--,@SENT_NUMBER
end
close EMAIL_CURSOR
deallocate EMAIL_CURSOR"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources