RTF to TEXT in sql server

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...开发者_Go百科

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

I have also same requirement but i will prefer not to use any richtext control dll or CLR in SQL Server. I do same thing by taking reference of System.Windows.Form.dll in my service / database layer project. Import that library at top of .cs file where we Import/Use our required library for that class and do below code. And it really resolved my problem. You can also try this and let me know if you face any problem.

STEPS To convert RTF To Text without RichTextControl ocx/dll or CLR in SQL Server

1) In your project Add Reference to System.Windows.Forms.dll (Preferably add this reference in ServiceLayer/DataBase Layer/ Domain Model Layer/Business Logic Layer)

2) Import/Use it in your class file where we import/use required namespace at the top of class

VB.Net --> Import Systems.Windows.Forms
C#.Net --> using System.Windows.Forms;

3) Add below code where you want to convert your RTF to Plain Text

 System.Windows.Forms.RichTextBox rtBox = new System.Windows.Forms.RichTextBox();
 rtBox.Rtf = actionNotes.Notes; // <your database table's RTF field data assigned to rtBox's Rtf property>
 string s = rtBox.Text; //This will convert your assigned RTF Field data in Plain Text

 return s;   //Return this string variable or assign it to the control where you want to display your data in Plain Text format

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.