VBScript to delete ODBC data source on multiple servers

Hi,
I have 2 scripts that i have found somewhere on the internet. One that creates an ODBC data source across multiple servers and one that deletes a data source on a specified machine. What i need to do is essentially put the two together so that i can delete across multiple servers easily. Any help would be much appreciated as i know little VB

Delete
Const HKEY_LOCAL_MACHINE = &H80000002

strComputer = "."
 
Set objReg=GetObject("winmgmts:{impersonationLevel=impersonate}!\\" & _ 
    strComputer & "\root\default:StdRegProv")
 
strKeyPath = "SOFTWARE\ODBC\ODBC.INI\XXX"
objReg.DeleteKey HKEY_LOCAL_MACHINE, strKeyPath

strKeyPath = "SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources"
strValueName = "XXX"
objReg.DeleteValue HKEY_LOCAL_MACHINE,strKeyPath,strValueName

Open in new window


Create
Option Explicit
'Constants
Const HKEY_CLASSES_ROOT       = &H80000000
Const HKEY_CURRENT_USER       = &H80000001
Const HKEY_LOCAL_MACHINE       = &H80000002
Const HKEY_USERS             = &H80000003
Const HKEY_CURRENT_CONFIG       = &H80000005

'Variables
On Error resume next
Dim DataSourceName
Dim DatabaseName
Dim Description
Dim DriverPath
Dim LastUser
Dim Server
Dim Trusted_connection
Dim DriverName
Dim InputFile
Dim iFSO
Dim ifile
Dim sComputer
Dim sPath

'Value assignment

DataSourceName = "DSN"
DatabaseName = "DSNBMM"
DriverPath = "C:\WINNT\System32\sqlsrv32.dll"
LastUser="xxx"
Server="xxx"
Trusted_connection="No"
Description="BMM:" & DatabaseName
DriverName="SQL Server"
InputFile="Servers.txt"
Set iFSO = CreateObject("Scripting.FilesyStemObject")
Set ifile = iFSO.OpenTextFile(inputfile)  
sPath            = "SOFTWARE\ODBC\ODBC.INI\" & DataSourceName



'Read and loop through the input file
Do until ifile.AtEndOfLine
sComputer      = ifile.ReadLine
If (0 = CreateRegKey(sComputer, HKEY_LOCAL_MACHINE, sPath)) Then
SetRegKeyStrValue sComputer, HKEY_LOCAL_MACHINE, sPath, "Database", DatabaseName
SetRegKeyStrValue sComputer, HKEY_LOCAL_MACHINE, sPath, "Description", Description
SetRegKeyStrValue sComputer, HKEY_LOCAL_MACHINE, sPath, "Driver", DriverPath
SetRegKeyStrValue sComputer, HKEY_LOCAL_MACHINE, sPath, "LastUser",LastUser
SetRegKeyStrValue sComputer, HKEY_LOCAL_MACHINE, sPath, "Server",Server
SetRegKeyStrValue sComputer, HKEY_LOCAL_MACHINE, sPath, "Trusted_Connection",Trusted_connection
Else      
Exit Do      
End If      


'Write in "ODBC Data Sources" Key to allow ODBC Manager list & manage the new DSN
SetRegKeyStrValue sComputer, HKEY_LOCAL_MACHINE, "SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources", DataSourceName , DriverName
'MsgBox (sComputer & " DONE!")
 Loop
 ifile.Close
 Set ifile = Nothing
 Set iFSO = Nothing

'Create RegKey Function

 Function CreateRegKey (sComputer, hTree, sKey)
Dim oRegistry
Dim lResult      
Set oRegistry      = GetObject("winmgmts:{impersonationLevel=impersonate}//" & sComputer & "/root/default:StdRegProv")
lResult = oRegistry.CreateKey(hTree, sPath)
If (lResult = 0) And (Err.Number = 0) Then
CreateRegKey = 0
Else
CreateRegKey = 1
msgbox("Create Key " & sKey & " Failed")
End If
Set oRegistry = Nothing
End Function

'set RegKey Function

 Function SetRegKeyStrValue (sComputer, hTree, sKey, sValueName, sValue)
Dim oRegistry
Dim lResult      
Set oRegistry      = GetObject("winmgmts:{impersonationLevel=impersonate}//" & sComputer & "/root/default:StdRegProv")
lResult = oRegistry.SetStringValue(hTree,   sKey,  sValueName,  sValue)
If (lResult = 0) And (Err.Number = 0) Then
SetRegKeyStrValue = 0
Else
SetRegKeyStrValue = 1
msgbox("Set Value for " & sKey & " Failed")
End If
Set oRegistry = Nothing
End Function

Open in new window

johnswaineAsked:
Who is Participating?
 
RobSampsonConnect With a Mentor Commented:
Hi, try this.
I have added sMode which you can set to add or delete so that you can use the same script to do both.
Regards,
Rob.

Option Explicit
'Constants
Const HKEY_CLASSES_ROOT       = &H80000000
Const HKEY_CURRENT_USER       = &H80000001
Const HKEY_LOCAL_MACHINE       = &H80000002
Const HKEY_USERS             = &H80000003
Const HKEY_CURRENT_CONFIG       = &H80000005

'Variables
On Error resume next
Dim DataSourceName
Dim DatabaseName
Dim Description
Dim DriverPath
Dim LastUser
Dim Server
Dim Trusted_connection
Dim DriverName
Dim InputFile
Dim iFSO
Dim ifile
Dim sComputer
Dim sPath
Dim sMode

'sMode can be "add" or "delete"
sMode = "add"

'Value assignment

DataSourceName = "DSN"
DatabaseName = "DSNBMM"
DriverPath = "C:\WINNT\System32\sqlsrv32.dll"
LastUser="xxx"
Server="xxx"
Trusted_connection="No"
Description="BMM:" & DatabaseName
DriverName="SQL Server"
InputFile="Servers.txt"
Set iFSO = CreateObject("Scripting.FilesyStemObject")
Set ifile = iFSO.OpenTextFile(inputfile)  
sPath            = "SOFTWARE\ODBC\ODBC.INI\" & DataSourceName



'Read and loop through the input file
Do until ifile.AtEndOfLine
	sComputer      = ifile.ReadLine
	If LCase(sMode) = LCase("add") Then
		If (0 = CreateRegKey(sComputer, HKEY_LOCAL_MACHINE, sPath)) Then
			SetRegKeyStrValue sComputer, HKEY_LOCAL_MACHINE, sPath, "Database", DatabaseName
			SetRegKeyStrValue sComputer, HKEY_LOCAL_MACHINE, sPath, "Description", Description
			SetRegKeyStrValue sComputer, HKEY_LOCAL_MACHINE, sPath, "Driver", DriverPath
			SetRegKeyStrValue sComputer, HKEY_LOCAL_MACHINE, sPath, "LastUser",LastUser
			SetRegKeyStrValue sComputer, HKEY_LOCAL_MACHINE, sPath, "Server",Server
			SetRegKeyStrValue sComputer, HKEY_LOCAL_MACHINE, sPath, "Trusted_Connection",Trusted_connection
			
			'Write in "ODBC Data Sources" Key to allow ODBC Manager list & manage the new DSN
			SetRegKeyStrValue sComputer, HKEY_LOCAL_MACHINE, "SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources", DataSourceName , DriverName
			'MsgBox (sComputer & " DONE!")
		Else      
			Exit Do      
		End If
	ElseIf LCase(sMode) = LCase("delete") Then
		DeleteRegKey sComputer, HKEY_LOCAL_MACHINE, sPath
		DeleteRegStrValue sComputer, HKEY_LOCAL_MACHINE, "SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources", DataSourceName
		'MsgBox (sComputer & " DONE!")
	End If
Loop
 ifile.Close
 Set ifile = Nothing
 Set iFSO = Nothing

'Create RegKey Function

 Function CreateRegKey (sComputer, hTree, sKey)
Dim oRegistry
Dim lResult      
Set oRegistry      = GetObject("winmgmts:{impersonationLevel=impersonate}//" & sComputer & "/root/default:StdRegProv")
lResult = oRegistry.CreateKey(hTree, sPath)
If (lResult = 0) And (Err.Number = 0) Then
CreateRegKey = 0
Else
Err.Clear
CreateRegKey = 1
msgbox("Create Key " & sKey & " Failed")
End If
Set oRegistry = Nothing
End Function

'set RegKey Function

 Function SetRegKeyStrValue (sComputer, hTree, sKey, sValueName, sValue)
Dim oRegistry
Dim lResult      
Set oRegistry      = GetObject("winmgmts:{impersonationLevel=impersonate}//" & sComputer & "/root/default:StdRegProv")
lResult = oRegistry.SetStringValue(hTree,   sKey,  sValueName,  sValue)
If (lResult = 0) And (Err.Number = 0) Then
SetRegKeyStrValue = 0
Else
Err.Clear
SetRegKeyStrValue = 1
msgbox("Set Value for " & sKey & " Failed")
End If
Set oRegistry = Nothing
End Function

Function DeleteRegKey(sComputer, hTree, sKey)
	Dim oRegistry
	Dim lResult
	Set oRegistry=GetObject("winmgmts:{impersonationLevel=impersonate}!\\" & sComputer & "\root\default:StdRegProv")
	lResult = oRegistry.DeleteKey(hTree, sKey)
	If (lResult = 0) And (Err.Number = 0) Then
	DeleteRegKey = 0
	Else
	Err.Clear
	DeleteRegKey = 1
	msgbox("Delete key for " & sKey & " Failed")
	End If
End Function

Function DeleteRegStrValue(sComputer, hTree, sKey, sValueName)
	Dim oRegistry
	Dim lResult
	Set oRegistry=GetObject("winmgmts:{impersonationLevel=impersonate}!\\" & sComputer & "\root\default:StdRegProv")
	sKey = "SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources"
	sValueName = "XXX"
	lResult = oRegistry.DeleteValue(hTree, sKey, sValueName)
	If (lResult = 0) And (Err.Number = 0) Then
	DeleteRegStrValue = 0
	Else
	Err.Clear
	DeleteRegStrValue = 1
	msgbox("Delete Value for " & sKey & "\" & sValueName & " Failed")
	End If
End Function

Open in new window

0
 
johnswaineAuthor Commented:
Hi Rob,

Thanks a lot for this, it will save me a hell of a lot of time.

In the DeleteRegStrValue Function, I changed sValueName = "XXX" to sValueName = datasourcename and it works perfectly

Thanks again
Neil
0
 
RobSampsonCommented:
Ah yes, sorry about that....that line doesn't actually need to be there, because sValueName is passed to the function anyway.
Thanks for the grade.
Regards,
Rob.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.