August 29, 2001 at 11:02 pm
How to configure Sql mail by using the store procedure xp_sendmail..
thanks in advance
from
alkesh
August 31, 2001 at 6:29 pm
Xp_sendmail only sends mail, not used to configure it. Are you having trouble with the syntax for xp_sendmail, or is it failing?
Andy
September 2, 2001 at 10:56 am
You need to start SQL Server Agent and then SQLMail. I think then you would be able to use xp_sendmail perfectly.
Also if you are using NT Web Server, you have another option of sending mails. ie. by using the CDO NTS component, this is the same component that we use in ASP for sending mails.
Paras Shah
Evision Technologies
Mumbai, India
Paras Shah
Evision Technologies
Mumbai, India
September 2, 2001 at 10:59 am
Andy, is there anyway that we can start SQL Server Agent by stored procedures, if is not started. this is because i am facing a problem on my shared web server where the sql server agent service does not start up sometimes.
Paras Shah
Evision Technologies
Mumbai, India
Paras Shah
Evision Technologies
Mumbai, India
September 2, 2001 at 11:04 am
this is the script that i had browsed thru in the past..
CREATE PROCEDURE SendMail_sp (@FROM NVARCHAR(255), @TO NVARCHAR(255), @SUBJECT NVARCHAR(255), @BODY NVARCHAR(4000)) AS
DECLARE @Object int
DECLARE @Hresult int
DECLARE @ErrorSource varchar (255)
DECLARE @ErrorDesc varchar (255)
DECLARE @V_BODY NVARCHAR(4000)
DECLARE @hr int
DECLARE @src varchar(255), @desc varchar(255)
EXEC @Hresult = sp_OACreate 'CDONTS.NewMail', @Object OUT
IF @Hresult = 0 begin
--SET SOME PROPERTIES
SET @V_BODY = '' + @BODY
EXEC @Hresult = sp_OASetProperty @Object, 'From', @FROM
EXEC @Hresult = sp_OASetProperty @Object, 'To', @TO
EXEC @Hresult = sp_OASetProperty @Object, 'Subject', @SUBJECT
EXEC @Hresult = sp_OASetProperty @Object, 'Body', @V_BODY
--CALL SEND METHOD
EXEC @Hresult = sp_OAMethod @Object, 'Send', NULL
--DESTROY THE OBJECT
EXEC @Hresult = sp_OADestroy @Object
end
else begin
EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT
SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc
end
Paras Shah
Evision Technologies
Mumbai, India
Paras Shah
Evision Technologies
Mumbai, India
September 2, 2001 at 4:41 pm
One way to start it would be to use xp_cmdshell to run 'net start sqlagent', where sqlagent is the name of the service (different for different instances). You can also do it using DMO. Both of those you could do from a proc or from a job. I couldnt find a sp that would start the agent directly - seems like there should be one!
Andy
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply