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 2008 Forums
 Transact-SQL (2008)
 Disable Mail Sent message

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 string

Jim

Everyday I learn something that somebody else already knew
Go to Top of Page

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)
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page
   

- Advertisement -