Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?

share|improve this question
up vote 2 down vote accepted

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
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';
share|improve this answer
@ellert hjelmeseth, thanks for the T-SQL solution works like a charm. This was little easy for me since I am a newbie in and verified. – Biju jose Apr 27 '14 at 18:02

You could use powershell:

(all the way at the end of that page)

share|improve this answer
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

share|improve this answer

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
share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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