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;

        }

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