June 22, 2008 at 4:56 am
Hello,
I have data stored in a database sql server 2000 .
when these data are sent by mail, they are received in format .txt
how to send them:
1. with html format
2. With the possibility of inserting "attached file"
we use SMTP as mail server
Thank you
June 22, 2008 at 9:36 am
Check out xp_smtp from Gert Drapers. This might do what you need.
June 23, 2008 at 10:43 am
I suggest the following link:
MSDN -> MSDN Library -> Win32 and COM Development -> Messaging and Collaboration -> CDO For Windows 2000
Although the Collaborative Data Objects is getting old (soon to be replaced by .Web.Mail, which I have not tried), CDO works (SQL Server 2K, server host in Windows 2000). NO need to have Outlook running on the SS2K's host machine.
One thing I found very odd on the first try was the use of http. ... used by the CdoConfiguration Module. These are NOT Web sites you can look up, these are actually Field names.
[font="Courier New"]
DECLARE
@ks_cdoSMTPConnectionTimeout varchar(255),
@ks_cdoSMTPServer varchar(255),
@ks_cdoSMTPServerPickupDirectory varchar(255),
@ks_cdoSMTPServerPort varchar(255),
@ks_cdoSendUsingMethod varchar(255),
@ks_cdoSendUsingPort varchar(1)
SET @ks_cdoSMTPConnectionTimeout = -- ACTUAL NAMES OF THE CONFIGURATION FIELDS ** NOT ** URL's
'"http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout"'
SET @ks_cdoSMTPServer =
'"http://schemas.microsoft.com/cdo/configuration/smtpserver"'
SET @ks_cdoSMTPServerPickupDirectory =
'"http://schemas.microsoft.com/cdo/configuration/smtpserverpickupdirectory"'
SET @ks_cdoSMTPServerPort =
'"http://schemas.microsoft.com/cdo/configuration/smtpserverport"'
SET @ks_cdoSendUsingMethod =
'"http://schemas.microsoft.com/cdo/configuration/sendusing"'
SET @ks_cdoSendUsingPort = '2'
EXEC @hr = sp_OACreate 'CDO.Message', @iMsg OUT
EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields(@ks_cdoSendUsingMethod).Value','2'
EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields(@ks_cdoSMTPServer).Value', @@SERVERNAME
EXEC @hr = sp_OAMethod @iMsg, 'Configuration.Fields.Update', null
EXEC @hr = sp_OASetProperty @iMsg, 'To', @ls_To
EXEC @hr = sp_OASetProperty @iMsg, 'Cc', @ls_Cc
EXEC @hr = sp_OASetProperty @iMsg, 'From', @ls_From
EXEC @hr = sp_OASetProperty @iMsg, 'Subject', @ls_Subject
EXEC @hr = sp_OASetProperty @iMsg, 'TextBody', @ls_Fileslist
-- FOR HTML USE 'HTMLBody' NOT 'TextBody'.
--and finally...
EXEC @hr = sp_OAMethod @iMsg, 'Send', NULL
EXEC @hr = sp_OADestroy @iMsg
[/font]
This should get you along. Some details have been deliberately omitted, I like the approach to teaching to fish instead of giving a fish.
I also suggest
"Sending SQL Server Notifications with CDOSYS" By Desiree Harris
[font="Courier New"]http://www.sql-server-performance.com/articles/dev/sending_sql_notifications_CDOSYS_p1.aspx[/font]
Regards
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply