' Parameters that should be passed to this script
' 0 MPElement ID ($MPElement$)
' 1 Target Id for ME this rule is running against ($Target/Id$)
' 2 Computer (FQDN) that SQL instance is hosted on
' 3 SQL Connection String for the instance that the DBs are being discovered on
' 4 SQL Instance that this rule is being run for
Set oAPI = MOMCreateObject("MOM.ScriptAPI")
set oSQLDiscoveryData = oAPI.CreateDiscoveryData(0, SourceID, ManagedEntityId)
If DoReplicationDiscovery(TargetComputerID, InstanceName, ConnectionString, oSQLDiscoveryData) >= 0 Then Call oAPI.Return(oSQLDiscoveryData)
Function DoReplicationDiscovery(ByVal sTargetComputerID, ByVal sSqlInstance, ByVal sSQLConnectionString, ByVal oSQLDiscoveryData)
Dim e
Dim oDistInstance
Dim cnADOConnection
Dim cnADOConnection2
Dim strProv
Dim oResults
Dim oResults2
Dim oResultsDistributor
Dim oRel
Dim oDatabase
Set e = New Error
Set cnADOConnection = MomCreateObject("ADODB.Connection")
cnADOConnection.Provider = "sqloledb"
cnADOConnection.ConnectionTimeout = 15
strProv = "Server=" & sSQLConnectionString & ";Database=master;Trusted_Connection=yes"
e.Clear
On Error Resume Next
cnADOConnection.Open strProv
e.Save
On Error Goto 0
if 0 <> Err.number then
'Error event in here
'g_oSQL.CreateConnectionFailureAlert sInstance, Err.number, Err.Description
DoReplicationDiscovery = SQL_DISCOVERY_CONNECT_FAILURE
Exit Function
end if
' Create the publication and subsciption objects
On Error Resume Next
' query for the list of databases which are not database snapshots
Set oResults = cnADOConnection.Execute("SELECT name, state_desc FROM sys.databases WHERE source_database_id IS NULL")
e.Save
On Error Goto 0
if 0 <> e.number then
'Error event in here
DoReplicationDiscovery = SQL_DISCOVERY_QUERY_FAILURE
Exit Function
end if
Set cnADOConnection2 = MomCreateObject("ADODB.Connection")
cnADOConnection2.Provider = "sqloledb"
cnADOConnection2.ConnectionTimeout = 15
Dim DistributorName
Dim sDBName
Do while Not oResults.EOF
sDBName = oResults(0)
strProv = "Server=" & sSQLConnectionString & ";Database=" & sDBName & ";Trusted_Connection=yes"
On Error Resume Next
cnADOConnection2.Open strProv
e.Save
On Error Goto 0
If e.number = 0 then
On Error Resume Next
Set oResults2 = cnADOConnection2.Execute("sp_helppublication")
e.Save
On Error Goto 0
If e.number = 0 then
If oResults2.Fields.Count > 0 Then
' Create the DB instance so if it does not exis we dont get an error submitting the hosted instnae
Set oDatabase = oSQLDiscoveryData.CreateClassInstance("$MPElement[Name='Microsoft.SQLServer.2008.Database']$")
With oDatabase
.AddProperty "$MPElement[Name='Windows!Microsoft.Windows.Computer']/PrincipalName$", TargetComputerID
.AddProperty "$MPElement[Name='SQL!Microsoft.SQLServer.ServerRole']/InstanceName$", sSqlInstance
.AddProperty "$MPElement[Name='SQL!Microsoft.SQLServer.Database']/DatabaseName$",sDBName
End With
Call oSQLDiscoveryData.AddInstance(oDatabase)
Do While Not oResults2.EOF
Set oDistInstance = oSQLDiscoveryData.CreateClassInstance("$MPElement[Name='Microsoft.SQLServer.2008.Publication']$")
With oDistInstance
.AddProperty "$MPElement[Name='Windows!Microsoft.Windows.Computer']/PrincipalName$", TargetComputerID
.AddProperty "$MPElement[Name='SQL!Microsoft.SQLServer.ServerRole']/InstanceName$", sSqlInstance
.AddProperty "$MPElement[Name='SQL!Microsoft.SQLServer.Database']/DatabaseName$", sDBName
.AddProperty "$MPElement[Name='SQL!Microsoft.SQLServer.Publication']/PublicationID$", oResults2(0)
.AddProperty "$MPElement[Name='SQL!Microsoft.SQLServer.Publication']/Name$", oResults2(1)
.AddProperty "$MPElement[Name='SQL!Microsoft.SQLServer.Publication']/IsActive$", CBool(oResults2(3))
.AddProperty "$MPElement[Name='SQL!Microsoft.SQLServer.Publication']/Description$", oResults2(7)
.AddProperty "$MPElement[Name='System!System.Entity']/DisplayName$", oResults2(1)
End With
Call oSQLDiscoveryData.AddInstance(oDistInstance)
oResults2.MoveNext
Loop
End If
End If
On Error Resume Next
Set oResultsDistributor = cnADOConnection2.Execute("sp_helpdistributor")
e.Save
On Error Goto 0
If oResultsDistributor.Fields.Count > 0 Then DistributorName = oResultsDistributor(0)
On Error Resume Next
Set oResults2 = cnADOConnection2.Execute("sp_helpsubscription")
e.Save
On Error Goto 0
If e.number = 0 then
If oResults2.Fields.Count > 0 Then
Do While Not oResults2.EOF
Set oDistInstance = oSQLDiscoveryData.CreateClassInstance("$MPElement[Name='Microsoft.SQLServer.2008.Subscription']$")
With oDistInstance
.AddProperty "$MPElement[Name='Windows!Microsoft.Windows.Computer']/PrincipalName$", TargetComputerID
.AddProperty "$MPElement[Name='SQL!Microsoft.SQLServer.ServerRole']/InstanceName$", sSqlInstance
.AddProperty "$MPElement[Name='SQL!Microsoft.SQLServer.Database']/DatabaseName$", oResults2(3)
.AddProperty "$MPElement[Name='SQL!Microsoft.SQLServer.Subscription']/Name$", oResults2(8)
.AddProperty "$MPElement[Name='SQL!Microsoft.SQLServer.Subscription']/Publisher$", oResults2(0)
.AddProperty "$MPElement[Name='SQL!Microsoft.SQLServer.Subscription']/PublisherDB$", sDBName
.AddProperty "$MPElement[Name='SQL!Microsoft.SQLServer.Subscription']/Publication$", oResults2(1)
.AddProperty "$MPElement[Name='SQL!Microsoft.SQLServer.Subscription']/Distributor$", DistributorName
.AddProperty "$MPElement[Name='System!System.Entity']/DisplayName$", oResults2(8)
End With
Call oSQLDiscoveryData.AddInstance(oDistInstance)
oResults2.MoveNext
Loop
End If
End If
cnADOConnection2.Close()
End If
oResults.MoveNext
Loop
cnADOConnection.Close()
DoReplicationDiscovery = 0
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 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 ThrowScriptError "Unable to create automation object '" & sProgramId & "'", oError
End Function
'******************************************************************************
Function ThrowScriptErrorNoAbort(ByVal sMessage, ByVal oErr)
On Error Resume Next
Dim oAPITemp
Set oAPITemp = MOMCreateObject("MOM.ScriptAPI")
oAPITemp.LogScriptEvent "DiscoverSQL2008ReplicationPublicationsSubscriptions.vbs", 4001, 1, sMessage & ". " & oErr.m_sDescription
End Function
'******************************************************************************
Function ThrowScriptError(Byval sMessage, ByVal oErr)
On Error Resume Next
ThrowScriptErrorNoAbort sMessage, oErr
Quit()
End Function
'******************************************************************************
Function ConvertSizeStringToNumber(sSizeString)
' Remove the KB and return a valid double
sSizeString = Replace(sSizeString, " KB", "")
ConvertSizeStringToNumber = CDbl(sSizeString)
End Function