Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Apr 2002
    Location
    Texas, USA
    Posts
    208
    Thanks
    0
    Thanked 1 Time in 1 Post

    How Best to Monitor a Database Contents (2003/2007)

    I'm not sure how best to word this so here goes, does anyone have any reccomendations on the best way to create something (be it an Infopath from, an Excel Spreadsheet, or anything else) that will allow for live (or as close to live as possible) monitoring of data from a SQL Database.

    Currently we have a view that pulls together data from several tables. This view is basicaly showing a queue of requests. The queue changes constantly as new requests come in and existing requests are completed. Currently I use a SELECT statement on the view in the SQL Server Query Tool that comes with SQL Server 2005/2008. I'm looking for an easier way to do this, something like a form that when loaded/opened will re-query the View and show the results. I'm not too concerned about looks or formatting and so an Excel Spreadsheet like layout would be fine however if I could use something that would allow some formatting control and or grouping that would be nice but I'm not married to that idea.

    We have the complete suite of Office products so I have a lot of choice however none seem to be easily setup to do this or at least its not obvious. I tried to do something in InfoPath and stopped when it showed what looked like a 2 way connection, enabling the pushing down of data as well as pulling and that is a NO-NO in thsi case. I need a READ ONLY view of this View in the database.

    It would also be nice if the 'THING' I came up with had the ability to automatically re-query the View every X seconds so I don't have to continually reload or re-open the item to re-query the view.

    I would think that in all of the MS Office suite there would be something that would easily allow for setting up a monitoring form or Window for this kind of thing but if there is it isn't very obvious.

    Any suggestions?

    Thanks

  2. #2
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: How Best to Monitor a Database Contents (2003/2007)

    You might be able to create a "read-only" username/password in the database. This would be useful because any Office-based solution is likely to reveal those credentials to the curious user.

    I haven't used it myself, but Excel does support scheduling procedures to run in the future. Please see these pages for more information:

    OnTime Method [Excel 2003 VBA Language Reference] on MSDN
    OnTime Method [Excel 2007 Developer Reference] on MSDN

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: How Best to Monitor a Database Contents (2003/2007)

    A database query in Excel (in 2003: Data | Import External Data | New Database Query) has the option to refresh itself at a specified interval. Database queries in Excel are read-only.

  4. #4
    Administrator
    Join Date
    Mar 2001
    Location
    St Louis, Missouri, USA
    Posts
    23,585
    Thanks
    5
    Thanked 1,059 Times in 928 Posts

    Re: How Best to Monitor a Database Contents (2003/2007)

    Have you considered SQL Server Reporting Services? See Microsoft SQL Server 2008: Reporting for a starting point.

    Joe
    Joe

  5. #5
    3 Star Lounger
    Join Date
    Apr 2002
    Location
    Texas, USA
    Posts
    208
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: How Best to Monitor a Database Contents (2003/2007)

    joeperez,

    Actually I am intersted in looking at RS however it would need to be on SQL Server 2005 as we don't have 2008 implemented and probably will noty for a while. I do have the CLient Tools ofr SQL 2008 though. I love the Activity Monitor in SSMS 2008 and would loooovvvee to find a way to duplicate it & create my own custom version that would query these custom views but I've not had much luck in finding out how to create your own custom Activity Monitor report for MSRS; any suggestions?


    Thanks

  6. #6
    Administrator
    Join Date
    Mar 2001
    Location
    St Louis, Missouri, USA
    Posts
    23,585
    Thanks
    5
    Thanked 1,059 Times in 928 Posts

    Re: How Best to Monitor a Database Contents (2003/2007)

    Not yet. I'm just getting into SQL Server 2008 myself. I've got an 800+ page book to read on SSRS and a 900+ page book to read on SSIS (integration services) after I finish the 500+ page general SQL server book. Whew!!! That is in addition to the normal day-to-day stuff.

    Joe
    Joe

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •