Smart questions
Smart answers
Smart people
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Member Login




Remember Me
Forgot Password?
Join Us!

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips now!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

Join Tek-Tips
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.
Jobs from Indeed

Link To This Forum!

Partner Button
Add Stickiness To Your Site By Linking To This Professionally Managed Technical Forum.
Just copy and paste the
code below into your site.

create odbc connection to sql server using vbscript

emmanuelbeaudet (Programmer) (OP)
21 May 04 10:45
Hi,
I tried this script that I found on the Net. It create odbc connection to sql server for each machine with the name in a text file. It works fine but its only created the odbc connection on the registry. If I look in the ODBC source in the control panet I can't see the connection, if I look in the registry I see it. if I create a new connection with the same name got message that the connection allready exist. How can I use this connection without seeing it in the odbc source ?

Thank you

Emmanuel


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 = "inventory_odbc"
DatabaseName = "inventory"
DriverPath = "C:\WINNT\System32\sqlsrv32.dll"
LastUser="sa"
Server="mfortin"
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
PHV (MIS)
21 May 04 17:11
Have you browsed ALL the tabs in the ODBC source manager form ?

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

emmanuelbeaudet (Programmer) (OP)
25 May 04 8:49
Yes and I didn't see the connection.
wcburton (IS/IT--Management)
27 May 04 17:17
Any progress with this problem? I am thinking I may need to do the same soon and would like to know if there is an answer in case it happens to me.

CODE


    '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
One thing I did see is that this line where you write the single value to 'ODBC Data Sources' did not paste well. It looks like the entire line is a comment and then wrapped when you pasted it. When you verified that the registry was written, did you just look for the 'databasename' key and the values in that key or did you verify that the 'databasename' value was written in the 'ODBC Data Sources' key?

Were all the values written as plain strings (REG_SZ) or did they maybe get written as multi or expanded strings?
desihuduga (Programmer)
28 May 04 12:56
I am doing a similar thing, but using vb.net. it is working fine for me.

----

Dim rk As Microsoft.Win32.RegistryKey

rk = Microsoft.Win32.Registry.LocalMachine.CreateSubKey("SOFTWARE\ODBC\ODBC.INI\ODBCNAME")
rk.SetValue("Server", "SERVERNAME")
rk.SetValue("Trusted_Connection", "Yes")
rk.SetValue("Driver", "c:\winnt\system32\\SQLSRV32.dll")
rk.SetValue("Description", "ODBCNAME")
rk.SetValue("Database", "DATABASENAME")

rk = Microsoft.Win32.Registry.LocalMachine.GetValue("SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources")
rk.SetValue(""ODBCNAME", "SQL Server")

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Back To Forum

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close