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
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:
; 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:
SQL Magazine also had a couple articles recently that explore the UDL and Data Link API in more detail than I do here:
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!