I've gathered a few more interesting bits of information about DSN's that I'd like to share with you this week:
- From Swynk reader Rod Burke: Basically, if you set the system dsn (not file dsn, and I haven't tried user dsn) to be non-trusted, this property is ignored when the DSN is used later. Seems to apply only to applications which pop up a login dialog ... i.e. not those which supply a username/password in the connection string or similar. If you expect Access, for example, to present a dialog for username password on a non-trusted connection to SQL Server, it will first complain about the trusted connection attempt failing. It's documented at http://support.microsoft.com/support/kb/articles/Q279/5/26.asp. (Added Feb 15, 2001)
- From Swynk reader and good friend Matt Owen: You might want to consider specifying a complete path to your file DSN's. By providing the path you don't have to check that someone has changed the default folder. You can just use the application folder where your executable is stored, or keep your DSN's in a file share with a fully qualified UNC.
- Simon Clark wrote to mention that using a DSN or UDL could possibly degrade performance if you open and close a connection a lot - due to the disk/registry reads involved to get the connection info. I can see in some situations that it would make sense to cache the connection information to save hitting the disk over and over, a perfect use for a dsnless connection where you load the connect string at startup and use it for the entire session. Overall I still prefer UDL's as they are more easily maintained.
- Did you know that you can have a File DSN that points to a System DSN? Why would you want to? As best I can tell from researching MSDN this was a fix for MS Query, which in it's earlier versions could only see File DSN's. It's pretty simple, just one line: [ODBC]DSN=SystemDSNName. PLEASE try not to find a use for this one!
- That the version of MS Query shipped as part of Office 2000 supports DSN's, but I can't find a way to get it to use a UDL or a connection string? It does support OLAP cubes, so I guess it's not all bad!
- I've posted an update to my DSNGen utility. Allan Mitchell reported the first (and only so far!) bug - while creating a file DSN, if the server name was "(local)" and the user selected the ServerName as Prefix option, the resulting DSN would not show up in the ODBC applet (the DSN would be created as (LOCAL)_dbname.DSN). If you haven't done so already, take a look at it.
If you're new to DSN's, take a look at my previous articles DSN's - What are they?, DSN, DSN-Less, or UDL?, and DSNGen - Another Tool for the Toolbox. If you've got another tip or comment about DSN's or UDL's, please email me.