RTF to Text User Defined Function

Question

RTF to Text User Defined Function

Ralph Neuenfeld

SSC Veteran

Points: 298
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: 2160 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: 298 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

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