Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-05-09 : 10:24:42
|
| andres writes "I've been reading in the forums about a bug trying to send messages up to 255 characteres via OLE objects CDONTS o CDOSYS, but i can't find any answers.Can you help with this please.Thanks" |
|
|
Kevin Snow
Posting Yak Master
149 Posts |
Posted - 2002-05-10 : 11:54:25
|
| There are two problems....In the older CDONTS DLLs the message length is limited to 255 characters...Also, in SQL 7.0, the extended stored procedure that calls the CDONTS DLL limits it's parameters to 255 characters. So, you can get hit coming and going. Perhaps they were thinking more along the lines of instant messaging when they set this up.I have successfully sent mail up to 8000 characters, but I had to use SQL 2000 (for the parameter side), and the latest CDONTS and CDOSYS dlls.Whether or not this has been corrected in the latest 7.0 service pack, I don't know.Heres the local SPROC I have used successfully.CREATE PROCEDURE [dbo].[sp_send_cdosysmail] @From varchar(255) , @To varchar(255) , @Subject varchar(255)=" ", @Body varchar(8000) =" "/*********************************************************************This stored procedure takes the above parameters and sends an e-mail. All of the mail configurations are hard-coded in the stored procedure. Comments are added to the stored procedure where necessary.Reference to the CDOSYS objects are at the following MSDN Web site:http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cdosys/html/_cdosys_messaging.asp***********************************************************************/ AS Declare @iMsg 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.-- http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cdosys/html/_cdosys_schema_configuration_sendusing.asp 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', 'srvexchcls1' -- 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, 'From', @From EXEC @hr = sp_OASetProperty @iMsg, 'Subject', @Subject-- If you are using HTML e-mail, use 'HTMLBody' instead of 'TextBody'. EXEC @hr = sp_OASetProperty @iMsg, 'TextBody', @Body EXEC @hr = sp_OAMethod @iMsg, 'Send', NULL-- Sample error handling. IF @hr <>0 select @hr BEGIN EXEC @hr = sp_OAGetErrorInfo NULL, @source OUT, @description OUT Select @hr IF @hr = 0 BEGIN SELECT @output = ' Source: ' + isnull(@source,'<null>') PRINT @output SELECT @output = ' Description: ' + isnull(@description,'<null>') PRINT @output END ELSE BEGIN PRINT ' sp_OAGetErrorInfo failed.' RETURN END END-- Do some error handling after each step if you need to.-- Clean up the objects created. EXEC @hr = sp_OADestroy @iMsgGOHope this helps. |
 |
|
|
|
|
|
|
|
Trying to send SMTP messages from SQL 7 via OLE Obejcts