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

DSN-Less Connections

By Andy Warren,

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< /p>

 

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

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!

 

Total article views: 5045 | Views in the last 30 days: 5
 
Related Articles
FORUM

customize servers in connection dialog

customize servers in connection dialog

ARTICLE

Connection Strings 101

This article introduces connection strings and suggests using MDAC to easily write efficient connect...

BLOG

PowerPivot create relationships

We continue creating our PowerPivot model using Excel 2013 by adding relationships. We use two metho...

FORUM

Errorlog: The query notification dialog on conversation handle ....

Errorlog: The query notification dialog on conversation handle ....

FORUM

Creating the ODBC connection - permissions

SQL Server and all databases from it not listed when create ODBC connection

Tags
configuring    
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