'*************************************************************************
' $ScriptName: "GetSqlNativeClientComman" $
'
' Purpose: This script is used to get SQL native client Driver name.
'
' $File: GetSqlNativeClientComman.vbs $
'*************************************************************************
Option Explicit
Function GetSqlDriverName
Dim oAPI1, oReg
Const HKEY_LOCAL_MACHINE = &H80000002
Dim strComputer, strDriverPath, strKeyPath, intValue, strValueName,arrSubKeys,oSubkey,strNewKeyPath,intDotIndex
strComputer = "."
strDriverPath="SQLOLEDB"
Set oReg=GetObject("winmgmts:\\" & _
strComputer & "\root\default:StdRegProv")
strKeyPath = "SOFTWARE\ODBC\ODBCINST.INI"
oReg.EnumKey HKEY_LOCAL_MACHINE, strKeyPath, arrSubKeys
For Each oSubkey In arrSubKeys
intValue = InStr(oSubkey, "SQL Server Native Client")
if intValue <> 0 Then
strValueName = "Driver"
strNewKeyPath = strKeyPath&"\"&oSubkey
oReg.GetStringValue HKEY_LOCAL_MACHINE, strNewKeyPath, strValueName , strDriverPath
strDriverPath = Mid (strDriverPath, InStrRev(strDriverPath, "\")+1)
intDotIndex = InStr(strDriverPath, ".")
strDriverPath = Left(strDriverPath , intDotIndex-1)
end if
Next
GetSqlDriverName = strDriverPath
End Function
'Copyright (c) Microsoft Corporation. All rights reserved.
'*************************************************************************
' $ScriptName: "GetOpsMgrDBPercentFreeSpace" $
'
' Purpose: This script gets Operations Manager DB Percentage of free space.
'
' $File: GetOpsMgrDBPercentFreeSpace.vbs $
'
' Parameters that should be passed to this script
' 0 DatabaseServerName
' 1 DatabaseName
'*************************************************************************
If ObjError.Number <> 0 Then
strMessageToUse = "Error Number: " & ObjError.number & " Error Details: " & ObjError.Description
call GetResponse(true, strMessageToUse, 0)
End If
On Error Resume Next
set oSQLPropertyBag = oAPI.CreatePropertyBag()
ObjError.Save
On Error Goto 0
If ObjError.Number <> 0 Then
strMessageToUse = "Error Number: " & ObjError.number & " Error Details: " & ObjError.Description
call GetResponse(true, strMessageToUse, strComputerName, "", "", "", "", "", "")
End If
'Set the connection string
strDriverName = GetSqlDriverName
Dim connectionString
connectionString = "Provider=" & strDriverName &";Server=" & _
databaseServerName & ";" & _
"Integrated Security=SSPI;Initial Catalog=" & databaseName
call GetDBFreeSpace(connectionString)
Sub GetDBFreeSpace(ByVal connectionString)
On Error Resume Next
Dim cnADOConnection
Set cnADOConnection = CreateObject("ADODB.Connection")
If ObjError.Number <> 0 Then
strMessageToUse = "Error Number: " & ObjError.number & " Error Details: " & ObjError.Description
call GetResponse(true, strMessageToUse, 0)
End If
On Error Resume Next
Dim oResults, dDBUnallocatedSpace, dDBPercentFreeSpace
Set oResults = cnADOConnection.Execute("exec sp_spaceused")
ObjError.Save
On Error Goto 0
If ObjError.Number <> 0 Then
strMessageToUse = "Error Number: " & ObjError.number & " Error Details: " & ObjError.Description
call GetResponse(true, strMessageToUse, 0)
End If
On Error Resume Next
dDBUnallocatedSpace = ConvertSizeStringToNumber(Trim(oResults(2)))
ObjError.Save
On Error Goto 0
If ObjError.Number <> 0 Then
strMessageToUse = "Error Number: " & ObjError.number & " Error Details: " & ObjError.Description
call GetResponse(true, strMessageToUse, 0)
End If
On Error Resume Next
Dim oResultsSize, dbDataSize
Set oResultsSize = cnADOConnection.Execute("exec sp_helpfile")
ObjError.Save
On Error Goto 0
If ObjError.Number <> 0 Then
strMessageToUse = "Error Number: " & ObjError.number & " Error Details: " & ObjError.Description
call GetResponse(true, strMessageToUse, 0)
End If
On Error Resume Next
'Covert from Kb to Mb
dbDataSize = ConvertSizeStringToNumber(Trim(oResultsSize(4)))
dbDataSize = dbDataSize / 1024
dDBPercentFreeSpace = Round((dDBUnallocatedSpace / dbDataSize) * 100, 2)
ObjError.Save
On Error Goto 0
If ObjError.Number <> 0 Then
strMessageToUse = "Error Number: " & ObjError.number & " Error Details: " & ObjError.Description
call GetResponse(true, strMessageToUse, 0)
End If
Set oResults = nothing
cnADOConnection.Close
call GetResponse(false, "", dDBPercentFreeSpace)
End Sub
Function ConvertSizeStringToNumber(sSizeString)
' Remove the MB and return a valid double
sSizeString = Replace(sSizeString, " KB", "")
sSizeString = Replace(sSizeString, " MB", "")
ConvertSizeStringToNumber = CDbl(sSizeString)
End Function
'---------------------------------------------------------------------------
' Retrieves the script output.
'---------------------------------------------------------------------------
Sub GetResponse(boolErrorFlag, strMessage, dDBPercentFreeSpace)
If boolErrorFlag = true Then
oSQLPropertyBag.AddValue "ErrorFlag", "1"
oSQLPropertyBag.AddValue "Error", strMessage
oSQLPropertyBag.AddValue "DBPercentFreeSpace", dDBPercentFreeSpace
oAPI.LogScriptEvent "GetOpsMgrDBPercentFreeSpace.vbs",100,EVENT_TYPE_INFORMATION, strMessage
Else
oSQLPropertyBag.AddValue "ErrorFlag", "0"
oSQLPropertyBag.AddValue "Error", "No error occurred."
oSQLPropertyBag.AddValue "DBPercentFreeSpace", dDBPercentFreeSpace
End If
oAPI.Return(oSQLPropertyBag)
WScript.Quit
End Sub
'---------------------------------------------------------------------------
' Error Handling Class
'---------------------------------------------------------------------------
Class Error
Private m_lNumber
Private m_sSource
Private m_sDescription
Private m_sHelpContext
Private m_sHelpFile
Public Sub Save()
m_lNumber = Hex(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