SQL Server Performance Forum – Threads Archive
cdo mail problem
Hi this is my script. <br /><br /><br />alter PROCEDURE automail_run_rs<br />@to varchar(500),<br />@from varchar(100),<br />@subject varchar(200),<br />@body text,<br />@filename varchar(1000) = ”,<br />@mailformat int<br />AS<br />–##Set Local Values<br />DECLARE @object int<br />DECLARE @hr int<br />DECLARE @src varchar(255)<br />DECLARE @desc varchar(255)<br /><br />–##Create Message object<br />EXEC @hr = sp_OACreate ‘CDO.Message’, @object OUT<br />IF @hr <> 0<br />BEGIN<br />print ‘start'<br />EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT <br />SELECT hr=convert(varbinary(4),@hr), [email protected], [email protected]<br />RETURN<br />END<br />/*<br />EXEC @hr = sp_OASetProperty @object, ‘Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendusingmethod")’, ‘587’<br />IF @hr <> 0<br />BEGIN<br />EXEC sp_OAGetErrorInfo @object<br />RETURN<br />END<br />*/<br /><br /><br />–##Set sendusing property<br />EXEC @hr = sp_OASetProperty @object, ‘Configuration.Fields("http://schemas.microsoft.com/cdo/configuration/sendusing")’, ‘2’<br />IF @hr <> 0<br />BEGIN<br />print ‘send using'<br />EXEC sp_OAGetErrorInfo @object<br />RETURN<br />END<br /><br />–##Set port property<br />EXEC @hr = sp_OASetProperty @object, ‘Configuration.Fields("http://schemas.microsoft.com/cdo/configuration/smtpserverport")’, ‘587’<br />IF @hr <> 0<br />BEGIN<br />print ‘port'<br />EXEC sp_OAGetErrorInfo @object<br />RETURN<br />END<br />–##Set smtp server property<br />EXEC @hr = sp_OASetProperty @object, ‘Configuration.Fields("http://schemas.microsoft.com/cdo/configuration/smtpserver")’, ‘smtp.somethink.com'<br />IF @hr <> 0<br />BEGIN<br />print ‘smtp server'<br />EXEC sp_OAGetErrorInfo @object<br />RETURN<br />END<br />EXEC @hr = sp_OASetProperty @object, ‘Configuration.Fields("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate")’, ‘1’<br />IF @hr <> 0<br />BEGIN<br />print ‘autho'<br />EXEC sp_OAGetErrorInfo @object<br />RETURN<br />END<br />EXEC @hr = sp_OASetProperty @object, ‘Configuration.Fields("http://schemas.microsoft.com/cdo/configuration/sendusername")’, ‘[email protected]‘<br />IF @hr <> 0<br />BEGIN<br />print ‘username'<br />EXEC sp_OAGetErrorInfo @object<br />RETURN<br />END<br />EXEC @hr = sp_OASetProperty @object, ‘Configuration.Fields("http://schemas.microsoft.com/cdo/configuration/sendpassword")’, ‘pass'<br />IF @hr <> 0<br />BEGIN<br />print ‘password'<br />EXEC sp_OAGetErrorInfo @object<br />RETURN<br />END<br />EXEC @hr = sp_OASetProperty @object, ‘Configuration.Fields("http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout")’, ‘160’<br />IF @hr <> 0<br />BEGIN<br />print ‘conn. timeout'<br />EXEC sp_OAGetErrorInfo @object<br />RETURN<br />END<br />–##Update the configuration<br />EXEC @hr = sp_OAMethod @object, ‘Configuration.Fields.Update’, NULL<br />IF @hr <> 0<br />BEGIN<br />print ‘conf. update'<br />EXEC sp_OAGetErrorInfo @object<br />RETURN<br />END<br />EXEC @hr = sp_OASetProperty @object, ‘Fields("urn<img src=’/community/emoticons/emotion-7.gif’ alt=’:s’ />chemas:httpmail:importance")’, ‘2’<br />IF @hr <> 0<br />BEGIN<br />print ‘importance'<br />EXEC sp_OAGetErrorInfo @object<br />RETURN<br />END<br />EXEC @hr = sp_OAMethod @object, ‘Fields.Update’, NULL<br />IF @hr <> 0<br />BEGIN<br />print ‘first update'<br />EXEC sp_OAGetErrorInfo @object<br />RETURN<br />END<br />PRINT ‘TEST'<br />EXEC @hr = sp_OAGetProperty @object, ‘Configuration.Fields("http://schemas.microsoft.com/cdo/configuration/smtpserver").value'<br />IF @hr <> 0<br />BEGIN<br />print ‘get smtp server'<br />EXEC sp_OAGetErrorInfo @object<br />RETURN<br />END<br />PRINT ‘TEST 2′<br />–##Set TO property<br />EXEC @hr = sp_OASetProperty @object, ‘To’, @to<br />IF @hr <> 0<br />BEGIN<br />print ‘to'<br />EXEC sp_OAGetErrorInfo @object<br />RETURN<br />END<br />–##Set FROM property<br />EXEC @hr = sp_OASetProperty @object, ‘From’, @from<br />IF @hr <> 0<br />BEGIN<br />print ‘from'<br />EXEC sp_OAGetErrorInfo @object<br />RETURN<br />END<br />–##Set Subject property<br />EXEC @hr = sp_OASetProperty @object, ‘Subject’, @subject<br />IF @hr <> 0<br />BEGIN<br />print ‘subject'<br />EXEC sp_OAGetErrorInfo @object<br />RETURN<br />END<br />–##set Subject property<br />EXEC @hr = sp_OASetProperty @object, ‘TextBody’, @body<br />IF @hr <> 0<br />BEGIN<br />print ‘textbody'<br />EXEC sp_OAGetErrorInfo @object<br />RETURN<br />END<br /><br />–##AddAttachment<br />IF @filename <> ”<br />BEGIN <br />EXEC sp_OAMethod @object, ‘AddAttachment’, NULL ,@filename<br />IF @hr <> 0<br />BEGIN<br />print ‘attchment'<br />EXEC sp_OAGetErrorInfo @object<br />RETURN<br />END<br />END<br /><br />–##Send the mail<br />EXEC @hr = sp_OAMethod @object, ‘Send’, NULL <br />IF @hr <> 0<br />BEGIN<br />print ‘send'<br />EXEC sp_OAGetErrorInfo @object<br />RETURN<br />END<br /><br />–##No memory leaks!<br />EXEC @hr = sp_OADestroy @object<br /><br /><br /><br /><br />This is my script<br />exec automail_run_rs<br />@to =’[email protected]‘,<br />@from =’[email protected]‘,<br />@subject =’Test’,<br />@body= ‘Test’,<br />@filename = ”,<br />@mailformat = 0<br /><br /><br />After executing this script i got errors<br />error:<br />The message could not be sent to the SMTP server. The transport error code was 0x80040217. The server response was not available <br /><br />whats the problem?<br /><br /><br />Thanks<br />S. RameshCheck whether the username and password used for SMTP server is valid. Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing.
]]>