Systems Engineering and RDBMS

Archive for September 24th, 2007

Universal Data Links

Posted by decipherinfosys on September 24, 2007

Universal Data Links or UDLs are a quick way to test out a connection string and prepare a connection string without typing in parameters.  Here is how you can go about creating a UDL and then saving it as a connection string that you can use in your applications.

1) Right click within any folder in Windows explorer and select New and then text document.

2) Name it as test.udl.  When it prompts you about whether you want to change the extension, click on Yes.

3) This will create a test.udl file for you.  Double click on the file and you will get this screen:

On the first tab, is the selection of the different OLEDB providers from which you can choose from.  For this post, we will select the Microsoft OLEDB provider for SQL Server.  One cal also choose SQL Native Client (SQL Server 2005), Oracle OLEDB Provider etc. depending upon the data source and the type of the application and the application needs.

4) Then click next.  You will come to the connection tab.

On this tab, you can specify the connection parameters for connectivity to the data source.  In the case of SQL Server when using the OLEDB Provider for SQL Server, you can choose between Windows and SQL Authentication and then choose your database as well.  For the sake of generating the connection string with the UDL, you can choose to store the password and can test your connection.  It is a quick and dirty way to test your connectivity as well to the data source.

5) On the next tab, you have some of the advanced settings like specifying a connection time out.

6) And on the last tab, one gets a listing of all the parameters for that connection string.  One can choose to specify additional attributes over here like the ApplicationName, the WorkStationID etc.  All this helps when troubleshooting where the connections are coming from since this information will then be reflected in the sysprocesses view on the server side.

Once you have tested the connection string and are happy with the settings, you can save it and then right click and open it with a notepad.   For the UDL created above, you will get this entry:

; Everything after this line is an OLE DB initstring
Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;User ID=sa;Initial Catalog=ReportServer;Data Source=(local)

As you can see above, the connection string is now available for you.  This prevents the need for typing out the connection strings and running into typing errors – in addition, this is a quick and dirty way of verifying your connections as well.

Posted in DB2 LUW, Oracle, SQL Server, Technology | 1 Comment »