February 11, 2010 at 5:24 am
I've written a CLR function to take RTF text and convert it to Text. It works fine on my development box, it works on a QA/development box, and we have a few QA boxes. I've tested it on another QA box and it works fine. Now on another QA box I recieved the following error;
Msg 6522, Level 16, State 1, Line 1
A .NET Framework error occurred during execution of user-defined routine or aggregate "RTFtoTEXT":
System.ComponentModel.Win32Exception: Error creating window handle.
System.ComponentModel.Win32Exception:
at System.Windows.Forms.Nativewindow.CreateHandle(CreateParams cp)
at System.Windows.Forms.Control.CreateHandle()
at System.Windows.Forms.Application.MarshalingControl..ctor()
at System.Windows.Forms.Application.ThreadContext.get_MarshalingControl()
at System.Windows.Forms.WindowsFormsSynchronizationContext..ctor()
at System.Windows.Forms.WindowsFormsSynchronizationContext.InstallIfNeeded()
at System.Windows.Forms.Control..ctor(Boolean autoInstallSyncContext)
at System.Windows.Forms.TextBoxBase..ctor()
at System.Windows.Forms.RichTextBox..ctor()
at Convertor.fnRTFtoText(String rtf)
Can anyone help me figure out the problem? Below is the function so you see there is not much code to it.
public static string fnRTFtoText(string rtf)
{
string textData = string.Empty;
if (rtf.IndexOf(@"{\rtf1\ansi") != -1)
{
RichTextBox rtb = new RichTextBox();
try
{
rtb.Rtf = rtf;
textData = rtb.Text;
}
catch (Exception e)
{
textData = e.Message;
}
finally
{
rtb.Dispose();
}
}
else
{
textData = rtf;
}
return textData;
}
Thanks,
Joe
February 11, 2010 at 4:05 pm
Problem was solved by installing SQL Server SP1 on the one system. The was the difference between the working systems.
February 15, 2010 at 3:33 pm
Hi Joe,
Can you tell me how you were able to reference System.Windows.Forms namespaces in your CLR function? I'm a newbie at this and I've been looking for a CLR solution to convert RTF to plaintext and your post seems to be what I'm looking for. The only issue I seem to have is referencing System.Windows.Forms.
February 15, 2010 at 6:15 pm
I have a sql script which does the following. That's all you need. Let me know if this helps
USE [DMZ]
GO
--set the database trustworthy
ALTER DATABASE DMZ SET TRUSTWORTHY ON
GO
--if the clr stored procedure exists drop it
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'Templates.RTF2TEXT') AND type in (N'P', N'PC'))
DROP PROCEDURE Templates.RTF2TEXT
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'Templates.RTFToTEXT') AND type in (N'FS'))
DROP FUNCTION Templates.RTFToTEXT
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'Templates.GetFreeText') AND type in (N'FS'))
DROP FUNCTION Templates.GetFreeText
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'Templates.ChartFreeText') AND type in (N'FT'))
DROP FUNCTION Templates.ChartFreeText
GO
--if the RTFConvertor assembly exists then drop it
IF EXISTS (SELECT * FROM sys.assemblies WHERE [name] = 'RTFConvertor')
DROP ASSEMBLY RTFConvertor
GO
--if the System.Windows.Forms assembly exists then drop it
IF EXISTS (SELECT * FROM sys.assemblies WHERE [name] = 'System.Windows.Forms')
DROP ASSEMBLY [System.Windows.Forms]
GO
--Create assembly [System.Windows.Forms]
CREATE ASSEMBLY [System.Windows.Forms] AUTHORIZATION dbo
FROM 'C:\Windows\Microsoft.NET\Framework\v2.0.50727\System.Windows.Forms.dll'
WITH PERMISSION_SET = UNSAFE
GO
--Create Assembly RTFConvertor
CREATE ASSEMBLY RTFConvertor from 'D:\Development\Allscripts\Migration Tool\Current\Assembly\MigrationTool.dll' WITH PERMISSION_SET = UNSAFE
GO
--create stored procedure Glossary.RTF2TEXT
CREATE PROCEDURE Templates.RTF2TEXT
(
@rtfChartnote nvarchar(max),
@textData nvarchar(max) out
)
AS EXTERNAL NAME RTFConvertor.Convertor.RTFtoText
GO
--create stored procedure Glossary.RTF2TEXT
CREATE FUNCTION Templates.RTFtoTEXT
(
@rtf nvarchar(max)
)
returns nvarchar(max)
AS EXTERNAL NAME RTFConvertor.Convertor.fnRTFtoText
GO
CREATE FUNCTION Templates.GetFreeText
(
@noteid int,
@rownumber int
)
returns nvarchar(max)
AS EXTERNAL NAME RTFConvertor.Convertor.[FreeText]
GO
CREATE FUNCTION Templates.ChartFreeText
(
@noteid int
)
returns table (rownum nvarchar(10), freetextData nvarchar(4000))
AS EXTERNAL NAME RTFConvertor.Convertor.ChartFreeText
GO
--enable CLR configuration
exec SP_CONFIGURE 'show advanced options', 1;
GO
RECONFIGURE WITH OVERRIDE
GO
exec SP_CONFIGURE 'clr enabled', 1;
GO
RECONFIGURE WITH OVERRIDE
GO
February 16, 2010 at 9:01 am
Thank you!
I used a subset of your script to:
1.) Set the database trustworthy
2.) Drop the System.Windows.Forms assembly if it existed
3.) Created the System.Windows.Forms assembly
Because I ran into some problems manually creating my function (couldn't find the path), I chose to use Visual Studio's Build and Deploy to create the user function.
4.) I then Altered the assembly for the function WITH PERMISSION_SET = UNSAFE (It appears that the Visual Studio Deploy process creates the assembly with a Permission Level of Safe. This caused me to receive an error of "Request for the permission of type 'System.Security.Permissions.UIPermission, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed." when I tried to execute the function.
5.) enable CRL configuration
Thanks again for the post. It turned out to be a nice, elegant solution for converting RTF text to plain text.
February 21, 2010 at 10:20 am
There is nothing nice or elegant about forcing System.Windows.Forms into Sql Server!!!
Regardless of the requirement to convert RTF text, this is a dumb and dangerous plan.
SQL Server provides many sensible controls in the hosted CLR environment. With a great deal of effort, you can work around many of these controls and load dangerous stuff into Sql Server. That is what has happened here.
I can't adequately express how much of a bad idea this is.
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
February 21, 2010 at 1:30 pm
Thanks for your opinion, Paul. Do you have any suggestions for converting RTF text that resides in an NTEXT column to plain text other than the solution given above?
February 21, 2010 at 1:50 pm
Yes, I would recommend doing this via SQL Server Service Broker External Activation (see here:http://social.msdn.microsoft.com/Forums/en-US/sqlservicebroker/thread/36a58004-dbef-46ad-85a2-93563f3f8f0e).
Although it lacks the utility of SQL CLR (it cannot be called from a UDF or a View) it has none of its coding limitations or safety issues. You can safely use anything from it that you would from any client or application code, including your RTF conversions.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
February 21, 2010 at 1:52 pm
Note that although the external Activator is only officially released for SQL Server 2008 or later, Microsoft does state (and I can confirm) that it does work for and can be used with SQL Server 2005.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
February 21, 2010 at 9:18 pm
gail_liberty (2/21/2010)
Thanks for your opinion, Paul. Do you have any suggestions for converting RTF text that resides in an NTEXT column to plain text other than the solution given above?
My personal preference would be to perform the conversion before the data reached SQL Server in the first place.
From information posted on another thread, I understand the data is imported into SQL Server.
One possibility is to use the SSIS script component to perform the conversion.
Another is to call a compiled .NET executable to do the job.
Pre-processing the data in this way seems much cleaner to me, and is likely to be very much more efficient than doing it from within SQL Server, whatever method is used.
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
February 22, 2010 at 6:48 am
Unfortunately, in my case, we can't pre-process the data as it is a 3rd party application that populates the SQL table. We're just trying to export the data as plain text.
February 22, 2010 at 7:16 am
gail_liberty (2/22/2010)
Unfortunately, in my case, we can't pre-process the data as it is a 3rd party application that populates the SQL table. We're just trying to export the data as plain text.
Export it as RTF and post-process?
😉 😀 😛
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply