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

Question

Post reply

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

cmille19

SSCertifiable

Points: 5970
More actions
August 29, 2005 at 8:57 pm

#187245

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

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

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

SeekQuel SSCommitted Points: 1818 More actions · Answer 1

Excellent!

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

Congratulations,

Geert

Alastair Browne Valued Member Points: 61 More actions · Answer 2

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

Kevin Jones SSC Enthusiast Points: 114 More actions · Answer 3

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

Kevin

ipTomN Say Hey Kid Points: 690 More actions · Answer 4

Simple and elegant.

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

Tom

John-79567 SSCommitted Points: 1892 More actions · Answer 5

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.

andrewkane17 SSCertifiable Points: 7529 More actions · Answer 6

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

cmille19 SSCertifiable Points: 5970 More actions · Answer 7

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").

andrewkane17 SSCertifiable Points: 7529 More actions · Answer 8

DOH!

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

Omri Bahat-228920 Mr or Mrs. 500 Points: 540 More actions · Answer 9

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.

Tim Edwards-235208 SSC Enthusiast Points: 125 More actions · Answer 10

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.

FriedIzLee SSC Rookie Points: 25 More actions · Answer 11

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?

cmille19 SSCertifiable Points: 5970 More actions · Answer 12

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.