I have been using SQL Server BPA for getting good information from Microsoft. I was using its 2005 version in which I used to export the results in CSV format but recently I got two new servers which has got SQL Server 2008 R2 installed on it and I know I can't run BPA 2005 on these, so chose the R2 version of it, but it doesn't have an option to save the report on CSV format, only XML, I have tried using Excel to convert it into CSV but no use, even-though it display the details but can't narrow down the results any idea of converting the results to CSV format?


While PowerShell is high on my list of things to learn I don't know much about it right now and I needed a csv quick, so I wrote a simple query in T-SQL to get the data I needed out of the XML in a tabular format and then just copied that into excel and saved it.

-- get XML from file
    @xml = CAST(A.[BulkColumn] AS XML)

-- query XML data
WITH XMLNAMESPACES ('http://schemas.microsoft.com/powershell/2004/04' as x)
FROM @xml.nodes('x:Objs/x:Obj') A ([obj]) -- "root" objects
CROSS APPLY A.[obj].nodes('x:LST/x:Obj') B ([obj]) -- findings objects
            A.[obj].value('@RefId', 'NVARCHAR(500)') [root_obj_RefId],
            B.[obj].value('@RefId', 'NVARCHAR(500)') [findings_obj_RefId],
            B.[obj].value('x:Props[1]/x:S[@N="ResultNumber"][1]', 'NVARCHAR(500)') [ResultNumber],
            B.[obj].value('x:Props[1]/x:S[@N="ResultId"][1]', 'NVARCHAR(500)') [ResultId],
            B.[obj].value('x:Props[1]/x:S[@N="ModelId"][1]', 'NVARCHAR(500)') [ModelId],
            B.[obj].value('x:Props[1]/x:S[@N="SubModelId"][1]', 'NVARCHAR(500)') [SubModelId],
            B.[obj].value('x:Props[1]/x:S[@N="RuleId"][1]', 'NVARCHAR(500)') [RuleId],
            B.[obj].value('x:Props[1]/x:S[@N="ComputerName"][1]', 'NVARCHAR(500)') [ComputerName],
            B.[obj].value('x:Props[1]/x:S[@N="Context"][1]', 'NVARCHAR(500)') [Context],
            B.[obj].value('x:Props[1]/x:S[@N="Source"][1]', 'NVARCHAR(500)') [Source],
            B.[obj].value('x:Props[1]/x:S[@N="Severity"][1]', 'NVARCHAR(500)') [Severity],
            B.[obj].value('x:Props[1]/x:S[@N="NeutralSeverity"][1]', 'NVARCHAR(500)') [NeutralSeverity],
            B.[obj].value('x:Props[1]/x:S[@N="Category"][1]', 'NVARCHAR(500)') [Category],
            B.[obj].value('x:Props[1]/x:S[@N="Title"][1]', 'NVARCHAR(500)') [Title],
            B.[obj].value('x:Props[1]/x:S[@N="Problem"][1]', 'NVARCHAR(500)') [Problem],
            B.[obj].value('x:Props[1]/x:S[@N="Impact"][1]', 'NVARCHAR(500)') [Impact],
            B.[obj].value('x:Props[1]/x:S[@N="Resolution"][1]', 'NVARCHAR(500)') [Resolution],
            B.[obj].value('x:Props[1]/x:S[@N="Compliance"][1]', 'NVARCHAR(500)') [Compliance],
            B.[obj].value('x:Props[1]/x:S[@N="Help"][1]', 'NVARCHAR(500)') [Help],
            B.[obj].value('x:Props[1]/x:S[@N="Excluded"][1]', 'NVARCHAR(500)') [Excluded]
    ) S
WHERE   S.[ResultNumber] IS NOT NULL
    AND S.[Severity] != 'Information';
  • @ellert hjelmeseth, thanks for the T-SQL solution works like a charm. This was little easy for me since I am a newbie in powershell.run and verified.
    – Biju jose
    Apr 27 '14 at 18:02

You could use powershell:


(all the way at the end of that page)

  • let me check this and have test run on my test machine if it works fine I will reply
    – Biju jose
    Jan 17 '13 at 2:40
  • This is for windows 2012 any Idea of running it in SQL server 2008r2
    – Biju jose
    Jan 17 '13 at 3:11

You can try using Policy-Based Management and import all of the Best Practice Policies which should be the same polices used by BPA. You can even go to codeplex and grab the Enterprise Policy Management Framework that will help you run this against all your servers and store the results into a database. EPMF will also get you some nice management friendly reports. I did a presentation at PASS Summit two years ago on this topic. You and grab my reference notes at



Since you want to use BPA, this PowerShell script will do the work for you.

Another thing to be aware is that you can use below freely available excellent tools for checking health/best practices for SQL Server.

  1. sp_BLITZ or
  2. SQL Power Doc

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.