RTF to TEXT in sql server

Question

I have a RTF field in my SQL 2005 table, I need to convert it to Text and display it. After a quick research I got the following method...

create function dbo.RTF2TXT(@in varchar(8000)) RETURNS  varchar(8000) AS 
BEGIN

DECLARE @object int
DECLARE @hr int
DECLARE @out varchar(8000)

-- Create an object that points to the SQL Server
EXEC @hr = sp_OACreate 'RICHTEXT.RichtextCtrl', @object OUT
EXEC @hr = sp_OASetProperty @object, 'TextRTF', @in
EXEC @hr = sp_OAGetProperty @object, 'Text', @out OUT
EXEC @hr = sp_OADestroy @object
return @out

END
GO

select dbo.RTF2TXT('{\rtf1\ansi\ansicpg1252\uc1 aaa}')

But Here I am getting only NULL as result... What could be the issue, please suggest

Answer 1

This looks like it uses the CLR to create a RichTextBox control.

Have you checked that

• the RichText OCX resides on the SQL Server
• CLR is enabled
• OLE is enabled (IIRC this is via the Surface Area Tool)

There is a thread here that uses very similar, if not the same code, that you have.

Alternatively, as you need this for SSRS, there is a nice article here that explains how to convert RTF to Text via SSRS.