Setting up ODBC Connections

This topic contains 0 replies, has 1 voice, and was last updated by Profile photo of Forums Archives Forums Archives 5 years, 5 months ago.

  • Author
    Posts
  • #6371

    by jvanassen at 2012-12-02 13:34:28

    Hi All

    Just found this forum and decided to sign up as I want to make Powershell my next venture of study.

    I recently got myself into my first IT job and have been working there for 6 months now. I've had exposure to many different areas SQL, Exchange, AD, vSphere etc. I've learnt the basics to each one through videos and reading and was going to pick one and learn it in-depth and get certified init however then my boss told me about Powershell. Iv watched some of Don's videos onit as well as had a play around with it in a Server 2008 VM and decided to set my spare time for the next couple of months to learning as much as I can in Powershell and getting confident init.

    One of the things i have to occasionally do at work is when a new database is created on a SQL Server is go and setup ODBC connections on client PCs which connect and read from this database. At the moment i have to go into the Data Sources and set this up manually on around 15 PC's and im doing this around 2 times in say 2 – 4 weeks. As you can imagine its annoying and takes a while. My boss is really the guy that implements any scripts we have and he has more important things on his plate than creating a powershell script for this when i can just go down and spend 30 mins doing it. However i want to surprise him and get this sorted so i have decided this has got to be the first script i create lol.

    I've learnt the basics of powershell through a 4 hour classroom video i found on YouTube from Don (best presented study video ive ever watched, easy to understand) However i now want to get cracking with my ODBC script and im not sure where to start if im honest, I cant find any examples on the web.

    I've looked through the commands on Microsoft (http://technet.microsoft.com/en-us/libr ... 19(v=vs.85).aspx)

    However none of these commands seem to be working and also if i do "get-help *odbc*" it doesnt show me that any odbc commands exist. Ive updated to version 3 but it doesnt seem to recognise anything to do with odbc.

    Please help, what am i missing here?

    by RichardSiddaway at 2012-12-03 07:12:42

    Your link has been truncated so I can't follow it so see the commands to which you refer. if you can re-post the full link we can take a look at it for you.

    As far as i am aware there aren't any odbc specific cmdlets available

    by jvanassen at 2012-12-03 11:44:20

    Thanks, please see link below:

    http://technet.microsoft.com/en-us/libr ... 19(v=vs.85).aspx

    Youl need to copy paste it, it doesn't seem to want to hyperlink the full address

    by jvanassen at 2012-12-03 12:46:05

    Just been reading some more bits n by the sounds of it odbc cmdlets have been added to windows 8 and server 2012 but are not backwards compatible

    by cmille19 at 2012-12-04 11:32:43

    I followed your link and you are correct it looks like those cmdlets are only available on Windows 8 or Server 2012:

    A workaround–You can probably find a script to create ODBC DSNs on machines by making the appropriate registry entries.

    by cookie.monster at 2012-12-07 09:31:50

    Indeed, you can use the registry.

    I personally ended up using VBscript so that this could run on XP systems without PowerShell. This might help get you looking in the right spots for the registry to create a PowerShell script. Don't recall where I found it.

    '=======
    ' COMMENT: Create a DSN for a MS Access database through ODBC
    ' C:\Windows\SysWow64\wscript.exe "CreateDSN.vbs < .MDB Path> "
    '
    '=======

    Dim DataSourceName, DatabaseName, Description, DriverPath, DriverName
    Dim fso, SysFolder, SysFolderPath
    Const SystemFolder= 1

    Set fso = wscript.CreateObject("Scripting.FileSystemObject")
    Set SysFolder = fso.GetSpecialFolder(SystemFolder)
    SysFolderPath= SysFolder.Path

    'Read in arguments
    Set oArg = wscript.Arguments
    if oArg.Count = 2 then
    DataSourceName = Wscript.Arguments.Item(0)
    DatabaseName = Wscript.Arguments.Item(1)
    Else
    wscript.echo "Usage: C:\Windows\SysWOW64\cscript.exe NewSystemDSN.vbs DataSourceName DatabaseLocation"
    end if

    'Specify the DSN parameters
    wscript.echo "Datasource = " & DataSourceName
    wscript.echo "DatabaseName = " & DatabaseName
    Description = "DSN for DB " & DatabaseName
    DriverPath = SysFolderPath & "\odbcjt32.dll"
    DriverName = "Microsoft Access Driver (*.mdb)"

    'Creating registry writer object
    Set WshShell = WScript.CreateObject("WScript.Shell")

    'Adding to registry the DSN key
    Dim RegEdPath
    RegEdPath_1 = "HKLM\SOFTWARE\ODBC\ODBC.INI\" & DataSourceName & "\"
    RegEdPath_2 = "HKLM\SOFTWARE\ODBC\ODBC.INI\" & DataSourceName & "\Engines\"
    RegEdPath_3 = "HKLM\SOFTWARE\ODBC\ODBC.INI\" & DataSourceName & "\Engines\Jet\"
    WshShell.RegWrite RegEdPath_1, ""
    WshShell.RegWrite RegEdPath_2, ""
    WshShell.RegWrite RegEdPath_3, ""

    'Filling coorresponding DSN
    WshShell.RegWrite RegEdPath_1 & "DBQ", DatabaseName, "REG_SZ"
    WshShell.RegWrite RegEdPath_1 & "Description", Description, "REG_SZ"
    WshShell.RegWrite RegEdPath_1 & "Driver", DriverPath, "REG_SZ"
    WshShell.RegWrite RegEdPath_1 & "DriverId", "25", "REG_DWORD"
    WshShell.RegWrite RegEdPath_1 & "FIL", "MS Access;", "REG_SZ"
    WshShell.RegWrite RegEdPath_1 & "SafeTransactions", "0", "REG_DWORD"
    WshShell.RegWrite RegEdPath_1 & "UID", "", "REG_SZ"
    WshShell.RegWrite RegEdPath_3 & "ImplicitCommitSync", "", "REG_SZ"
    WshShell.RegWrite RegEdPath_3 & "MaxBufferSize", "2048", "REG_DWORD"
    WshShell.RegWrite RegEdPath_3 & "PageTimeout", "5", "REG_DWORD"
    WshShell.RegWrite RegEdPath_3 & "Threads", "3", "REG_DWORD"
    WshShell.RegWrite RegEdPath_3 & "UserCommitSync", "Yes", "REG_SZ"

    ' WshShell.RegRead

    ' WshShell.RegDelete

    'Write in "ODBC Data Sources" Key to allow ODBC Manager list & manage the new DSN
    WshShell.RegWrite "HKLM\SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources\" & DataSourceName , DriverName

    wscript.Quit

    Once I have this file saved, I run a bat file depending on if it's XP or 7 (I need 32-bit ODBC DSNs on 7):
    XP: cscript.exe "\\path\to\script.vbs" DataSourceName "H:\path\to\DatabaseLocation.mdb"
    7: C:\windows\syswow64\cscript.exe "\\path\to\script.vbs" DataSourceName "H:\path\to\DatabaseLocation.mdb"

    by Infradeploy at 2012-12-09 12:01:48

    Save yourself a lot of hassle and use GPO preferences

You must be logged in to reply to this topic.