Solved

VBScript to delete ODBC data source on multiple servers

Posted on 2010-08-17
3
983 Views
Last Modified: 2012-06-27
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

0
Comment
Question by:johnswaine
  • 2
3 Comments
 
LVL 65

Accepted Solution

by:
RobSampson earned 500 total points
ID: 33461083
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
 

Author Comment

by:johnswaine
ID: 33462908
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
 
LVL 65

Expert Comment

by:RobSampson
ID: 33463355
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

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

When it comes to writing scripts for a Client/Server computing environment it is essential to consider some way of enabling the authentication functionality within a script. This sort of consideration mainly comes into the picture when we are dealin…
Introduction During my participation as a VBScript contributor at Experts Exchange, one of the most common questions I come across is this: "I have a script that runs against only one computer. How can I make it run against a list of computers in …
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now