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:

http://msdn.microsoft.com/en-us/library/ee633712.aspx

  • SQL Pivot query based on “SUM of a column divided by SUM of another column”
  • Can I modify below GroupBy clause into better one
  • Outer join multiple tables with flattened results
  • SSIS parallel processing foreach loop
  • Updating table with new objects without mass delete through LINQ to SQL in C#
  • SQL Server localdb. Confusion about two MDF files
  • 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?

  • This extension requires the Microsoft ODBC Driver 11 for SQL Server to communicate with SQL Server
  • Whether SQL Blocking Session will be cleared by itself?
  • Return first ID matching a sequence of conditions
  • In SQL replace null value with another value
  • extract schema for sql server compact edition
  • Predicting Number of Records from GROUP BY WITH CUBE
  • 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

    In summary:

    • 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:

    TVFs:

    • NGrams
    • RegexMatches
    • RegexSplit
    • Split

    SVFs:

    • RegexExtract
    • RegexIsMatch
    • RegexIsValid
    • RegexMask
    • RegexReplace
    • Similarity
    • SimilarityDate
    • XmlTransform

    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

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.