March 22, 2004 at 3:40 pm
Ok, here's my situation: I have SQL 2000 running on 1 server right now, and will be moving to a faster server. All of the different programs that I have access a named instance of [server]\[instance]. Is there anything I can do to move the access to the new server without having to go through every piece of code and change the connection string? Should I have set this up differently in the first place? Any ideas/suggestions are welcome. Also, stories of how others have conquered this same issue are quite welcome.
Thanks
March 22, 2004 at 4:46 pm
If your application was written correctly in the first place then you would only have to make the change ONCE in yolur application!
One correct way of doing this properly the first time is to create a module with connection string in it and make it Public, then you could just call that module anywhere in your application!
By the sounds of it, you have done it this way or a similar way and you therefore have the connection string whenever you are making the connection to the Database.
So......you will have to make the change everywhere in your application!
March 22, 2004 at 11:11 pm
It would have even been better if you had added a registry setting or a INI file in your program that contains the Servername and location. It's never wise to hardcode servernames and such. In that way you can also use different DTAP (Development/Test/Acceptance/Production) environments. Since this apparently is not the case you will have to browse through your code. Depending on the langauge you are programming there are varous "FindAndReplace" AddIn downloadable from the internet. They will simplify your job a lot.
An example for VB5 an 6: http://www.vb2themax.com/Downloads/Files/AdvFindReplace.zip
March 23, 2004 at 12:25 am
Is your "old" server obsolete ?
if so : clone your "old" server to your new one, so you can avoid the naming problems.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
March 23, 2004 at 7:58 am
We've done extensive windows based db development with a mixed enviornment of SQL Server db's and odbc data sources. We've used multiple methods of dealing with connections/connection strings and by far the easiest way to manage them has been with UDL files. We use ADO connections hooked up to a UDL file stored in a public directory on the network. Any server name changes then can quickly be made in the UDL file and all applications that use the UDL file will access the server/new server specified in the UDL file. Again, this works well for SQL Server but it works just as well for ODBC data sources as well. In February a client we service just converted their entire network to their parent company WAN with completely different naming conventions. In a matter of minutes, all the applications were using the correct DB servers on the new network. As an aside, we also have an ancestor object that is called on application creation to determine the location of the UDL file on the network or the local server (and ultimately prompts the user if it can't find it) . This solves the problem of the entire network changing including Database servers and application/file/print servers.
March 24, 2004 at 4:57 am
This VB code displays the Windows dialog that allows the user to select a server (the same as when you double-click on a .udl file) :
Dim l_objDataLink As MSDASC.DataLinks
Dim l_conConnection As adodb.Connection
Set l_objDataLink = New MSDASC.DataLinks
l_objDataLink.hwnd = WindowHandle ' Sets the parent window
Set l_conConnection = l_objDataLink.PromptNew
You then can store the returned connection (string) in the registry or a .ini file as suggested already.
March 24, 2004 at 7:45 am
If you're using ODBC to connect to the datasource in your code, you could create a script to go in and alter the registry and update the server in the DSN for you. However, if you're using something like...
myConn.ConnectionString = "Provider=SQLOLEDB;Data Source=SERVERNAME;Initial Catalog=pubs;Trusted_Connection=Yes;"
you'll obviously have to change your code as suggested by others.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply