RTF to Text User Defined Function

Question

Post reply

RTF to Text User Defined Function

Ralph Neuenfeld

SSC Veteran

Points: 238
More actions
February 28, 2006 at 10:23 pm

#167223

Hi People

I've got a user defined function to convert RTF data to text - it just stripts the formatting and supposedly returns just the text from a field or variable - I get nothing.

No one else seems to have this problem - and I've tried it on several different servers that I'm running.

I've been told it runs - but when I run the stored procedure I get Null returned - depending if I run it from query analyser or from another stored procedure call.

I'm running SQL Server 2000 standard. I've tried this UDF on different servers running different OS's and even my laptop - I've given execute permissions to the extended stored procedures and everything. Is there something simple I've forgotten.

The UDF is

CREATE function 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

The test string I pass is

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

My Results is

NULL

(1 row(s) affected)

Any suggestions would be greatly appreciated.

Regards

Ralph

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

paramind SSCrazy Points: 2130 More actions · Answer 1

Have you checked, whether the object is really created?

Next, are you sure, that you don't have to initiate some method on your object? I mean, you pass a parameter, do nothing and expect the poor object to deliver output to you

EXEC @hr = sp_OASetProperty @object, 'TextRTF', @in

something like ... EXEC @hr = sp_OAExecute.Transform ... (just an example, I don't have a documentation)

EXEC @hr = sp_OAGetProperty @object, 'Text', @out OUT

_/_/_/ paramind _/_/_/

Ralph Neuenfeld SSC Veteran Points: 238 More actions · Answer 2

Thanks Paramind.

You're first suggestion was the key - I checked the creation of the sp_oacreate and it wasn't returning a value - but from the microsoft site as example of creating another object other than a richtext.control worked.

I then found that my laptop registry entry for 'RICHTEXT.RichtextCtrl' pointed to r:\ for the richtx32.ocx instead of c:\windows\system32. Once edited the function worked !!!!!!!!!!

My servers don't have a 'RICHTEXT.RichtextCtrl' entry in the registry's and don't have the richtx32.ocx. So I am going to try to install manually or find out what other application installs these by default - because my servers are purely Windows 2000/2003 and SQL 2000 (No other apps installed at all).

Thanks

Ralph.

Steve Pope Valued Member Points: 57 More actions · Answer 3

Steve Pope

Valued Member

Points: 57

April 13, 2006 at 5:55 pm

#632546

Ralph,

Did you ever work out the details of manually installing the RICHTEXT control? I am in a similair situation and was wondering just how you might have done it. I have run into an error message leading me to believe that the control needs to be properly licensed.

Thanks,

Steve