Vista Tutorial - change odbc

I recently moved some of my SQL databases to my new sql2005 box. Now I'm
looking for a way to send a script to people that will change there ODBC
connection to the new server.

any way to script this?


Thanks.

The ODBC settings are stored in the registry. You can export the keys to
a reg file.


Under:
HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources , you'll find
a key (name of dsn) for each SQL connection.

Under:
HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI you'll see a key with sub values
corresponds to the value in the above key.


Export the two, open the reg files in notepad and make the neccessary changes
if needed. I recommend merging
the files into one reg file, then ask your users to import (double-click)
it on the destination server(s).


-----
Shay Levi
$cript Fanatic
http://scriptolog.blogspot.com

I should have mention that DSNs under HKEY_LOCAL_MACHINE are system DSNs,
under the same path in HKEY_LOCAL_USER you can find
user DSNs, if any.

-----
Shay Levi
$cript Fanatic
http://scriptolog.blogspot.com

Powershell includes a psDrive that makes the registry look like a file
system, so this is pretty easy to do. The following two-liner works:

$def = Get-childItem -path "hklm:\software\odbc\odbc.ini" -include
"mydatasourcename" -recurse
set-itemproperty -path $def.PsPath -name "Server" -value "myNewServerName"

Explanation:

The first line uses the HKLM: PSDrive to access the registry. I couldn't
figure out a more direct way to get the exact key that is needed,so I had to
specify a partial path (out to ODBC.Ini) and then use -include to specify the
datasource name, and -recurse was required too. I'm sure I'm missing a
better way, but this works. The result of this command is that $def
contains a reference to a registry object that describes that registry entry.
(Or $null, if the specified value doesn't exist.)

The second line uses the set-itemproperty cmdlet to set the property named
"Server" to the value "myNewServerName".

Change "myDataSourceName" and"myNewServerName" to whatever you are using.

HTH,

Leo




"AHartman" wrote:

What if you only know the server name... they could use anything for a
datasource name.


"Leo Tohill" <LeoTohill@xxxxxx> wrote in message
news:1748AB4C-022F-482A-9135-9867F6D374CB@xxxxxx

If that's the case, you'd need to have the user input the dsn. Depending
upon how elaborate you want to be, you could simply use "read-host" to get an
entered value, or you could query and list them all, and have the user
select.


"AHartman" wrote:

Silly me, here's the way to write that first statement (use get-item instead
of get-childitem)

$def = Get-childItem -path "hklm:\software\odbc\odbc.ini\myDataSourceName"

Including your requirement to get the data source name from the user, you
could do this:


$def = $null
while (!$def)
{
$dsn = $null
while (!$dsn)
{
$dsn = read-host -prompt "Enter the data source name or ctrl-c to quit"
}
$def = Get-Item -path "hklm:\software\odbc\odbc.ini\$dsn" -erroraction
silentlycontinue
if (!$def)
{
out-host -input "There is no system data source named `"$dsn`""
}
}
set-itemproperty -path $def.PsPath -name "Server" -value "myNewServerName"




"Leo Tohill" wrote:

cool... thanks!


"Leo Tohill" <LeoTohill@xxxxxx> wrote in message
news:6F6B5506-7B2F-4A65-998B-56C63ACFFD3B@xxxxxx