October 24, 2007 at 10:20 am
Thank you but I need to read a column that is in Rich Text format from SQL server and at the same time convert it to a text field so I can display it in the report via SQL Server Reporting services. Thanks.
October 25, 2007 at 1:42 am
In 2005, this would be a perfect job for the CLR. Using the RichTextBox control seems the simplest as it has rtf and plain text properties. Write as one and read as the other.
January 7, 2008 at 10:41 pm
ERROR Source Description HelpFile HelpID
0x800A0183RichtextCtrlProperty cannot be setRTFBox98.CHM387or
i get the above error everytime i try run the script below does anybody have any idea why ?
-------------------------------------------------------------------------------------------
windows xp
sqlexpress 2005
trustworthy set to true
CLR enabled yada yada
-------------------------------------------------------------------------------------------
DECLARE @object int
DECLARE @hr int
DECLARE @out varchar(8000)
EXEC @hr = sp_OACreate 'RICHTEXT.RichtextCtrl', @object OUT print @hr
EXEC @hr = sp_OASetProperty @object, 'TextRTF', @in IF @hr <> 0 BEGIN EXEC sp_OAGetErrorInfo @object END
EXEC @hr = sp_OAGetProperty @object, 'Text', @out OUT IF @hr <> 0 BEGIN EXEC sp_OAGetErrorInfo @object END
EXEC @hr = sp_OADestroy @object print @hr IF @hr <> 0 BEGIN EXEC sp_OAGetErrorInfo @object END
select @out
January 8, 2008 at 3:51 am
I tested this just now with the same example I had posted, but with 2005; I had to enable OLE witht he surface area tool first, of course.
It works on my server, Win2000, so I'd have to guess that maybe the Richtext object is a different version on XP; maybe the same property is named differently in the version installed on your machine.
The object I use would have both a object.TextRTF and object.Text property.
try changing this line to just .RTF and see if it makes a difference:
EXEC @hr = sp_OASetProperty @object, 'TextRTF', @in
\EXEC @hr = sp_OASetProperty @object, 'RTF', @in
for reference, when i go to RegEdit and search for RICHTEXT.RichtextCtrl, the class ID is for Microsoft Rich Textbox Control 6.0 (SP6)
and is located under:
HKEY_CLASSES_ROOT\CLSID\{3B7C8860-D78F-101B-B9B5-04021C009402}
do you have the same version?
Lowell
January 8, 2008 at 3:39 pm
yeah exactly the same as yours. as far as i am aware i have set all the permissions in sqlserver,
ive done the ole automation surface area configuration bit, i have set all sql permission options eg:
EXEC sp_configure 'CLR ENABLED' , '1'
RECONFIGURE
ALTER DATABASE DELLSPARE9/SQLEXPRESS SET TRUSTWORTHY ON
and still nothing.
January 8, 2008 at 7:11 pm
Downloaded the most recent richtext ocx from microsoft its now working beautifully thanks for the headsup Lowell
!!
for anyone who needs it http://activex.microsoft.com/controls/vb6/richtx32.cab
there it is.
just unregister the old one and
register the new one
using regsrv32
January 9, 2008 at 7:14 am
glad you got it working Leonard; this is one of those things that adds to the toolbox, but you don't use too often.
I made this function to try and strip RTF down to just it's text portion, without OLE, but it's not 100% perfect; it sometimes leaves a bit of rtf strings, but it's fine for analyzing data....i use it as a new column so I can search against it:
CREATE FUNCTIONdbo.fnParseRTF
(
@rtf VARCHAR(8000)
)
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE@Stage TABLE
(
Chr CHAR(1),
Pos INT
)
INSERT@Stage
(
Chr,
Pos
)
SELECTSUBSTRING(@rtf, Number, 1),
Number
FROMmaster..spt_values
WHEREType = 'p'
AND SUBSTRING(@rtf, Number, 1) IN ('{', '}')
DECLARE@Pos1 INT,
@Pos2 INT
SELECT@Pos1 = MIN(Pos),
@Pos2 = MAX(Pos)
FROM@Stage
DELETE
FROM@Stage
WHEREPos IN (@Pos1, @Pos2)
WHILE 1 = 1
BEGIN
SELECT TOP 1@Pos1 = s1.Pos,
@Pos2 = s2.Pos
FROM@Stage AS s1
INNER JOIN@Stage AS s2 ON s2.Pos > s1.Pos
WHEREs1.Chr = '{'
AND s2.Chr = '}'
ORDER BYs2.Pos - s1.Pos
IF @@ROWCOUNT = 0
BREAK
DELETE
FROM@Stage
WHEREPos IN (@Pos1, @Pos2)
UPDATE@Stage
SETPos = Pos - @Pos2 + @Pos1 - 1
WHEREPos > @Pos2
SET @rtf = STUFF(@rtf, @Pos1, @Pos2 - @Pos1 + 1, '')
END
SET@Pos1 = PATINDEX('%\cf[0123456789][0123456789 ]%', @rtf)
WHILE @Pos1 > 0
SELECT@Pos2 = CHARINDEX(' ', @rtf, @Pos1 + 1),
@rtf = STUFF(@rtf, @Pos1, @Pos2 - @Pos1 + 1, ''),
@Pos1 = PATINDEX('%\cf[0123456789][0123456789 ]%', @rtf)
SELECT@rtf = REPLACE(@rtf, '\pard', ''),
@rtf = REPLACE(@rtf, '\par', ''),
@rtf = LEFT(@rtf, LEN(@rtf) - 1)
SELECT@rtf = REPLACE(@rtf, '\b0 ', ''),
@rtf = REPLACE(@rtf, '\b ', '')
SELECT@rtf = STUFF(@rtf, 1, CHARINDEX(' ', @rtf), '')
RETURN@rtf
END
Lowell
January 24, 2008 at 10:20 am
why do i keep coming back with NULL when i run this???
CLR and OLE are enabled and registered RICHTX32.OCX.................any ideas??
_________________________________________________________________________________SQLGeordieWeb:- Jarrin ConsultancyBlog:- www.chrisjarrintaylor.co.ukTwitter:- @SQLGeordie
January 24, 2008 at 12:12 pm
I'm just guessing, but it might be the size of the variable being passed; I'm also assuming your not using the fnParseRTF, since that doesnt need CLR or OLE.
add this before the line you see below:
PRINT DATALENGTH(@in)
EXEC @hr = sp_OASetProperty @object, 'TextRTF', @in
it might be that sp_OASetProperty only allows 8000 chars, and if your variable is bigger than that, it affects the results? just guessing, but I'd look for exceptions to the process like that.
Lowell
January 25, 2008 at 2:09 am
Unfortunately not, i checked the length yesterday and it only came to roughly 3000 characters. I'm obviously missing something really simple but i just can't see it!
_________________________________________________________________________________SQLGeordieWeb:- Jarrin ConsultancyBlog:- www.chrisjarrintaylor.co.ukTwitter:- @SQLGeordie
January 25, 2008 at 2:13 am
Anything + Null = Null so I suggest that you tace all the @rtf + instances and find which one is null. Just hack the code to write each occurrence to a temporary table.
January 25, 2008 at 2:24 am
i've even tried something simple like:
select dbo.RTF2TXT('{123456}')
and still getting nulls.
I'm not using fnParseRTF because it doesn't strip out every bit of rtf and i need to strip everything.
_________________________________________________________________________________SQLGeordieWeb:- Jarrin ConsultancyBlog:- www.chrisjarrintaylor.co.ukTwitter:- @SQLGeordie
January 25, 2008 at 3:04 am
What you want to do looks to be similar to the first step SQL takes when creating a full-text index on a RTF document. When SQL builds a FT index on a RTF doc, it first puts the doc through the appropriate IFilter to produce a text-only version. It then builds the FTS index on the resulting text.
You can do stage 1 yourself, using the Filtdump routine in the W2003 SDK. You would need to wriet a sp that exports our RTF to a flat file, call Filtdump to export the text, then import the test file back into SQL. Alternatively, get a programmer to do a CLR routine that can take SQL @variables as parameters and call the IFilter dll without the need to create the intermediate files.
This technique allows you to extract the text from any document for which you have an IFilter.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
January 25, 2008 at 3:05 am
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.....
_________________________________________________________________________________SQLGeordieWeb:- Jarrin ConsultancyBlog:- www.chrisjarrintaylor.co.ukTwitter:- @SQLGeordie
January 25, 2008 at 4:57 am
well i've checked the registry and tried both RICHTEXT.RichtextCtrl and RICHTEXT.RichtextCtrl.1 but still getting NULL for the object.
The server is running on Win2003 and Sql2005, could it be a permissions issue?? Does the server need rebooting after registering the .ocx file?
Any more ideas? I need this setup asap :o(
_________________________________________________________________________________SQLGeordieWeb:- Jarrin ConsultancyBlog:- www.chrisjarrintaylor.co.ukTwitter:- @SQLGeordie
Viewing 15 posts - 16 through 30 (of 105 total)
You must be logged in to reply to this topic. Login to reply