DSN, DSN-Less, or UDL

In a previous article (DSN)
I discussed the basics of DSN’s and the different types that you can use. In
this article I’d like to continue that discussion by looking at two relatives of
the DSN, the DSN-less connection and the Universal Data Link (UDL). 

DSN-less means that you don’t use a DSN to store
your connection information. Instead, you either prompt your user for it at run
time (Query Analyzer is a good example) or you store the equivalent information
somewhere else; the registry, an INI file, or even a table. If you truly need to
provide a dynamic connection where the user will be connecting to a variety of
different sources, DSN-less is definitely easier on the user. Many developers
decide to use a DSN-less connection and just store the connection string in the
registry, eliminating the need to make sure that a valid DSN for the application
exists. Using a DSN-less connection is not good or bad, just a decision you need
to make when designing your app. I will say that most help desk folks will be
much more familiar with DSN’s.

This is the Query Analyzer login dialog.
DSN-Less!


Just to give you a feel for the difference, here
is how you connect to Pubs using both a DSN and a DSN-Less connection:

‘using
a DSN

cn.open
"DSN=Pubs"

 

‘dsnless

cn.open
"Server=Andy;Database=Master;Trusted_Connection=Yes"

OLEDB brought another connection option with it,
the UDL. UDL’s are created with the Data Link Properties dialog (shown below).
UDL’s are an evolutionary step from DSN’s. They support creating both ODBC and
OLEDB connections, plus the dialog displays all of the properties that each
particular driver (provider) supports. Because the underlying format of the UDL
is pure ascii, it’s easy to create one using standard text operations if needed.
Even Notepad would work! I like the fact that all of the connection string
options are easy to work with (app name, time out, etc). 

This is an example of what the text of a UDL file
looks like. To view it, just create one, then rename the file with a .txt
extension and open it. You can
see that this is a one line connection string with a short header:

[oledb]

;
Everything after this line is an OLE DB initstring

Provider=SQLOLEDB.1;Integrated
Security=SSPI;Persist Security Info=False;Initial Catalog=Northwind;Data
Source=ANDY

 

You use a UDL almost exactly like a DSN:

cn.open "File
Name=C:\Program Files\Common Files\System\OLE DB\Data Links\Test.UDL"

I found several references on MSDN that mentioned
that the default location for UDL files is C:\Program Files\Common
Files\System\OLE DB\Data Links, but I was unable to find a registry key that
would allow this to be configured. Putting your UDL’s there would have the
effect of making them "system" UDL’s. A better approach might be to
place them somewhere in the users profile under Documents & Settings.

I
found quite a few useful articles while researching. This one has a reg file you can
download that gives you the ability to right click on your desktop to create a
new UDL:
http://support.microsoft.com/support/kb/articles/Q244/6/59.ASP

Right
here on Swynk Danny Lesandrini has two great articles: 

ODBC
DSN-Less Connection Tutorial
 

Problematic
or Programmatic ODBC?

 

SQL
Magazine also had a couple articles recently that explore the UDL and Data Link
API in more detail than I do here:

http://www.sqlmag.com/Articles/Index.cfm?ArticleID=15629&Key=OLE%20DB

http://www.sqlmag.com/Articles/Index.cfm?ArticleID=9640&Key=OLE%20DB

 

I
think UDL’s and OLE DB are the way to go, but I suspect we’ll be dealing with
DSN’s for a while yet. As always, I look forward to your comments and questions!

Latest Articles