change system SQL database connection

In windows you can create a database connection for your front end program to use.  Well as with all things in life things change, you decide to get a new database server, you messed up on the initial imaging of the workstations, you decided to expand or consolidate your database servers.  Instead of having to go around to each desktop and dig through control panel on each one to edit the database connection, here's a script to change the server.

This does not create a new database connection on each computer, for that you'll probably want to do configuration through control panel on one of the computers and then export it out the HKLM\SOFTWARE\Wow6432Node\ODBC\ODBC.INI or HKLM\SOFTWARE\ODBC\ODBC.INI keys to a file.

If your database is something other then CMS you'll need to change the key path referenced below.




@echo off
if "%1" == "" goto needarguments
for /f "tokens=2" %%W in ('date /t') do set dater=%%W
for /f "tokens=1-3 delims=/" %%d in ('echo %dater%') do set curdate=%%f%%d%%e
rem what we're changing the CMS database server to.
set dsnserver=newprod



:nextcomp
set remotecomp=%1
rem need to do some ping checks and stuff
echo updating %1 at %time%


for /f "tokens=2 skip=1 delims==" %%q in ('ping -n 1 %1') do set pingtimer=%%q
for /f "tokens=1 delims=m" %%s in ('echo %pingtimer%') do set pingtime1=%%s


if "%pingtime1%" == "1 Received " set pingtime1=down
if "%pingtime1%" == "down" echo %1 down at %date% %time% >> pc-down-dsn-%curdate%.lst
if "%pingtime1%" == "down" goto thiscompfinished



rem this works only if changing the server, if no dsn/odbc connection is
rem yet set up, you'll need to add more registry keys then just this one.
rem echo on
if exist \\%1\c$\windows\syswow64 reg add "\\%1\HKLM\SOFTWARE\Wow6432Node\ODBC\ODBC.INI\CMS" /v Server /t reg_sz /d %dsnserver% /f
if not exist \\%1\c$\windows\syswow64 reg add "\\%1\HKLM\SOFTWARE\ODBC\ODBC.INI\CMS" /v Server /t reg_sz /d %dsnserver% /f
if errorlevel 1 echo %1 had error updating dsn on %date% %time% >> pc-error-dsn-%curdate%.lst
if errorlevel 0 echo %1 updated dsn to %dsnserver% on %date% %time% >> pc-fixed-dsn-%curdate%.lst
echo off


:thiscompfinished
shift
if not "%1" == "" goto nextcomp
echo finished at %date% %time%
goto end

:needarguments
echo usage: %0 comp1 comp2 comp3 etc...
echo make sure to not use any \\







:end