Working Around a Varchar Truncation

I've noticed that when I pass varchar data in a parameter to the sp_OASetProperty stored procedure, SQL Server 7.0 truncates the data to 255 characters. For example, when I try to use Microsoft Collaboration Data Objects (CDO) to send a message, the body of the message is limited to 255 characters even though the @body variable is defined as varchar(8000). When I print the content of @body, the data is intact, but SQL Server sends only 255 characters. Does a workaround for the truncation problem exist?

The 255-character limit is based on the method that the sp_OA* stored procedures expose for passing data. You can work around the limit in one of two ways. You can pass the arguments through a table or external file: Simply create code that loops back into the database and reads all the data in the varchar column. Or you can define a wrapper that uses a method to accumulate the message text through repeated calls until the code returns an end-of-text or empty text delimiter.