FYI to Everyone:

Steve has made the source code (VC++ 7.1 solution) for this project available in a ZIP file from http://www.sqlservercentral.com/products/mcoles/default.asp.

Compilation of some parts requires Boost Library source (in particular the Regular Expression extended procedures require Boost).  Boost source is available for free download at http://www.boost.org/.

If you do download and compile the source, I highly recommend downloading the latest Platform SDK from http://www.microsoft.com/downloads/details.aspx?FamilyId=A55B6B43-E24F-4EA3-A93E-40C0EC4F68E5&displaylang=en.

I've also been asked several times about the license for this toolkit.  Here it is:

DBA Toolkit License

The DBA Toolkit is composed of my original source code, and/or other source code that the original authors have placed in the public domain.  Credit has been given throughout the source code to those original authors, and their original licensing agreements have been maintained in the source code where appropriate.

You are free to use this source code and binaries however you want, modify it however you like, and use it for any purpose you like, personal or commercial.  All I ask is two things:

1.  If you redistribute the source code, modified or unmodified, give me a shout out in the comments or something

2.  If you do something really cool and make big and/or useful improvements, consider sharing it with the rest of the world (this is not mandatory, but it is the nice thing to do).  I'm particularly interested in the improvements people make to the source, and always appreciate the feedback.

Obviously the Boost Library is covered by Boost's own licensing agreement which you will need to observe if you decide to use the portions of code that interface with it (the Regular Expression functionality).  And Microsoft has their own licensing agreements you might need to review when before distributing programs compiled using the Platform SDK.

I've always greatly admired the DBA toolkit, and found the criticism of Mr Wooton very amusing. Well, I assume it was tongue-in-cheek. Even if it wasn't useful, it is great to illustrate that it can be done.

One point he's right about. SQL Server works very well in a Unix\J2EE environment thanks to its rather good JDBC driver.  Of course, the database server itself has to run on  Windows, but that is only a problem to the fanatics.

I recognize I'm a little late to the party, but...

Has anyone tried to use these xp's in an x64 environment? We are moving from 32-bit SQL2K to 64-bit SQL2K5 and would like to continue using these in the new environment, rather than refactor to the SQL2K5 built-ins.

Thanks,

Paul S

Just FYI, I haven't compiled or tested these on 64-bit systems, but for SQL 2K5, I'd highly recommend using the SQLCLR and built-in functionality.  On SQL 2K5 XP's are deprecated, and the SQLCLR code is much easier to maintain.  Creating regular expression matching procedures and functions using .NET and the SQLCLR is relatively simple.  Just my .02.

Thanks,

Michael

Read all records with NULL postcode into a temp table consisting of above columns.

Read first record from the temp table into Cursor
CREATE TABLE #TestSearch ( matchnum int , groupnum int , value varchar ( 128 ) ) 

 select @addline = @add1 --Sets string to be tested
        EXEC master.dbo.xp_regex_match @regExStr  , @addLine, 'I+' , @Ans Output   --Where @RegEx is a Postcode match
        if @ans = 'Y'
        Begin
                INSERT INTO #TestSearch ( matchnum , groupnum , value ) 
                EXEC master.dbo.xp_regex_search @regExStr  , @addline, 'I+' 
        
        if Exists(select 1 from #testSearch)
            select @val = value from #TestSearch    
            Begin
                Update #TestMatch
                Set postcode = @val where id =  @id
            End
        END
       
       Do the same for addressLine2, 3 etc
       
       Get the next record and go back to the top

                Update #TestMatch
                Set postcode = master.dbo.fn_regex_match_and_return_matched_value(addressLine1, @regEx)
                Where Postcode is NULL