Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Jul 24, 2003

How to Create ODBC DSN on multiple SQL server machines

By DatabaseJournal.com Staff

by MAK [Muthusamy Anantha Kumar]

In a typical Client - Server environment it is often necessary to create ODBC DSN on many client machines or servers for front-end applications such as VB, or MS-Access, which uses ODBC DSN for linked tables. Even for creating linked servers to heterogeneous systems like Oracle, Sybase and mainframe DB2 we need to create ODBC DSN. This is usually a tedious job for the Network Administrators since they must log on to every machine to create those ODBC DSN. The following script helps in creating ODBC DSN (for SQL server) on multiple machines.

Understanding ODBC DSN creation

Whenever we create an ODBC DSN for SQL Server, it creates a list of entries in the registry. For example, if we create ODBC DSN "CRCUE_ODBC," for the database "CRCUE" of the SQL Server "WINSQL2K" with trusted connection, the corresponding ODBC keys will be created in the registry.

In the registry under the following folder, it creates a list of key entries.



In this article, I am going to demonstrate how to create the same ODBC DSN in three separate machines: XYZ123, WINSQL2K and WINSYBASE2K

Step1: Create a text file c:\pclist.txt with a list of the client machine names.

Example:

XYZ123

WINSQL2K

WINSYBASE2K

Step 2: Open notepad, copy the code below and save it as c:\createsqlodbclist.vbs

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 = "CRCUE_ODBC"
DatabaseName = "Crcue"
DriverPath = "C:\WINNT\System32\sqlsrv32.dll"
LastUser="sa"
Server="WINSQL2k"
Trusted_connection="Yes"
Description="ODBC DSN for the Database:" & DatabaseName
DriverName="SQL Server"
InputFile="c:\pclist.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

Step3:

Double click the c:\createsqlodbclist.vbs file. It will create ODBC DSNs on all of the machines listed in the text file "pclist.txt." After it creates the ODBC DSN on every computer, it displays a message similar to those below.

Execute the above .VBC file by changing the values under 'Value assignment with a new SQL server name, database name for creating another ODBC DSN for a different SQL Server.

Note:

This article is based in the assumption that all related drivers have been installed on al ofl the client machines and also the login used for creating and running this VBScript have administrator access to all the machines.

Conclusion:

The above VBScript code can be used not only for creating ODBC DSN but also for creating any registry key entries on multiple machines.



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM