'Start of Main
'-----------------------------------------------------------------------------------------------
call Main()

Sub Main()

   Dim objParameters, sConnectionString
   Dim oAPI, oBag

   Dim oAPITemp 
   Set oAPITemp = MOMCreateObject("MOM.ScriptAPI")
   oAPITemp.LogScriptEvent "dbDumps2000.vbs", 4000, 4, " script has been executed. "

   Set objParameters = WScript.Arguments

   If objParameters.Count <> 1 Then
       Quit()
   End If

   sConnectionString= objParameters(0)
   
   Set objParameters = Nothing

   Set oAPI = MOMCreateObject("MOM.ScriptAPI")
   Set oBag = oAPI.CreatePropertyBag()

   If GetDumpInfo(oBag, sConnectionString) = 0 Then
       Call oAPI.Return(oBag)
   Else
       Quit()
   End If

End Sub
'End of Main
'-----------------------------------------------------------------------------------------------



Function MomCreateObject(ByVal sProgramId)
    Dim oError
    Set oError = New Error
    
   On Error Resume Next
   Set MomCreateObject = CreateObject(sProgramId)
   oError.Save
   On Error Goto 0
   
   If oError.Number <> 0 Then WScript.Quit
End Function

Function Quit()
   WScript.Quit()
End Function

Class Error
    Private m_lNumber
    Private m_sSource
    Private m_sDescription
    Private m_sHelpContext
    Private m_sHelpFile
    Public Sub Save()
        m_lNumber = Err.number
        m_sSource = Err.Source
        m_sDescription = Err.Description
        m_sHelpContext = Err.HelpContext
        m_sHelpFile = Err.helpfile
    End Sub
    Public Sub Raise()
        Err.Raise m_lNumber, m_sSource, m_sDescription, m_sHelpFile, m_sHelpContext
    End Sub
    Public Sub Clear()
        m_lNumber = 0
        m_sSource = ""
        m_sDescription = ""
        m_sHelpContext = ""
        m_sHelpFile = ""
    End Sub
    Public Default Property Get Number()
        Number = m_lNumber
    End Property
    Public Property Get Source()
        Source = m_sSource
    End Property
    Public Property Get Description()
        Description = m_sDescription
    End Property
    Public Property Get HelpContext()
        HelpContext = m_sHelpContext
    End Property
    Public Property Get HelpFile()
        HelpFile = m_sHelpFile
    End Property    
End Class




Function GetDumpInfo (ByRef oBag, ByVal sConnectionString)


   Dim SCRIPT_SQL 
   Dim cnADOConnection
   Dim strProv
   Dim rsSQLdumps
   Dim bFail
   Dim DBname
   Dim daysSinceFull 
   Dim daysSinceDiff


   SCRIPT_SQL = "SET NOCOUNT ON" &; VbCrLf &;_
   "SELECT" &; VbCrLf &;_
   "d.name as [DBname]" &; VbCrLf &;_
   ", [daysSinceFull] = max(isnull(datediff(dd,b.backup_start_date,getdate()),0)) " &; VbCrLf &;_
   ", [daysSinceDiff] = max(isnull(datediff(dd,bi.backup_start_date,getdate()),0))" &; VbCrLf &;_
   "  " &; VbCrLf &;_
   "FROM [master]..[sysdatabases] d with (nolock)" &; VbCrLf &;_
   "LEFT JOIN [msdb]..[backupset] b  with (nolock) on d.name = b.database_name" &; VbCrLf &;_
   "and b.backup_start_date = (select max(backup_start_date)" &; VbCrLf &;_
   "from [msdb]..[backupset] b2" &; VbCrLf &;_
   "where b.database_name = b2.database_name and b2.type = 'D')" &; VbCrLf &;_
   "LEFT JOIN [msdb]..[backupset] bi  with (nolock) on d.name = bi.database_name" &; VbCrLf &;_
   "and bi.backup_start_date = (select max(backup_start_date)" &; VbCrLf &;_
   "from [msdb]..[backupset] b3" &; VbCrLf &;_
   "where bi.database_name = b3.database_name and b3.type = 'I')" &; VbCrLf &;_
   "where d.name not in ('Pubs','tempdb','Northwind', 'Adventureworks') and d.status < 30" &; VbCrLf &;_
   "group by d.name "
    

   'on error resume next
   
   Set cnADOConnection = MomCreateObject("ADODB.Connection")
   cnADOConnection.Provider = "sqloledb"
   cnADOConnection.ConnectionTimeout = 30
   strProv = "Server=" &; sConnectionString &; ";Database=msdb;Trusted_Connection=yes"
        
   Err.Clear
   cnADOConnection.Open strProv
   if 0 <> Errr.number then
       ' Throw
       Exit Function
   end if


   Err.Clear
   Set rsSQLdumps = cnADOConnection.Execute(SCRIPT_SQL)
   If 0 <> Err.number Then
       ' Throw
       Exit Function
   End If

   If 0 = rsSQLdumps.State Then
       ' No records returned, recordset is likely closed.
   Exit Function
   End If

   Do
       bFail = True
       Err.Clear
       if rsSQLdumps.EOF then
           if 0 <> Err.number then Exit Do
           bFail = False
           Exit Do
       End If
       If 0 <> Err.number Then Exit Do

           DBname = rsSQLdumps("DBname").Value
           daysSinceFull = rsSQLdumps("daysSinceFull").Value
           daysSinceDiff = rsSQLdumps("daysSinceDiff").Value
           Call oBag.AddValue(DBname &; "-daysSinceFull", CInt(daysSinceFull))
           Call oBag.AddValue(DBname &; "-daysSinceDiff", CInt(daysSinceDiff))

       Err.Clear
       rsSQLdumps.MoveNext
       If 0 <> Err.number then Exit Do
   Loop


   
   If bFail Then
       ' Throw
   End if

   Set cnADOConnection = Nothing
   Set rsSQLdumps = Nothing


    GetDumpInfo = 0

End Function