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 |
sevtech
Starting Member
3 Posts |
Posted - 2012-11-19 : 09:17:13
|
Hi all,Bit of a strange request this. I'm using SQL Mail within SQL Server 2008 (and yes I know I should use Database Mail instead, but network security prevents us using SMTP so I have to use SQL Mail).We use SAS EG as basically an ETL tool (as well as various other things outside the scope of this). SAS is importing some data into SQL Server and then running a stored proc, which updates some figures and then sends an email confirming that it has completed, so that we know we can run reports that depend on it. The problem is, SAS is reading the "Mail Sent" message that xp_sendmail returns as a warning. This is bad for SAS, as having warnings prevents us from full automation in control-m, and also violates S-Ox controls.Is there any way I can stop xp_sendmail returning that message, or suppress it?My code is similar to:select @strSQL = 'exec servername.master.dbo.xp_sendmail @recipients = N''' + @recipents + ''', @copy_recipients = N''' + @copyrecipients + ''', @subject = N''' + @title + ''', @message = N''' + @message + ''''exec(@strSQL)Thanks in advance for your help.---------------sevtech |
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2012-11-19 : 12:21:11
|
Try Adding ", NO_OUTPUT" to the end of your stringJimEveryday I learn something that somebody else already knew |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2012-11-19 : 12:27:45
|
According to Books Online it doesn't support NO_OUTPUT and doesn't seem to have any other way to turn off the successful message.Also keep in mind that xp_sendmail is gone in SQL Server 2012, if you plan on upgrading you'll need to find an alternative. And properly configured, there's no security issues with SMTP. (which is obvious if you're sending or receiving external email; it's just as secure/insecure as Exchange) |
|
|
sevtech
Starting Member
3 Posts |
Posted - 2012-11-20 : 04:09:06
|
SMTP requests are blocked by our network to external mail servers, and the SMTP port on our Exchange server is blocked to us - it's only open for IT supported servers, which this isn't. The security is imposed by IT, rather than with SMTP itself. We tend to have to be creative here to find a way around IT restrictions, as they won't provide any support to smaller scale projects (if it's not saving millions, it's not a priority and therefore does not get worked on). So we won't be upgrading SQL Server any time soon, even if we had a copy of 2012 available (we also have zero budget for software etc. but have one copy of 2008 IT have given us). The joys of working for a global organisation!Is there any way I can get the output going into a temporary table or a variable rather than returning as a message?---------------sevtech |
|
|
sevtech
Starting Member
3 Posts |
Posted - 2012-11-20 : 04:28:36
|
OK, I've got an idea for a messy workaround I will try - using xp_cmdshell to run a batch file that in turn executes the stored proc that sends the email, and specifying no_output on the xp_cmdshell command.---------------sevtech |
|
|
|
|
|
|
|