Convert RTF data

Question

Convert RTF data

Viewing 15 posts - 31 through 45 (of 105 total)

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

ChrisTaylor SSCarpal Tunnel Points: 4527 More actions · Answer 1

Definitely must be a Win2003 permissions issue. I've just tried it on my machine (win2000) running sql 2005 express and it works fine.

Looking into it now and will report back with my findings.....

_________________________________________________________________________________SQLGeordieWeb:- Jarrin ConsultancyBlog:- www.chrisjarrintaylor.co.ukTwitter:- @SQLGeordie

Stewart Joslyn SSCertifiable Points: 6131 More actions · Answer 2

Not sure whether this is relevant :

http://support.microsoft.com/kb/899191

Lowell SSC Guru Points: 323518 More actions · Answer 3

Christopher Taylor (1/25/2008)
i've thrown a bit of error handling in (after 1st sp_OASetProperty) and this is the output:
ObjectID: NULL - looks like this is the problem!
hr: 0x80042727
Source: ODSOLE Extended Procedure
Description: sp_OASetProperty usage: ObjPointer int IN, PropertyName varchar IN, @setval IN [, additional indexing IN params]
Going to check the registry now.....

If the object is null, that means the windows handle created when CreateObject("'RICHTEXT.RichtextCtrl") is called, it is failing, which pretty much means the ocx is not installed or registered correctly, just like leonard's issue half a dozen messages or so back. Can you download and reinstall the richtxt32.cab from the link he provided, and then reinstall it?

I'd bet that's the issue;

an FYI: when i just tried the command EXEC @hr = sp_OACreate RichTextLib.RichTextBox', @object OUT

instead of

EXEC @hr = sp_OACreate 'RICHTEXT.RichtextCtrl', @object OUT

I also got null values. you didn't substitute rich text controls in your create statement, did you?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

ChrisTaylor SSCarpal Tunnel Points: 4527 More actions · Answer 4

Already registered it again (from the download link given) but still no luck. The only difference i can tell between what i've done on my machine and on the server is that on mine i could right click the file and register it, on the server i had to use regsvr32 to register it. But both came back with a successful message.......

_________________________________________________________________________________SQLGeordieWeb:- Jarrin ConsultancyBlog:- www.chrisjarrintaylor.co.ukTwitter:- @SQLGeordie

ChrisTaylor SSCarpal Tunnel Points: 4527 More actions · Answer 5

OK, found the problem. I'm not sure if its due to our server not having VB installed or not but our server was missing a few files (compared to my local machine which worked!):

RICHTEXT.SRG

RICHTX32.DEP

RICHTX32.oca

once these were applied to the registry then it worked.

BUT, i have a new issue. Some of our RTF notes contain grids but these notes are NULL'd when i run the function against them. I've checked the lengths of these and it seems that anything over 5573 characters has a problem. Amended the function from varchar(8000) to varchar(MAX) but still no luck. Could it be a memory issue? Error for @hr is 0x8004271C

Any ideas on a quick fix??

_________________________________________________________________________________SQLGeordieWeb:- Jarrin ConsultancyBlog:- www.chrisjarrintaylor.co.ukTwitter:- @SQLGeordie

ChrisTaylor SSCarpal Tunnel Points: 4527 More actions · Answer 6

found this:

http://support.microsoft.com/kb/325492

You may receive this error message when you try to retrieve an OUTPUT parameter or return value that is less than 4001 characters to a Transact-SQL parameter of type text through sp_OAGetProperty or sp_OAMethod:
hr Source Description
---------- ---------------------------- --------------------------
0x8004271C ODSOLE Extended Procedure Error in srv_paramset.

could it still be an issue on sql 2005????

_________________________________________________________________________________SQLGeordieWeb:- Jarrin ConsultancyBlog:- www.chrisjarrintaylor.co.ukTwitter:- @SQLGeordie

Bob Barnwell SSC Enthusiast Points: 134 More actions · Answer 7

Christopher? Lowell?

Have the NULL problem here as well..... Server on Win 2003, SQL Server 2005. Same setup as Chris it seems....

Chris, you solved the problem with those 3 files... how did you go about registering them?

I'm running out of hair to pull out.....

-Bob-

ChrisTaylor SSCarpal Tunnel Points: 4527 More actions · Answer 8

the quickest fix is to install VB onto the server if possible, this will create the relevant keys required, hence why it worked on my machine but not the sql servers. Will try and dig out the reg keys i had to add....

_________________________________________________________________________________SQLGeordieWeb:- Jarrin ConsultancyBlog:- www.chrisjarrintaylor.co.ukTwitter:- @SQLGeordie

Bob Barnwell SSC Enthusiast Points: 134 More actions · Answer 9

Thanks, Chris. It looks like VB did the trick... or rebooting the server the nth time, or permissions / security.... Now, executing the following code:

BEGIN

DECLARE @object int

DECLARE @hr int

DECLARE @out varchar(80)

EXEC @hr = sp_OACreate 'RICHTEXT.RichtextCtrl', @object OUT

print @hr

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

PRINT @hr

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

PRINT @HR

EXEC @hr = sp_OADestroy @object

print @hr

print @out

END

Now gives the desired results:

0

Hello

Thanks to you and everyone who participated in this topic!

emiddlebrooks Hall of Fame Points: 3397 More actions · Answer 10

Another tact you might try is regular expressions.

Here is link to using regular expressions in SQL

http://www.sqlservercentral.com/articles/Advanced+Querying/sql2000dbatoolkitpart2/2362/

And a link to a regular expression that converts rtf to plain text

http://regexlib.com/REDetails.aspx?regexp_id=1655

I haven't tried it yet but seems like it would work. Maybe faster than all the object manipulation.

Pravin-276596 Old Hand Points: 304 More actions · Answer 11

My trick has been to install and then uninstall VB 6.0 (with latest sp) and it works!

layale.bassil Old Hand Points: 395 More actions · Answer 12

Hello All:

I need to convert rtf data to plain text, I am doing some database conversion and I need to copy the rtf data from one table to another table as plain text. I have tried the proposed solution:

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

I am using SQL Server 2005, I ran this function and it didn't give any error, but when I use it, i.e:

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

It always returns null. I guess it's not working or it's facing an internal error. Any suggestion?

Help is much appreciated.

Regards.

ChrisTaylor SSCarpal Tunnel Points: 4527 More actions · Answer 13

is the text you're passing in more than 4000 characters?

_________________________________________________________________________________SQLGeordieWeb:- Jarrin ConsultancyBlog:- www.chrisjarrintaylor.co.ukTwitter:- @SQLGeordie

layale.bassil Old Hand Points: 395 More actions · Answer 14

Hello:

First of all thanks for your reply. In fact, no I am not passing a text more than 4000 characters, in my previous post, I show a sample which I tried to test the function with:

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

This is a small rtf file which I dont think has a size bigger than it should.

One thing to note, I did install VB 6.0 as some of the colleagues already answered in this forum, and still didn't work. Add to this, I noticed that I am not being to create the following COM object:

EXEC @hr = sp_OACreate 'RICHTEXT.RichtextCtrl', @object OUT;

print @hr

I get a negative value: -2147221005 as a result. So what should I do to be able to use the 'RICHTEXT.RichtextCtrl'?

Thanks in advance.

Regards.

Pravin-276596 Old Hand Points: 304 More actions · Answer 15

You have mentioned that you installed VB 6.0, did you also install the latest service pack? my experience has been that it works only after installing the SP.