Sending email from SQL 7 via SMTP truncates body at 255 characters 1

[jrbarnett]

I'm attempting to develop an automatic email notification system for an application using the technique documented in

http://support.microsoft.com/kb/312839

to send the emails.

On SQL Server 2000 Standard Edition systems, this works well, but the live system that runs this is still on SQL Server 7 (Standard Edition). My tests have found that the message body is getting truncated at 255 characters.
Both hosts are running Windows 2000 Server SP 4. SQL 2000 SP 4 is installed on the SQL 2000 box, and SQL 7 SP 4 is on the SQL 7 box.

Realistically it isn't possible to upgrade this server to SQL 2000 due to limited resources on the host at present. I've thought of setting up a linked server to a SQL 2000 host to run this remotely, but I'd rather not mix and match if at all possible, keeping everything for one application in one database.

Does anybody have any ideas please on what I can do to get this working properly with SQL Server 7?

John

 

[nice95gle]

Try changing the @Body varchar(4000) to @Body Text.

I know this was a bug in sql server 7.0 and below but I don't know of a solution. It's been a long time working with 7.0

 

[jrbarnett]

Hello nice95gle,

I've now had a chance to check this and it has fixed the problem. I'll now see about implementing this on our live system.

Thank you very much for this, have a well deserved star.

John

 

[mrdenny]

I recommend getting a SQL 7 server as a test server. If production is SQL 7 then Dev and QA/Test should also be SQL 7. Developing on SQL 2000 then deploying to SQL 7 there is no way to guarantee that everything will work correctly.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[jrbarnett]

Point taken and understood Denny, we've got one. I'm actually looking to retire this box as soon as possible as its about 10 years old, but its got some specialist software on it that has so far resisted all attempts to be migrated to the new server and work properly. Its about 3rd from top on my overall priority list.

John