SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Disable Mail Sent message
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sevtech
Starting Member

United Kingdom
3 Posts

Posted - 11/19/2012 :  09:17:13  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
2869 Posts

Posted - 11/19/2012 :  12:21:11  Show Profile  Reply with Quote
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

USA
15683 Posts

Posted - 11/19/2012 :  12:27:45  Show Profile  Visit robvolk's Homepage  Reply with Quote
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

United Kingdom
3 Posts

Posted - 11/20/2012 :  04:09:06  Show Profile  Reply with Quote
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

United Kingdom
3 Posts

Posted - 11/20/2012 :  04:28:36  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000