SQLServerCentral / Discuss Content Posted by Chad Miller / Article Discussions / Article Discussions by Author / Use SQL-DMO and Excel to Quickly Create Reports for Auditors / Latest Posts

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

cmille19 — Thu, 18 Feb 2010 17:28:31 GMT

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.vbsIf 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.

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

FriedIzLee — Thu, 18 Feb 2010 07:04:27 GMT

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?

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

Tim Edwards-235208 — Wed, 23 Apr 2008 13:13:12 GMT

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.

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

Omri Bahat-228920 — Sat, 03 Dec 2005 17:16:00 GMT

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.

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

andrewkane17 — Thu, 06 Oct 2005 16:08:00 GMT

DOH!

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

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

cmille19 — Thu, 06 Oct 2005 15:43:00 GMT

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

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

andrewkane17 — Thu, 06 Oct 2005 14:46:00 GMT

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

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

John-79567 — Thu, 06 Oct 2005 09:54:00 GMT

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.

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

ipTomN — Tue, 04 Oct 2005 19:17:00 GMT

Simple and elegant.

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

Tom

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

Kevin Jones — Tue, 04 Oct 2005 08:06:00 GMT

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

Kevin

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

Alastair Browne — Tue, 04 Oct 2005 03:30:00 GMT

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

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

SeekQuel — Tue, 04 Oct 2005 01:25:00 GMT

Excellent!

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

Congratulations,

Geert

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

cmille19 — Mon, 29 Aug 2005 20:57:00 GMT

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