.net – Quick ways to test OLE DB Connection String

.net – Quick ways to test OLE DB Connection String

The following method has proven useful for me. Its super quick and practical and doesnt 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:

enter

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

enter

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

enter

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.

enter

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.

enter

Source

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()

.net – Quick ways to test OLE DB Connection String

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 theres 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: Im 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.

Leave a Reply

Your email address will not be published. Required fields are marked *