Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Introduction to DSN's

By Andy Warren,

DSN is an acronym for Data Source Name. It's a simple and standard way to describe how to connect to a data source using an ODBC driver. More importantly, using a DSN means you can change the location of your data by updating the DSN, no update to your application required. Keep in mind that DSN's only describe ODBC connections, not OLEDB connections.

There are three types of DSN's; system, user, and file. System and user DSN's are registry based, while File DSN's are stored in the file system. You normally create and administer DSN's through the ODBC Data Source Administrator, found in Control Panel on NT/9x machines, or in Administrative Tools for Win2K machines. However, there is nothing magical about editing DSN's. You can use regedit to view/modify user or system DSN's, and notepad works fine for file DSN's. 

ODBC Data Source Administrator

Most information about DSN's is stored in the registry. HKLM\Software\ODBC\ODBCINST.INI contains a list of all ODBC drivers installed on your machine. HKLM\Software\ODBC\ODBC.INI contains all of your system DSN's stored as separate subkeys. ODBC.INI has two other subkeys, ODBC Data Sources that contains a list of all your system DSN's, and ODBC FileDSN, which contains the name of the folder where File DSN's are stored (C:\Program Files\Common Files\ODBC\Data Sources is the default). User DSN's are stored in Hkey_Current_User\Software\ODBC, but it has only an ODBC.INI key. No Odbcinst.ini key is needed, as installed drivers are available to all users and so are stored in Hkey_LocalMachine.

Choosing which type of DSN to use depends on how your application works. In almost all cases I've 
found that a system DSN works well since any user using my app on that machine will still be connecting to the same datasource. It also makes installation a snap, since the application can be installed for 'All Users' and the DSN will be accessible to anyone logging in to that machine. If you use a user DSN, then you'll have to do some extra work to make sure the DSN gets created when a user new to that machine logs in for the first time.

To a developer, all DSN's are not quite equal. Here is how a developer would open a connection to the Pubs database using each type of DSN:

DSN

User cn.Open "Provider=MSDASQL;DSN=Pubs"
System cn.Open "Provider=MSDASQL;DSN=Pubs"
File cn.Open "Provider=MSDASQL;FILEDSN=Pubs"

As you can see, there is no difference in the syntax required to use either a user or a system DSN - in fact, some quick testing with ADO 2.6 reveals that if you have a system and user DSN of the same name, it will choose the user DSN. Another interesting point is that you can actually have a system, user, and file DSN all with the same name. 

One thing you want to consider is how will you change the DSN on 500 workstations if you move your data? If you use a user/system DSN, you can create .reg file and apply it in the login script (regedit /s filename.reg) or perhaps just email the .reg file to the users with instructions to apply the change (good luck!). 

If you're using a File DSN, you can just copy the new DSN over the old one during the login script or again, email the updated file to your users. Another alternative for file DSN's is to change the default folder for file DSN's to point to a share available to all users. Then you can update the DSN at any time and the change is instantly available to your users. If you choose this method, please set the security on the folder to read only!

Additional reading:
http://msdn.microsoft.com/library/periodic/period99/ntp9951.htm
http://msdn.microsoft.com/library/default.asp?URL=/library/psdk/dasdk/odch9hv7.htm

Total article views: 7841 | Views in the last 30 days: 8
 
Related Articles
FORUM

Which Stored Procedure changed

Which Stored Procedure changed

FORUM

Machine Name Change

If machine name changes will database keep working

ARTICLE

Creating a System Stored Procedure

Creating a system stored procedure isn't hard. You're always cautioned from making changes to the sy...

FORUM

System Stored Prodecures

New Proc Created in Master under System Stored Procedures

FORUM

System Stored Procedures referring to which tables.??

System Stored Procedures Tables?

Tags
configuring    
miscellaneous    
programming    
sql server 6.5    
sql server 7    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones