Function IsValidObject(ByVal oObject)
IsValidObject = False
If IsObject(oObject) Then
If Not oObject Is Nothing Then
IsValidObject = True
End If
End If
End Function
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
'#Include File:SQL2008Constants.vbs
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 ThrowScriptErrorNoAbort(ByVal sMessage, ByVal oErr)
On Error Resume Next
Dim oAPITemp
Set oAPITemp = MOMCreateObject("MOM.ScriptAPI")
oAPITemp.LogScriptEvent WScript.ScriptName, 4001, 1, sMessage & ". " & oErr.Description
End Function
Function ThrowScriptError(Byval sMessage, ByVal oErr)
On Error Resume Next
ThrowScriptErrorNoAbort sMessage, oErr
Quit()
End Function
Sub HandleError(customMessage)
Dim localLogger
If Not (Err.number = 0) Then
Set localLogger = new ScriptLogger
localLogger.LogFormattedError(customMessage)
Wscript.Quit 0
End If
End Sub
Function HandleErrorContinue(customMessage)
Dim localLogger
HandleErrorContinue = False
If Not (Err.number = 0) Then
Set localLogger = new ScriptLogger
localLogger.LogFormattedError(customMessage)
Err.Clear
HandleErrorContinue = True
End If
End Function
'#Include File:ConnectionString.vbs
Function BuildConnectionString(strServer, strDatabase)
ON ERROR RESUME NEXT
Err.Clear
Dim dataSource
dataSource = BuildServerName(strServer, "")
BuildConnectionString = "Data Source=" & EscapeConnStringValue(dataSource) & ";Initial Catalog=" & EscapeConnStringValue(strDatabase) & ";Integrated Security=SSPI"
End Function
Function BuildConnectionStringWithPort(ByVal strServer, ByVal strDatabase, ByVal tcpPort)
ON ERROR RESUME NEXT
Err.Clear
Dim dataSource
dataSource = strServer
If ((tcpPort <> "0") And (tcpPort <> "")) Then
dataSource = dataSource & "," & tcpPort
End If
BuildConnectionStringWithPort = "Data Source=" & EscapeConnStringValue(dataSource) & ";Initial Catalog=" & EscapeConnStringValue(strDatabase) & ";Integrated Security=SSPI"
End Function
' This function should be used to escape Connection String keywords.
Function EscapeConnStringValue (ByVal strValue)
ON ERROR RESUME NEXT
Err.Clear
EscapeConnStringValue = """" + Replace(strValue, """", """""") + """"
End Function
Function EscapeWQLString (ByVal strValue)
ON ERROR RESUME NEXT
Err.Clear
EscapeWQLString = Replace(strValue, "'", "\'")
End Function
Function GetTcpPort (ByVal strServer)
ON ERROR RESUME NEXT
Err.Clear
Dim tcpPort
tcpPort = ""
Call BuildServerName(strServer, tcpPort)
GetTcpPort = tcpPort
End Function
Function BuildServerName(ByVal strServer, ByRef tcp)
ON ERROR RESUME NEXT
Err.Clear
Dim pathArray, instanceName, computerName, ip, serverName
Dim oWMI, oQuery
ip= ""
pathArray = Split(strServer, "\")
computerName = pathArray(0)
instanceName = "MSSQLSERVER"
if (pathArray.Count > 1) Then
instanceName = pathArray(1)
End If
serverName = strServer
Set oWMI = GetObject("winmgmts:\\" & computerName & "\root\Microsoft\SqlServer\" & SQL_WMI_NAMESPACE)
Set oQuery = oWMI.ExecQuery("SELECT * FROM ServerNetworkProtocolProperty WHERE ProtocolName = 'Tcp' AND InstanceName = '"& EscapeWQLString(instanceName) &"' AND PropertyName = 'ListenOnAllIPs'")
If oQuery.Count >0 Then
Dim isListenAll
Set isListenAll = oQuery.ItemIndex(0)
If(isListenAll.PropertyNumVal = 1) Then
Set oQuery = oWMI.ExecQuery("SELECT * FROM ServerNetworkProtocolProperty WHERE ProtocolName = 'Tcp' AND InstanceName = '"& EscapeWQLString(instanceName) &"' AND IPAddressName = 'IPAll' AND (PropertyName = 'TcpPort' OR PropertyName = 'TcpDynamicPorts') AND PropertyStrVal <> ''")
If (oQuery.Count > 0) Then
tcp = oQuery.ItemIndex(0).PropertyStrVal
If ((tcp <> "0") And (tcp <> "")) Then
serverName = serverName & "," & tcp
Else tcp = ""
End If
End If
Else
Set oQuery = oWMI.ExecQuery("SELECT * FROM ServerNetworkProtocolProperty WHERE ProtocolName = 'Tcp' AND InstanceName = '"& EscapeWQLString(instanceName) &"' AND IPAddressName <> '' AND PropertyName = 'Enabled' AND PropertyNumVal = 1")
If (oQuery.Count > 0) Then
Dim ipAddressName
ipAddressName = oQuery.ItemIndex(0).IPAddressName
Set oQuery = oWMI.ExecQuery("SELECT * FROM ServerNetworkProtocolProperty WHERE ProtocolName = 'Tcp' AND InstanceName = '"& EscapeWQLString(instanceName) &"' AND IPAddressName = '"& EscapeWQLString(ipAddressName) &"' AND (PropertyName = 'TcpPort' OR PropertyName = 'TcpDynamicPorts') AND PropertyStrVal <> ''")
If (oQuery.Count > 0) Then
tcp = oQuery.ItemIndex(0).PropertyStrVal
End If
Set oQuery = oWMI.ExecQuery("SELECT * FROM ServerNetworkProtocolProperty WHERE ProtocolName = 'Tcp' AND InstanceName = '"& EscapeWQLString(instanceName) &"' AND IPAddressName = '"& EscapeWQLString(ipAddressName) &"' AND PropertyName = 'IpAddress' AND PropertyStrVal <> ''")
If (oQuery.Count > 0) Then
ip = oQuery.ItemIndex(0).PropertyStrVal
End If
If ip <> "" Then
serverName = ip
End If
If ((tcp <> "0") And (tcp <> "")) Then
serverName = servername & "," & tcp
Else tcp = ""
End If
End If
End If
End If
On Error Goto 0
BuildServerName = serverName
End Function'#Include File:SQLReplicationPublicationsSubscriptionsDiscovery.vbs
'Copyright (c) Microsoft Corporation. All rights reserved.
' 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
e.Clear
On Error Resume Next
cnADOConnection.Open strProv
e.Save
On Error Goto 0
if 0 <> e.Number then
e.Clear
strProv = BuildConnectionString(sSQLConnectionString, "master")
cnADOConnection.Open strProv
e.Save
if 0 <> e.Number then
'Error event in here
'g_oSQL.CreateConnectionFailureAlert sInstance, Err.number, Err.Description
DoReplicationDiscovery = SQL_DISCOVERY_CONNECT_FAILURE
Exit Function
End If
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 = 30
Dim DistributorName
Dim sDBName, bNeedCreateDB
Do While Not oResults.EOF
bNeedCreateDB = False
' Change database context
' TODO: Check if we can use 'USE' statement instead of reconnection
sDBName = oResults(0)
strProv = BuildConnectionStringWithPort(sSQLConnectionString, sDBName, sTcpPort)
e.Clear
On Error Resume Next
cnADOConnection2.Open strProv
e.Save
On Error Goto 0
If e.Number = 0 Then
' Discover transactional and snapshot publications
On Error Resume Next
Set oResults2 = cnADOConnection2.Execute("sp_helppublication")
e.Save
On Error Goto 0
If e.Number = 0 And oResults2.Fields.Count > 0 Then
bNeedCreateDB = True
Do While Not oResults2.EOF
Set oDistInstance = oSQLDiscoveryData.CreateClassInstance(SQL_REPLICATION_PUBLICATION_CLASS)
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
' Discover merge publications
On Error Resume Next
Set oResults2 = cnADOConnection2.Execute("sp_helpmergepublication")
e.Save
On Error Goto 0
If e.Number = 0 And oResults2.Fields.Count > 0 Then
bNeedCreateDB = True
Do While Not oResults2.EOF
Set oDistInstance = oSQLDiscoveryData.CreateClassInstance(SQL_REPLICATION_PUBLICATION_CLASS)
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$", -1 * 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
' Try get distributor name
On Error Resume Next
Set oResultsDistributor = cnADOConnection2.Execute("sp_helpdistributor")
On Error Goto 0
If oResultsDistributor.Fields.Count > 0 Then DistributorName = oResultsDistributor(0)
' Discover transactional and snapshot subscriptions
On Error Resume Next
Set oResults2 = cnADOConnection2.Execute("sp_helpsubscription")
e.Save
On Error Goto 0
If e.Number = 0 And oResults2.Fields.Count > 0 Then
bNeedCreateDB = True
Do While Not oResults2.EOF
Set oDistInstance = oSQLDiscoveryData.CreateClassInstance(SQL_REPLICATION_SUBSCRIPTION_CLASS)
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.Subscription']/Name$", oResults2(8)
.AddProperty "$MPElement[Name='SQL!Microsoft.SQLServer.Subscription']/Publisher$", TargetComputerID
.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
' Discover merge subscriptions
On Error Resume Next
Set oResults2 = cnADOConnection2.Execute("sp_helpmergesubscription")
e.Save
On Error Goto 0
If e.Number = 0 And oResults2.Fields.Count > 0 Then
bNeedCreateDB = True
Do While Not oResults2.EOF
Set oDistInstance = oSQLDiscoveryData.CreateClassInstance(SQL_REPLICATION_SUBSCRIPTION_CLASS)
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.Subscription']/Name$", oResults2(0)
.AddProperty "$MPElement[Name='SQL!Microsoft.SQLServer.Subscription']/Publisher$", TargetComputerID
.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(0)
End With
Call oSQLDiscoveryData.AddInstance(oDistInstance)
oResults2.MoveNext
Loop
End If
If bNeedCreateDB Then
' Create the DB instance so if it does not exist we won't get an error submitting the hosted instance
Set oDatabase = oSQLDiscoveryData.CreateClassInstance(SQL_DATABASE_CLASS)
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)
End If