February 5, 2008 at 12:14 pm
I have production SQL Server 2000 and dev SQL Server 2005 on my machine. This is an awkward situation when I have to develop on 2005 and then try it on 2000 and sometimes it doesn't work and I have to downgrade it.
I need to send email from the stored procedure in case file was not found. I configured my 2005 server to send mail :
BEGIN
SET NOCOUNT ON
-- do some other actions
DECLARE @body VARCHAR(1024)
SET @body = 'Please check. '+
CONVERT(VARCHAR, GETDATE())
EXEC master..xp_sendmail
@recipients='test@test.com',
@message = @body,
@subject = 'Warning: file was not found'
END
I get an error on SQL Server 2005:
Msg 17985, Level 16, State 1, Line 0
xp_sendmail: Procedure expects parameter @user, which was not supplied.
When I run same thing on the production SQL 2000, I get a different error:
Error: xp_sendmail: Your SQL Server Service is running under the local system account. You need to change your SQL Server Service account ro run under a domain account in order to complete this operation (State:37000, Native Code: 4673)
What can it be? Is there any simple code to use to send email from SP?
February 5, 2008 at 2:34 pm
I think the underlying cause is the same on both instances. SQL Server Service must run as a domain account in order to send mail using xp_sendmail. If it's a local account on both SQL 2000 and SQL 2005, xp_sendmail won't work.
BTW, xp_sendmail is deprecated in SQL 2005 and will probably be removed in a future version. You should use database mail in SQL 2005
Greg
February 7, 2008 at 11:43 am
Do you have any simple code snippet for database mail in SQL 2005 ?
February 7, 2008 at 12:05 pm
Hello Vika,
You can refer to BOL at this link
http://msdn2.microsoft.com/en-us/library/ms190307.aspx
It is simple to use database mail as said by earlier member.
Thanks
Lucky
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply