RTF to TEXT
-
Well if the line wrap has not deceived me then the function does nothing because it is commented out. You seem to have 4 EXEC strung together in a single line that is commented out! Do you have any idea what you are doing, sp_OACreate is an OLE Automation procedure. I can't use it because of security permissions so I would not be surprised if that will also kill this.
Never underestimate the power of human stupidity RAH
Thanks for your response. I don't think, the line wrap will create issues. I have removed those comments & tried it. Actually I was trying to convert the Rich text type string to plain text while fetching records. Can you suggest any better approach to convert a string from rich text to plain text in sql server. Thanks once again
Sebastian
-
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... 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 Thanks
-
Well if the line wrap has not deceived me then the function does nothing because it is commented out. You seem to have 4 EXEC strung together in a single line that is commented out! Do you have any idea what you are doing, sp_OACreate is an OLE Automation procedure. I can't use it because of security permissions so I would not be surprised if that will also kill this.
Never underestimate the power of human stupidity RAH
Hello, One more thing can't we use following code to allow execution of sp_OACreate
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GOThanks
Sebastian
-
sp_OACreate is the devil. This is NOT something that a database should be doing - load the RTF data to your business layer and convert it there.
Thanks for your reply. This method also I have tried. What I have intended to do is to create a class library , add the following code;
public String ConvertRtftoText(RichTextBox rtb)
{
String strText = "";
try
{
strText = rtb.Text;
}
catch (Exception Ex)
{
// do nothing
}
return strText;
}& call this as a CLR stored procedure. But the problem which I faced is , when I tried to execute the below code
CREATE ASSEMBLY getTextfromRTF
from '\\ustech39\Shared\BackendRTFHandler.dll'
WITH PERMISSION_SET = UNSAFEI got another error
Warning: The Microsoft .Net frameworks assembly 'system.windows.forms, version=2.0.0.0, culture=neutral, publickeytoken=b77a5c561934e089, processorarchitecture=msil.' you are registering is not fully tested in SQL Server hosted environment.
Warning: The Microsoft .Net frameworks assembly 'system.drawing, version=2.0.0.0, culture=neutral, publickeytoken=b03f5f7f11d50a3a, processorarchitecture=msil.' you are registering is not fully tested in SQL Server hosted environment.
Msg 10301, Level 16, State 1, Line 1
Assembly 'BackendRTFHandler' references assembly 'accessibility, version=2.0.0.0, culture=neutral, publickeytoken=b03f5f7f11d50a3a.', which is not present in the current database. SQL Server attempted to locate and automatically load the referenced assembly from the same location where referring assembly came from, but that operation has failed (reason: 2(The system cannot find the file specified.)). Please load the referenced assembly into the current database and retry your request.modified on Monday, June 7, 2010 9:19 AM
-
Thanks for your reply. This method also I have tried. What I have intended to do is to create a class library , add the following code;
public String ConvertRtftoText(RichTextBox rtb)
{
String strText = "";
try
{
strText = rtb.Text;
}
catch (Exception Ex)
{
// do nothing
}
return strText;
}& call this as a CLR stored procedure. But the problem which I faced is , when I tried to execute the below code
CREATE ASSEMBLY getTextfromRTF
from '\\ustech39\Shared\BackendRTFHandler.dll'
WITH PERMISSION_SET = UNSAFEI got another error
Warning: The Microsoft .Net frameworks assembly 'system.windows.forms, version=2.0.0.0, culture=neutral, publickeytoken=b77a5c561934e089, processorarchitecture=msil.' you are registering is not fully tested in SQL Server hosted environment.
Warning: The Microsoft .Net frameworks assembly 'system.drawing, version=2.0.0.0, culture=neutral, publickeytoken=b03f5f7f11d50a3a, processorarchitecture=msil.' you are registering is not fully tested in SQL Server hosted environment.
Msg 10301, Level 16, State 1, Line 1
Assembly 'BackendRTFHandler' references assembly 'accessibility, version=2.0.0.0, culture=neutral, publickeytoken=b03f5f7f11d50a3a.', which is not present in the current database. SQL Server attempted to locate and automatically load the referenced assembly from the same location where referring assembly came from, but that operation has failed (reason: 2(The system cannot find the file specified.)). Please load the referenced assembly into the current database and retry your request.modified on Monday, June 7, 2010 9:19 AM
So do as Jamie suggested, move the conversion to your business layer where it belongs and get it out of the database. Why do you want to inflict another layer of abstraction between you nd your data, inplementing the CLR for this is just wrong.
Never underestimate the power of human stupidity RAH
-
So do as Jamie suggested, move the conversion to your business layer where it belongs and get it out of the database. Why do you want to inflict another layer of abstraction between you nd your data, inplementing the CLR for this is just wrong.
Never underestimate the power of human stupidity RAH
Hi Mycroft Holmes My intension was to avoid looping through the records in front end. thanks for the suggestion
Sebastian
-
Hi Mycroft Holmes My intension was to avoid looping through the records in front end. thanks for the suggestion
Sebastian
IMHO you have your priorities completely wrong, to save looping the records you are willing to saddle your self with OLE or the CLR, a cursor would be better than that and I hate cursors. You could convert the content to varchar and then check each character for a-z and punctuation. Nasty but should be doable.
Never underestimate the power of human stupidity RAH
-
Hi Mycroft Holmes My intension was to avoid looping through the records in front end. thanks for the suggestion
Sebastian
Sebastian T Xavier wrote:
My intension was to avoid looping through the records in front end.
Why not do the conversion on the fly, just before the data is required in the BL? Does the data "have" to be re-stored in the database as plain text? If the answer is yes, consider writing a specialized application to do the conversion once. Another alternative would be a CLR-UDF in Sql Server; you'd load the RTF text into a
RichTextBoxand return it's plain-text contents.I are Troll :suss:
-
Sebastian T Xavier wrote:
My intension was to avoid looping through the records in front end.
Why not do the conversion on the fly, just before the data is required in the BL? Does the data "have" to be re-stored in the database as plain text? If the answer is yes, consider writing a specialized application to do the conversion once. Another alternative would be a CLR-UDF in Sql Server; you'd load the RTF text into a
RichTextBoxand return it's plain-text contents.I are Troll :suss:
Hello Eddy, please see an earlier post in this chain.. Click Will this same issue happen to CLR-UDF? please advise Thanks for your thoughts
Sebastian
-
Hello Eddy, please see an earlier post in this chain.. Click Will this same issue happen to CLR-UDF? please advise Thanks for your thoughts
Sebastian
-
Good information.... Thank you
Sebastian
-
Good information.... Thank you
Sebastian