sql mail
-
Well I'm confusified.
Installed outlook with corporate/workgroup support under same account sql service is starting with. Set up new email account and profile. Sql recognized the profile and successfully ran xp_sendmail.
only problem is that msg was delivered to outlook mailbox rather then from it.
sql mail acount. sqlmail@domain.com
xp_sendmail @recipients='dave@otherdomain.com',@subject='test'
Query analyzer returns "Mail sent" or whatever message.
Message ends up in sqlmail@domain.com's inbox instead of dave@otherdomain.com
??? Any ideas?
Dave
Trainmark.com IT Training B2B Marketplace
(Jobs for IT Instructors) -
I wonder what happen if you send email from outlook?
I certainly never have seen this type of problem from sql-mail before.
mom
-
From outlook it works fine. I resolved this by simply using sp_OACreateObject and an already installed smtp email componant. Sure is allot less hassle. Permission aside
Dave
Trainmark.com IT Training B2B Marketplace
(Jobs for IT Instructors) -
I used to sql mail but it can be a bit of a pain as it's always chaking the pop 3 mailbox even if you don;t want it process SQL queries that are mailed to it. Also you get MAPI errors if you have hardware failures and things are restarted in the order it likes.
I found a better solution was to write a stored procedure that uses the collaboration data objects that are installed by default by server 2000 & 2003 as long as you don't need to process received mail this is fine.
Also if you use sql mail and want send e-mail notifications from DTS packages I found you needed to logged in as the same account it was running under when you created the DTS packages or it wouldn't send them. This is not a problem using a stored procedure.
Anyway the biggest bonus is you don't need outlook on your server.
If your interested I'll mail you the stored procedure.
-
I have spent all morning trying to fix the same problem. It is strange, when I send a test email from EM it works fine the email gets sent and put into my Inbox, however when I use exec xp_sendmail 'me@domain.com','hello' QA reads "Mail Sent" or when a SQL Agent job runs I get no errors everything ok, but it never gets sent to my inbox. It works only if I run test from QA. Can anyone pleae help?
-
Well I went the route David Scotland went, since I already have an aspEmail componant installed on that server, I just use it.
I was not excited about installing outlook on my server, the security risks are probably higher then granting permission to sp_oaCreateObject procedure
Thanks Dave I'd love to see you procedure. I use the new CDO on my win03 server but I'd like to see the code for cdo on 2k. Is it different?
Cheers
Dave
Trainmark.com IT Training B2B Marketplace
(Jobs for IT Instructors) -
Well here you go then
--***************Configuring the Message Object ******************
-- This is to configure a remote SMTP server.
EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusing").Value','2'
-- This is to configure the Server Name or IP address.
-- Replace MailServerName by the name or IP of your SMTP Server.
EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserver").Value', 'post.demon.co.uk'
-- Save the configurations to the message object.
EXEC @hr = sp_OAMethod @iMsg, 'Configuration.Fields.Update', null
-- Set the e-mail parameters.
EXEC @hr = sp_OASetProperty @iMsg, 'To', @To
EXEC @hr = sp_OASetProperty @iMsg, 'CC', @cc
EXEC @hr = sp_OASetProperty @iMsg, 'From', @From
EXEC @hr = sp_OASetProperty @iMsg, 'Subject', @Subject
EXEC @hr = sp_OASetProperty @iMsg, 'TextBody', @Body
EXEC @hr = sp_OAMethod @iMsg, 'AddAttachment', NULL, @Attachment
EXEC @hr = sp_OAMethod @iMsg, 'Send', NULL
-- Error handling.
IF @hr 0
select @hr
BEGIN
EXEC @hr = sp_OAGetErrorInfo NULL, @source OUT, @description OUT
IF @hr = 0
BEGIN
SELECT @output = ' Source: ' + @source
PRINT @output
SELECT @output = ' Description: ' + @description
PRINT @output
END
ELSE
BEGIN
PRINT ' sp_OAGetErrorInfo failed.'
RETURN
END
END
-- Clean up the objects created.
EXEC @hr = sp_OADestroy @iMsg
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
-
Looks like its essentialy the same as win03. don't you require authentication on your smtp server? Gotta fight spam ya know
Adding (vbscript):
objMessage.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = cdoBasic
objMessage.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/se
ndusername") = strUserName
objMessage.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = strPasswordName
I think the cdoBasic enumerate is 2 but I can't remember.
This should be a usefull thread for many with sql mail nightmares
Dave
Trainmark.com IT Training B2B Marketplace
(Jobs for IT Instructors) -
OOps don't know what I managed to do there but that won't create a stored procedure for you. The procedure is identical on 2000 & 2003 but if you still use NT then it's different. Anyway lets try and paste all of it this time.
CREATE PROCEDURE [dbo].[sp_send_cdosysmail]
@From varchar(100) = "SQL@djscotland.demon.co.uk",
@To varchar(100) ,
@cc varchar(300)= " ",
@Subject varchar(100)=" ",
@Body varchar(4000) =" ",
@Attachment varchar(255) = " "
/*********************************************************************
This stored procedure takes the parameters and sends an e-mail.
Comments are added to the stored procedure where necessary.
***********************************************************************/
AS
Declare @iMsg int
Declare @iBp int
Declare @hr int
Declare @source varchar(255)
Declare @description varchar(500)
Declare @output varchar(1000)
--************* Create the CDO.Message Object ************************
EXEC @hr = sp_OACreate 'CDO.Message', @iMsg OUT
--***************Configuring the Message Object ******************
-- This is to configure a remote SMTP server.
EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusing").Value','2'
-- This is to configure the Server Name or IP address.
-- Replace MailServerName by the name or IP of your SMTP Server.
EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserver").Value', 'post.demon.co.uk'
-- Save the configurations to the message object.
EXEC @hr = sp_OAMethod @iMsg, 'Configuration.Fields.Update', null
-- Set the e-mail parameters.
EXEC @hr = sp_OASetProperty @iMsg, 'To', @To
EXEC @hr = sp_OASetProperty @iMsg, 'CC', @cc
EXEC @hr = sp_OASetProperty @iMsg, 'From', @From
EXEC @hr = sp_OASetProperty @iMsg, 'Subject', @Subject
EXEC @hr = sp_OASetProperty @iMsg, 'TextBody', @Body
EXEC @hr = sp_OAMethod @iMsg, 'AddAttachment', NULL, @Attachment
EXEC @hr = sp_OAMethod @iMsg, 'Send', NULL
-- Error handling.
IF @hr 0
select @hr
BEGIN
EXEC @hr = sp_OAGetErrorInfo NULL, @source OUT, @description OUT
IF @hr = 0
BEGIN
SELECT @output = ' Source: ' + @source
PRINT @output
SELECT @output = ' Description: ' + @description
PRINT @output
END
ELSE
BEGIN
PRINT ' sp_OAGetErrorInfo failed.'
RETURN
END
END
-- Clean up the objects created.
EXEC @hr = sp_OADestroy @iMsg
GO
-
I stopped using SQLMail completely, but use a little VBscript to notify me on seccess/failure:
Set oMsg = CreateObject("CDONTS.NewMail")
oMsg.From = "dentalxchange.com"
oMsg.To = "jpoddoubsky@dentalxchange.com"
oMsg.Subject = "Reindex failed for db on Server1"
oMsg.Body = "Reindex failed for db on Server1"
oMsg.Send
Set oMsg = Nothing
I stick it everywhere I need to know job results and very happy with how it works!
Julia
-
Our internal mail server will only let people it knows and trusts send smtp stuff through it, so need for password it just knows to let that particular SQL Server send mail.
If it's going externally the mail server will send it to our mail relay to do that. We don't trust notes or exchange out there in the big bad world so no chance of spamming from us.
-
Hello everyone. I too am trying to find an alternative to SQL Mail. We have our SQL Server (2000) installed on a Win2003 server and found that all of the SQLMail docs reference creating a Mail Profile, which I can't seem to do on the Win2003 server. XP_Sendmail that I've checked out doesn't seem to allow us to specify a UID/PASSWORD for the remote SMTP server (we do not have one internally and would prefer not to set one up since we already have several external ones available). The code I've seen in the above posts seem to refer to various URLs that may or may not be configuration settings (i.e.,http://schemas.microsoft.com/cdo/configuration/sendusing) but I can't access them to verify.
Our application was designed to handle MAPI (from the client) or SQLMail/MAPI from the Server ... neither is a good option now because OUTLOOK pops up those darn "a program is trying to access Outlook ..." message and we can no longer seem to set up a profile on Win2003.
So, What ARE the real alternatives?? There HAS to be a way to do this ... I would guess that we could install a different mail client? If so, what would be a good one to use to send simple email (don't need all of the bells and whistles that come with Outook).
Thanks,
Brad
-
Brad
Firstly, its not good forum etiquette to hijack someone else's post. Yes your question does relate to SQL Mail, but it has nothing to do with the original posters question.
As for alternatives to SQL Mail, I'd recommend xp_smtp_sendmail from http://www.sqldev.net. We've been using it for years without any problems whatsoever. We have it setup for SQL Agent alerts, general email messages from within stored procedures, job failure notifications.
--------------------
Colt 45 - the original point and click interface
-
I have persits aspEmail component installed on my server for use with ASP pages so I am now just using it from stored proc's as well and it works great for me.
You do have to provide permissions to the extended stored procedures like sp_OACreate, which might not be an option for everyone.
declare @oMail int --Object reference
declare @resultcode int
declare @Body varchar(8000)
declare @Subject varchar(200)
declare @To varchar(500)
declare @FromAddress varchar(200)
declare @FromName varchar(200)
declare @UserName varchar(200)
declare @Password varchar(200)
--load up your vars here.
EXEC @resultcode = sp_OACreate 'Persits.MailSender', @oMail OUT
IF @resultcode = 0
BEGIN
EXEC @resultcode = sp_OASetProperty @oMail, 'Host', 'mail.YourDomain.com'
EXEC @resultcode = sp_OASetProperty @oMail, 'FromName', @fromName
EXEC @resultcode = sp_OASetProperty @oMail, 'From', @fromAddress
EXEC @resultcode = sp_OASetProperty @oMail, 'username', @UserName
EXEC @resultcode = sp_OASetProperty @oMail, 'password', @Password
EXEC @resultcode = sp_OAMethod @oMail, 'AddAddress', NULL, @To
EXEC @resultcode = sp_OASetProperty @oMail, 'Subject', @subject
EXEC @resultcode = sp_OASetProperty @oMail, 'Body', @Body
EXEC @resultcode = sp_OAMethod @oMail, 'Send', NULL
EXEC @resultcode = sp_OAMethod @oMail, 'Reset', NULL
END
EXEC sp_OADestroy @oMail
GO
Dave
Trainmark.com IT Training B2B Marketplace
(Jobs for IT Instructors)
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply