Solved

Need a fast way of switching between databases on two servers. (ODBC)

Posted on 2004-04-07
19
352 Views
Last Modified: 2007-12-19
We have two identical MS SQL data-bases that we need to switch between several times per day. (Test and development).

It is quite tedious to: Start the ODBC-administrator in the Control Panel. Find the Data Source. Click Configure. Change the Server Name. Click Next. Insert the Password. Click Next. Click Next. Click Finish. Click OK. Click OK.

There should be some kind of script available or similar...?

Clients: WIN 2000 PRO, WIN XP PRO. Servers: WIN 2000 Server. DataBase: SQL Server 2000.
0
Comment
Question by:KDK
  • 6
  • 4
  • 4
  • +2
19 Comments
 
LVL 34

Expert Comment

by:arbert
ID: 10774366
What uses the ODBC connection?  Can you configure DNSless connections?

0
 
LVL 34

Expert Comment

by:arbert
ID: 10774393
oops, of course I meant DSN not DNS...
0
 
LVL 13

Expert Comment

by:danblake
ID: 10774397
It's really hard to say, without knowing how you have setup your systems/applications you have available/are using...

There should be no reason why you cannot use enterprise manager to add both systems to your EM window or access directly via QA (You just need to use the Client Network Config utility to ensure that you have information for both systems added with aliases).
0
 

Author Comment

by:KDK
ID: 10774682
We have an application, installed locally on the PCs. This app. connects to a MS SQL Server DB using ODBC. I need to 'direct' the ODBC-driver to the first server on our network / the first data base - and a little later I need to change to the second server / the second DB and so forth...

In EM I have no problems, since I see all the servers and all the data bases in the window - and just select the right one.

To arbert: I cannot see how I can avoid DSNs. Due to a lot of reasons, we are forced to stick with ODBC...

What I'm looking for is a 'keyboard-typing-saving-feature', saving me the many Control Panel keystrokes to redirect between the servers/DBs.

Thanks.

/Hans
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 10774696
Please maintain this very old open question:
Question Title: Trying to install client 5.0.6a on Win XP: MSGINA-error
Author: KDK
Points: 125
Date: 09/02/2003 11:25AM CDT
http://www.experts-exchange.com/Applications/Email/Lotus_Notes/Q_20726689.html
0
 
LVL 13

Expert Comment

by:danblake
ID: 10774767
0
 
LVL 13

Expert Comment

by:danblake
ID: 10774814
It is possible to record this only key-strokes if very carefull...
0
 
LVL 22

Expert Comment

by:_TAD_
ID: 10775111


Why not just create a DOS batch file to edit the registry for you?

I found my ODBC entries here:

HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\FINPROD


Here's a quick snippet of code on how to delete items from the trash bin (using the registry):

I'll have to play around with the DOS a little bit, but I think you can really just create a batch program to toggle between Production and test.


@ECHO OFF
cls
Echo Clearing Recent and Favorites shortcut files...
del /q "%USERPROFILE%\Recent\*.*"        >NUL
del /q "%USERPROFILE%\Favorites\*.*"    >NUL
echo.
echo Creating Registry file...
echo REGEDIT4 >%USERPROFILE%\Remove.Reg

echo.
echo Adding remove keys for Explorer and Internet Explorer...
echo [-HKEY_CURRENT_USER\Software\Microsoft\Internet Explorer\TypedURLs] >>%USERPROFILE%\Remove.Reg
echo [-HKEY_CURRENT_USER\Software\Microsoft\Windows\CurrentVersion\Explorer\RunMRU] >>%USERPROFILE%\Remove.Reg

echo.
echo Adding "remove" key for Excel 2000 MRU list...
echo [-HKEY_CURRENT_USER\Software\Microsoft\Office\9.0\Excel\Recent Files] >>%USERPROFILE%\Remove.Reg

echo.
echo Adding "remove" key for Powerpoint 2000 MRU list...
echo [-HKEY_CURRENT_USER\Software\Microsoft\Office\9.0\PowerPoint\Recent File List] >>%USERPROFILE%\Remove.Reg

echo Now removing the registry keys...
regedit /S %USERPROFILE%\Remove.Reg

echo Now purging Deleted Files...
cd %systemdrive%\Recycler
del /q/s .\*.*
0
 
LVL 34

Expert Comment

by:arbert
ID: 10775649
Create 2DSNs, pass a parm to your program and tell it which one to use.  The only thing you have to change is the parm when you run.
0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 

Author Comment

by:KDK
ID: 10775654
_TAD_,

This is a good solution!

Will set-up a crash-and-burn machine after the Easter and try this out. Yes, you need a little trial-and-error work before the solution works - but this shou not be impossible.

danblake's solution is also possible - but personally I find replaying a series of keystrokes a little more shaky - and less suitable in this scenario. But the idea is not scrapped - will keep it as 'plan B'.

Thanks.

/Hans
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 10776185
>>Why not just create a DOS batch file to edit the registry for you?<<
With all due respect, but on a Production box?  I don't think so.
0
 

Author Comment

by:KDK
ID: 10776276
acperkins,

No! Definitely not for the production box! Just for us, developing and testing - we should be able to cope with a few batchfiles or registry-changes....

However, if anybody finds a better solution - I'm all ears... I agree, the method is not very 'clean'...

/Hans
0
 
LVL 34

Expert Comment

by:arbert
ID: 10777717
I agree with acperkins.  personally, whether it's a production box or not, I would kludge with the registry as little as possible.  This is usually a debug option/parm you set in your program to automatically choose the connection on the fly...
0
 
LVL 22

Expert Comment

by:_TAD_
ID: 10780344


How's this?  You'll need to change lines 8, 9, and 42

set DB1=<MyDb1Name>  <-- replace with your database names
set DB2=<MyDb2Name>

___x_MY_ODBC_NAME_x___   <---- replace with your ODBC name/alias



You may want to create a new ODBC connection for "testing" before screwing up your *good* ODBC connection.




<script --  ODBC.bat>

rem -- This program edits the registry and changes
rem -- the database for a specified ODBC entry

@ECHO OFF
cls

rem -- !!! --CHANGE THE NEXT TWO LINES-- !!!
set DB1=MyDb1Name
set DB2=MyDb2Name


rem ---------------------------------------------------------
rem ---------------------------------------------------------

:START
ECHO Press '1' to load DataBase1 and '2' for Database2

SET /P DB=input\

IF "%DB%" == "1" GOTO DB1
IF "%DB%" == "2" GOTO DB2

cls
ECHO Please Enter a Valid Number
GOTO START


rem ----------------------------------------------------------
:DB1
set DB=%DB1%
GOTO UPDATE


rem ----------------------------------------------------------
:DB2
set DB=%DB2%
GOTO UPDATE


rem ----------------------------------------------------------
:UPDATE
reg add HKLM\SOFTWARE\ODBC\ODBC.INI\___x_MY_ODBC_NAME_x___ /v Database /t REG_SZ /d %DB% /f
0
 
LVL 22

Expert Comment

by:_TAD_
ID: 10783555


okay, it's been like 10 hours and I just wouldn't be me if I didn't OVER explain something.  So I feel compelled to add a few comments and/or changes.

This line:

reg add HKLM\SOFTWARE\ODBC\ODBC.INI\___x_MY_ODBC_NAME_x___ /v Database /t REG_SZ /d %DB% /f

open up a dos prompt and type: help reg add /? and you will get a list of all the short names for the registry
HKLM -  HKEY_LOCAL_MACHINE
HKLU -  HKEY_LOCAL_USER
etc.

The path above is the path to the ODBC connections on my computer, yours may be different (but I doubt it).  

/v is the variable you want to change
/t is the variable type (you may want to open your registry and make sure that your database name is type REG_SZ
/d is the data or text value
/f is 'force overwrite' ... basically makes it a silent change instead of asking if you really want to do it or not.


Finally, a small change....

Change this line:

--FROM--
ECHO Press '1' to load DataBase1 and '2' for Database2

--TO--
ECHO Press '1' to load %DB1% '2' for %DB2%
0
 
LVL 22

Accepted Solution

by:
_TAD_ earned 150 total points
ID: 10783565
rem -- This program edits the registry and changes
rem -- the database for a specified ODBC entry

@ECHO OFF
cls

rem -- !!! --CHANGE THE NEXT TWO LINES-- !!!
set DB1=MyDb1Name
set DB2=MyDb2Name


rem ---------------------------------------------------------
rem ---------------------------------------------------------

:START
ECHO Press '1' to load %DB1% and '2' for %DB2%

SET /P DB=input\

IF "%DB%" == "1" GOTO DB1
IF "%DB%" == "2" GOTO DB2

cls
ECHO Please Enter a Valid Number
GOTO START


rem ----------------------------------------------------------
:DB1
set DB=%DB1%
GOTO UPDATE


rem ----------------------------------------------------------
:DB2
set DB=%DB2%
GOTO UPDATE


rem ----------------------------------------------------------
:UPDATE
reg add HKLM\SOFTWARE\ODBC\ODBC.INI\___x_MY_ODBC_NAME_x___ /v Database /t REG_SZ /d %DB% /f
0
 
LVL 22

Expert Comment

by:_TAD_
ID: 10783646


and for all those who feel this should not be done on a production box....

eh... I've done far worse.  I agree that messing around with the registry is not for the timid or faint of heart.

But a small change like a database name is trivial.  As long as the program is done properly, and idiot-proof (which I think I've done) then I see no real harm in putting something like this on a production box.  Although, there is that caviot.. why would anyone switch between databases on production?



lastly, a suggestion for improvement.

KDK> if you have control of the source code for this app, might I suggest on startup that you query all of you DSNs and allow the user logging in to choose which DSN they would like?

To avoid gaining a huge list, you create a naming convention (like we have for PeopleSoft) and then only get the ODBC connections that look like 'psft_?' (for my example)

psft_p     //production
psft_t     //test
psft_d    //development

For all of you end users, they will only have one ODBC installed on their machine.  Your testers and developers on the other hand will have several ODBC connections to choose from.

Just a thought....


HAPPY EASTER!
0
 

Author Comment

by:KDK
ID: 10887797
Hi,

Long time no hear - I'm sorry about that, but Easter, holidays etc....

It's all working now, thank you _TAD_!

I just made 2 batfiles, containing the essence of _TAD_'s scripts with a few mods:
reg add HKCU\SOFTWARE\ODBC\ODBC.INI\paragen /v Server /t REG_SZ /d name_of_the_server /f

I found it more convenient to create 2 batfiles - one for each database - and place them in front of the application, ending up with two icons on the desktop, each changing the reg. value as needed - and then starting the app.

Yes, you change the reg. value each time the app. starts, but this only takes a split second.

Thank you!

/Hans
0
 
LVL 22

Expert Comment

by:_TAD_
ID: 10887927


great to hear!

thanks for the points!
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Save sql query result in sql server 15 24
Sort by Month and Year - SQL 3 23
Change part of a string 2 23
Stored procedure 23 9
I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now