Use SQL-DMO and Excel to Quickly Create Reports for Auditors

  • Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/cmiller/usesqldmoandexceltoquicklycreatereportsforauditors.asp

  • Excellent!

    First time I see an easy to understand and very useful SQL DMO example.

    Congratulations,

    Geert

  • Very good concept, but unfortunately as always, I can never get VBA script to work 🙁

    When I have time, I may try to do the same thing using .NET, C# and Office Interop Libraries.

    Alastair

     

  • Incredibly powerful! Thank you for the great example. Awesome stuff!

    Kevin

  • Simple and elegant.

    This one worked for me straigt from the 'box'.

    Tom

  • i keep getting a server doesnt exist or access denied error.  I triple checked the server name and verified my nt account has SA privs.  Maybe im doing something wrong.

  • I am having message server does not exist. Any tips on what I am doing wrong would be appreciated.

  • Have you followed the instructions on the helptab of the worksheet? The config tab contains the following entries in column A, all of which need to be deleted (including List your SQL Server HERE) and replaced with the SQL Server names in you environment:

    List your SQL Server HERE
    SQLPRD1
    SQLPRD2
    SQLPRD3

    The VBA code loops through the list of server specified in on the config tab column A (Column A is a named Excel Range "Server").

  • DOH!

     

    Thanks cmille19, should have actualy used my brain on this. Working like a charm now.

  • The example you provided is great. One comment though- the same goal can be achieved without any development efforts by running simple SQL queries on all databases and servers in parallel and returning aggregated results from all queried databases, by using tools such as SQL Farm Combine.

    The SQL-DMO code is good, however it runs serially and it can take quite some time for results to return, especially if you extend the example to return larger result sets, or retrieve data from a large number of databases and servers.

    Editor's Note: : Dr. Omri Bahat works for SQLFarms, a software vendor of SQL Server tools.

  • This was a great article! Only problem is that I get a page saying "Search for any content tagged Miscellaneous & usesqldmoandexceltoquicklycreatereportsforauditors & 2064 & SQLDMO_Macros.xls" and "Sorry, nothing found for this search" when I try to download the spreadsheet. I am anxious to take a look at the spreadsheet and actually provide some much needed reporting to management.

  • I get a "Run-Time Error '429': ActiveX component can't create object" error when I "Select Server". Can you please tell me why that would happen?

  • This is an older article I wrote. The underlying code relies on SQL-DMO which is installed with SQL Server 2000 Client Tools. I suspect you may not have SQL-DMO installed on the machine from which you are running. You can test by attempting to create a DMO object. Save the following text as a vbs file:

    Set oServer = CreateObject("SQLDMO.SQLServer")

    Next execute cscript.exe c:\pathtoyourfile.vbs

    If no errors you have DMO installed. One other thought -- Excel macro behavior has changed in Excel 2007, be sure you're able to execute macros.

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply