THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

John Paul Cook

Building connection files by using a UDL file

Oops, that should be connection STRINGS in the title instead of files. I can’t fix the typo in the title because it would break links to this post. A UDL file brings up the Data Link Properties dialog box for testing a connection or building a connection string for you. It’s very handy.

image

All you need to do is create a UDL file. It’s so useful I have a UDL file on the desktop of all of my machines.

Start by creating a text file on your desktop or wherever you want.

image

Select the entire file name including the txt file type (this means that your machine must be configured to show the extension) and change the name and file type to test.udl or connection.udl or whatever you prefer.

Now you have a UDL file on your desktop:

image

Double-click the UDL file to bring up the dialog box. By default it appears as shown above. It isn’t limited to just SQL Server connection strings. It works for all of your OLE DB data providers as you can see on the provider tab. Notice that in the screen capture below, Oracle’s provider appears.

image

It gets even better! Once you’ve successfully tested your connection, click OK to save your connection string into the UDL file. Open the UDL file in a text editor and you’ll find your fully constructed connection string. For example:

[oledb]
; Everything after this line is an OLE DB initstring
Provider=SQLOLEDB.1;Persist Security Info=False;User ID=cookjp;Initial Catalog=SomeOtherDb;Data Source=SRV2

In this example, SQL Server authentication was used. Notice that for security purposes, the password was not persisted into the UDL file. If you check Allow saving password on the Connection tab of the Data Link Properties dialog box, it will save the password into the connection string in the UDL file after displaying this warning:

image

You can drag your UDL file to a Visual Studio project to get the connection string:

image

Published Monday, July 19, 2010 11:32 AM by John Paul Cook

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

WesBrown said:

Built a little utility a million years ago in vb.net that I could call and generate a UDL on the fly in my DTS packages. Fun stuff!

http://sqlserverio.com/2010/07/19/creating-udl-files-on-the-fly/

July 19, 2010 1:15 PM
 

Sudarshan.D said:

Hello...

In my computer udl file not create. i had many times did it. but not create udl file. kindly give me a solution to create the udl file

thanking you

Sudarshan.D

December 27, 2013 1:39 PM

Leave a Comment

(required) 
(required) 
Submit

About John Paul Cook

John Paul Cook is both a Registered Nurse and a Microsoft SQL Server MVP experienced in Microsoft SQL Server and Oracle database application design, development, and implementation. He has spoken at many conferences including Microsoft TechEd and the SQL PASS Summit. He has worked in oil and gas, financial, manufacturing, and healthcare industries. Experienced in systems integration and workflow analysis, John is passionate about combining his IT experience with his nursing background to solve difficult problems in healthcare. He sees opportunities in using business intelligence and Big Data to satisfy healthcare meaningful use requirements and improve patient outcomes. John graduated from Vanderbilt University with a Master of Science in Nursing Informatics and is an active member of the Sigma Theta Tau nursing honor society. Contributing author to SQL Server MVP Deep Dives and SQL Server MVP Deep Dives Volume 2.

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement