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.
| Author |
Topic |
|
martinch
Starting Member
35 Posts |
 Posted - 2004-11-19 : 08:14:50
|
| Hi all,I'm trying to use SQL Server's in-built e-mail function from a Stored Proc, but I'm having some problems with it :(We've got a database of students, with a scheduled Stored Procedure running every night. This SP finds all records in the DB matching a criteria, and uses XP_SendMail to send an e-mail to them (it also writes an entry in our log table). The problem is, it's decidedly flaky - sometimes it will send the e-mail fine, other times it'll say it has, and I'll never receive it (for testing purposes, everything is sent to me). I know it's running and "sending" the e-mail, because of what it's writing in our log. Out of the 7-or-so times it's run (on the same data), I've only received 3 messages (instead of the 7 I should have).Code overview is:* Call XP_StartMail & log result* Get Recordset & build recipient list* Call XP_SendMail with recipient list & log result* Call XP_StopMail & log resultAny ideas as to why it's being flaky? Or is this normal/our server? Also, is it possible for SQL Server to send e-mail using XP_SendMail without needing Outlook open at the same time?Also, is there any way of getting it to keep the sent e-mail in the Sent Items folder so we can view it using Outlook (just to prove things to people who haven't received things)? At the moment, we can't seem to find them.Sorry if this is a basic question, or if it's been covered before, but I'm new to SQL Server, and search didn't find anything...Thanks in advance! |
|
|
JimL
SQL Slinging Yak Ranger
1537 Posts |
Posted - 2004-11-19 : 08:44:36
|
| There should be no reason to have outlook open.Few things to check.1. Outlook needs to be installed on the SQL server and have its own mailbox.2. SQL mail service needs to be configured to that instance of outlook.3. All Xp_sendmail must be from the servers point of view.JimUsers <> Logic |
 |
|
|
martinch
Starting Member
35 Posts |
Posted - 2004-11-19 : 08:51:18
|
Thanks for the reply Jim!quote:
JimL:
"There should be no reason to have outlook open."
Hmm, that's interesting - when we were generating test messages, SQL Server would say it sent it when we didn't have Outlook open (on the server), but it wouldn't - it only seemed to work with Outlook open...Point 1: YupPoint 2: YupPoint 3: Not sure what you mean here...  It's starting to look like we've not configured it properly...  - Martin |
|
|
|
JimL
SQL Slinging Yak Ranger
1537 Posts |
Posted - 2004-11-19 : 09:06:10
|
| Outlook open where?on your local computer or on the server?JimUsers <> Logic |
|
|
|
martinch
Starting Member
35 Posts |
Posted - 2004-11-19 : 09:09:30
|
quote:
JimL:
"Outlook open where? On your local computer or on the server?"
It (SQL Server) only seemed to want to send e-mail if Outlook was open on the server.Sorry for not being very clear on this (obviously I haven't been)... - Martin |
|
|
|
martinch
Starting Member
35 Posts |
Posted - 2004-11-19 : 09:32:18
|
Well, it would appear that I was incorrect to say it didn't send the messages, as I've just been deluged with them - it would appear that they got stuck in the mail spool for over a day (they were all sent at about 10am yesterday - it's now 2:30!!)!! Sorry! Thanks for your replies Jim!  |
|
|
|
|
|
|
|
|
Problems with SQL Mail