Deploy SQL 2008 R2 MDS Functions without MDS
SQL Server 2008 R2 has a new Master Data Services feature that includes some really nice regular expression matching & extraction functions:
I am running 2008 R2, and want to run those functions outside of the MDS database.
How can I? Is there a way to extract them and reapply them to my own db?
3 Solutions collect form web for “Deploy SQL 2008 R2 MDS Functions without MDS”
I figured it out and fully documented it (with screenshots) on my blog. http://www.pettijohn.com/2010/08/regular-expressions-in-sql-2008-r2.html
- Install MDS from D:\MasterDataServices\x64\1033_ENU
- Run the Configuration Manager and set up a new MDS database. You will be copying from here and into your own database.
- Script the Microsoft.MasterDataServices.DataQuality assembly (Programmability / Assemblies / Right click / Script as / Create)
- Change the AUTHORIZATION to db_datareader
- Find the functions you need (regex are under scalar), and right click, script, create.
- Delete the USE block, and change the namespace to dbo, or one of your own.
- Run all of the scripts on your own database, starting with the assembly.
Go grab it while it’s hot.
Adapted string functions and assembly from Sql Server 2008 R2 MDS
The package includes the following functions:
I altered the default schema to dbo.
You can access the .NET Regex classes via CLR integration in SQL 2005/2008. See: Regular Expressions Make Pattern Matching And Data Extraction Easier