Quick ways to test OLE DB Connection String

Question

For debugging purpose I'd like to know ways to test OLE DB connection string quickly.

I've found this free software, it works on my machine, tested successfully.

Is there a even quicker way to do so, maybe from command line on Windows? Because most of the time, it is the client rather than me that would do this task, so I prefer a "zero-installation" approach that would impact their system the least.

Answer 1

The following method has proven useful for me. It's super quick and practical and doesn't require PowerShell:

• Open up Notepad and create an empty text file, then click File -> click Save -> and save it with the File name: TestConnection.udl to your desktop.
• Go to your desktop and double-click on the TestConnection.udl file you just created and the Data Link Properties box will popup.
• Select the Provider tab and Find the provider that you want to connect with and click Next >>.
• Now from the Connection tab, select or enter your source/ server name -> then enter information to log on to server -> and select the database on the server.
• Click Test Connection and click OK to save the file. If errors occur during testing of your connection string, you will get a popup box with the error message.

---

Walkthrough (same as above but with images)

Open up Notepad and create an empty text file, then click File -> click Save -and save it with the File name: TestConnection.udl to your desktop:

Go to your desktop and double-click on the TestConnection.udl file you just created and the Data Link Properties box will popup.

Select the Provider tab and Find the provider that you want to connect with and click Next >>.

Now from the Connection tab, select or enter your source/ server name -> then enter information to log on to server -> and select the database on the server.

Click Test Connection and click OK to save the file. If errors occur during testing of your connection string, you will get a popup box with the error message.

Source

Answer 2

If the client has PowerShell installed (a given if they are running Windows 7 or Windows Server 2008 R2), then you can execute these commands from a PowerShell console window:

$conn = New-Object System.Data.OleDb.OleDbConnection
$conn.ConnectionString = "Provider=Search.CollatorDSO" # whatever you are testing
$conn.Open()
$conn.Close()

Answer 3

SQL Server Native Connection string testing in PowerShell

This method works in Powershell for testing an SQL Server Native connection string (the type that might work with a SQL Server database and be used in a web.config file). Note that there's no Provider=xxxx at the start of this connection string.

First: Start → Run → PowerShell

$conn = New-Object System.Data.SqlClient.SqlConnection
$conn.ConnectionString = "Data Source=(local)\SQLExpress;Initial Catalog=MyDatabase;Persist Security Info=True;User ID=MyDBUser;Password=IShouldBeUsingIntegratedSecurity!;MultipleActiveResultSets=True;User Instance=False"

# If no error occurs here, then connection was successful.
$conn.Open();
$conn.Close();

SqlConnection Class documentation.

Kudos to @Chuck Heatherly, on whose example this is based.

Note: I'm fully aware that this question is for OLEDB, however having come here looking for a means of doing this for a SQL Native connection, this might be of use to others who arrive here with the same goal.

Answer 4

There is one quick UI Method to Verify it

1. Create an empty file: empty.txt
2. rename the extension udl: empty.udl
3. Now just double-click that file, it will ask the server and user name password, you gonna know how to do it.

Refer: http://www.gotknowhow.com/articles/test-a-database-connection-string-using-notepad