CDOSYS email body truncated

I have written a stored procedure, based on the sample code to send email.
Only problem is that the message body is truncated to 255 characters. Any
ideas? Thanks!
The procedure is listed below. [It has an extra sp_OAGetProperty statement
to retrieve and print the message body after it it set. When I run it, the
email is sent but the body is truncated (the 2 print statements show the
before and after).]
Is this an environment problem? Or am I doing something really stupid?
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE [dbo].[Send_CDOSYSMail]
@.from varchar(100),
@.to varchar(100),
@.cc varchar(100) = null,
@.bcc varchar(100) = null,
@.subject varchar(100),
@.body varchar(4000),
@.html char(1) = 'N'
/ ****************************************
*****************************
This stored procedure takes the parameters and sends an e-mail.
All the mail configurations are hard-coded in the stored procedure.
Comments are added to the stored procedure where necessary.
References to the CDOSYS objects are at the following MSDN Web site:
http://msdn.microsoft.com/library/d...s_messaging.asp
****************************************
*******************************/
AS
declare @.iMsg int
declare @.hr int
declare @.source varchar(255)
declare @.description varchar(500)
declare @.output varchar(1000)
-- Tidy up the parameters
set @.from = ltrim(rtrim(@.from))
set @.to = ltrim(rtrim(@.to))
set @.cc = ltrim(rtrim(@.cc))
set @.bcc = ltrim(rtrim(@.bcc))
set @.subject = ltrim(rtrim(@.subject))
set @.body = ltrim(rtrim(@.body))
-- Create the CDO.Message Object
exec @.hr = sp_OACreate 'CDO.Message', @.iMsg OUT
-- Configure the Message Object
-- ... configure a remote SMTP server
--
http://msdn.microsoft.com/library/d...n_sendusing.asp
exec @.hr = sp_OASetProperty @.iMsg,
'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendus
ing").Value','2'
-- ... configure the Server Name or IP address
exec @.hr = sp_OASetProperty @.iMsg,
'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpse
rver").Value', '<mysmtpservername>'
-- 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, 'Cc', @.cc
exec @.hr = sp_OASetProperty @.iMsg, 'Bcc', @.bcc
exec @.hr = sp_OASetProperty @.iMsg, 'Subject', @.subject
-- Send the email
if @.html = 'Y'
begin
exec @.hr = sp_OASetProperty @.iMsg, 'HTMLBody', @.body
end
else
begin
exec @.hr = sp_OASetProperty @.iMsg, 'TextBody', @.body
end
print @.body
declare @.xbody varchar(8000)
exec @.hr = sp_OAGetProperty @.iMsg, 'TextBody', @.xbody OUT
print isnull(@.xbody,'?')
IF @.hr <> 0
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
exec @.hr = sp_OAMethod @.iMsg, 'Send', NULL
-- error handling
IF @.hr <> 0
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
GOHello,
Based on my test, the email body is printed twice and the results are
correctly. Can you print the email body correctly?
For your reference, XPSMTP provides a SMTP based SQL Mail solution for
sending MIME based email over
SMTP, implemented as an Extended Stored Procedure.
XPSMTP.DLL - SQL Server SMTP Mail XP
http://www.sqldev.net/xp/xpsmtp.htm
I hope the information is helpful.
Sophie Guo
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
========================================
=============
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.|||> Can you print the email body correctly?
No ... as I said before it is truncated to 255 characters (in the email that
is sent and in the result returned from sp_OAGetProperty).

> For your reference, XPSMTP provides a SMTP based SQL Mail solution for
> sending MIME based email over SMTP, implemented as an Extended Stored
> Procedure.
> XPSMTP.DLL - SQL Server SMTP Mail XP
> http://www.sqldev.net/xp/xpsmtp.htm
Thanks, I will have to use that if there is no obvious reason for what's
going wrong.|||Cracked it, after some googling. I'd forgotten I was using SQL Server 7
(it's an ancient system I'm trying to support!) ... Apparently, there is a
limitation in SQL 7 that truncates all strings passed to COM objects to 255
characters. As a result, the datatypes of the parameters are limited to 255
characters, since only the first 255 characters will be passed to the CDOSYS
COM object. (It's a limitation of the sp_OA... methods.) This is no good fo
r
the body, so the work around is to use a TEXT field for this. Don't know
what the upper limit is, but it seems to work and certainly goes beyond the
255 character limit.|||Hello,
I don't know you are using SQL server 7 and I tested the issue on SQL
server 2000. I found the following articles address the issue:
278448 BUG: sp_OASetProperty Truncates Varchar Types to 255 Characters
http://support.microsoft.com/?id=278448
324146 FIX: SQL Server 7.0 sp_OASetProperty Method Does Not Accept More Than
http://support.microsoft.com/?id=324146
The following information in the BOL is for your reference:
text
Variable-length non-Unicode data in the code page of the server and with a
maximum length of 231-1 (2,147,483,647) characters. When the server code
page uses double-byte characters, the storage is still 2,147,483,647 bytes.
Depending on the character string, the storage size may be less than
2,147,483,647 bytes.
I hope the information is helpful.
Sophie Guo
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
========================================
=============
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.

Catalog and characters with accent

I have a question about SQL Server 2000 Full Text Index.
I want to create a catalog in a field (varchar(255)), but Im with 2
problems:
1. The characters of this field can have accent. But when I do a search
I want to see the rows with and without the accent. For example:
SELECT NAME
FROM TABLE
WHERE CONTAINS (FIELD, '"PLASTICO*"')
With this command I want to see the row PLASTICO and the row PLSTICO.
Is it possible? Now, Im just receiving only the row PLASTICO. I need
that the catalog be accent insensitive. Can I do that?
2. My sencond problem is: I need to see also the rows that have the word
PLASTICO inside the complete word. For example: I want to see also the
rows with INTERPLASTICO, 2PLASTICO, XPTOPLASTICO. But whe I wrote the
following command I dont receive these words:
SELECT NAME
FROM TABLE
WHERE CONTAINS (FIELD, '"*PLASTICO*"')
Is it possible to do that? I wnat to see the rows that have the word
PLASTIC in the begin, middle or end of the words.
Thaks,
Paulo
*** Sent via Developersdex http://www.codecomments.com ***
SQL 2005 can solve both your problems. You can configure your catalog for
accent insensitive searches. You can also use the thesaurus option to expand
your search on plastico to search on interpastico, 2plastico, and
xptoplastico, as long as you enter all of these expansion terms into your
thesaurus file in advance.
In SQL 2000 you have to expand your search terms for accented or unaccented
versions as well as the alternate word forms.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Paulo Andre Ortega Ribeiro" <paulo.andre.66@.terra.com.br> wrote in message
news:O%23k7qFyeFHA.2740@.TK2MSFTNGP10.phx.gbl...
> I have a question about SQL Server 2000 Full Text Index.
> I want to create a catalog in a field (varchar(255)), but Im with 2
> problems:
> 1. The characters of this field can have accent. But when I do a search
> I want to see the rows with and without the accent. For example:
> SELECT NAME
> FROM TABLE
> WHERE CONTAINS (FIELD, '"PLASTICO*"')
> With this command I want to see the row PLASTICO and the row PLSTICO.
> Is it possible? Now, Im just receiving only the row PLASTICO. I need
> that the catalog be accent insensitive. Can I do that?
> 2. My sencond problem is: I need to see also the rows that have the word
> PLASTICO inside the complete word. For example: I want to see also the
> rows with INTERPLASTICO, 2PLASTICO, XPTOPLASTICO. But whe I wrote the
> following command I dont receive these words:
> SELECT NAME
> FROM TABLE
> WHERE CONTAINS (FIELD, '"*PLASTICO*"')
> Is it possible to do that? I wnat to see the rows that have the word
> PLASTIC in the begin, middle or end of the words.
> Thaks,
> Paulo
>
>
> *** Sent via Developersdex http://www.codecomments.com ***