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:

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:

You use a UDL almost exactly like a DSN:

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

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!