October 27, 2009 at 4:27 pm
I've gone through and setup an account in DB Mail and have been able to send. However, i have need to send emails out that contain credentials to get into other Apps that we develop. However, while trying to accomplish this, i continue to hit more than a few snags.
I have figured out a way to get multiple recipients. However, i have to get a username and password into the Body based on the email, or a user id. I have spent a few hours working around with cursors and temp tables to try to accomplish this but, to no avail. If there are any ideas, please post.
txtSignature_CommentEmoticon(':)');
đŸ™‚
October 30, 2009 at 11:51 am
I implemented a similar thing with cursors.
I cant see your difficulties, multiple recipients can be added with a simple semi-colon separated list.
Finally, iterate over the cursor and call the senddbmail function for each client in your list.
cheers!
October 30, 2009 at 12:01 pm
Another way to have multiple recipients and not having to change the SQL Mail account is the create an alias account and in your mail server sent it to the right people.
Pedro
October 30, 2009 at 12:22 pm
You mention multiple recipients, and a username and password. Are you sending the same username and password to each person, or does each get unique data?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 2, 2009 at 2:07 pm
Actually I figured it out not long after i made the post. I had actually never spent any time with cursors before, and what i needed was to have each email address get the username and password associated with it sent to that address. Once i figured out an appropriate way to incorporate a unique id, i made it work. Here is the code i used:
declare @userid int,
@email varchar(100),
@username varchar(100),
@password varchar(100),
@bodyTEXT varchar(1000)
Declare userid Cursor For
Select userid from dbo.Security
Open userid
set @userid = 0
set @email = ''
set @username = ''
set @password = ''
Fetch From userid into @userid
While @@FETCH_STATUS = 0
Begin
Set @email = (Select top 1 email from dbo.Security where UserID = @userid)
Set@username = (select Top 1 username from dbo.Security where UserID = @userid)
Set @password = (Select Top 1 password from dbo.Security where UserID = @userid)
set @bodyTEXT = 'Some Body message. Blah Blah Blah All text.
Here is your logon information:
Username: ' + @username + '
Password: ' + @password + '
If there are any questions about this email or the website, please call.'
Fetch next from userid into @userid
exec dbo.sp_send_dbmail @profile_name = 'Support', @recipients = @email, @subject = 'Security Login Information',
@body = @bodyTEXT
End
Close userid
deallocate userid
Now, I know that some of it is a little different, but i am still only a beginner. But this will accomplish what i need to get it done.
Thanks for the Responses! If you have any suggestions to make this better, i'll be glad to read/try them.
txtSignature_CommentEmoticon(':)');
đŸ™‚
November 10, 2009 at 11:25 pm
Most excellent. Thank you! I just modified this code to send appointment reminders and it may save us up to 100 reminder calls a week.
Cheers,
Eric
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply