by jvanassen at 2012-12-02 13:34:28
Hi Allby RichardSiddaway at 2012-12-03 07:12:42
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?
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.by jvanassen at 2012-12-03 11:44:20
As far as i am aware there aren’t any odbc specific cmdlets available
Thanks, please see link below:by jvanassen at 2012-12-03 12:46:05
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
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 compatibleby 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:by cookie.monster at 2012-12-07 09:31:50
A workaround–You can probably find a script to create ODBC DSNs on machines by making the appropriate registry entries.
Indeed, you can use the registry.by Infradeploy at 2012-12-09 12:01:48
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 <DSNName> <.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"
Save yourself a lot of hassle and use GPO preferences