3305 Main Street Ste 019 Vancouver, WA 98663

How to Test SQL Server ODBC Connectivity

Background
Applications such as Liberty Reports and Event 1 Integrator utilize the Microsoft SQL Server ODBC Driver to query data from SQL Server databases. This article provides a set of steps that can be used to confirm whether the SQL Server ODBC Driver is functioning as expected using tools that are available as part of Microsoft Office. If the conclusion of this test reveals that data cannot be queried, you should consult with your I.T. support representative or database administrator to determine the cause and resolution.

Summary
This test will consist of executing a query that displays data from your database.

Steps

  1. Create an ODBC Data Source that points to your SQL Server database by using the steps identified in the following article:
    https://www.event1software.com/kb-microsoft-products/how-to-setup-a-microsoft-sql-server-odbc-data-source/
  2. Open Windows File Explorer and then navigate to the folder where your Microsoft Office executable files are located. Click here for information about how to determine the correct folder path.
  3. Locate the MSQRY32.EXE file and double-click it to launch it.
  4. The Microsoft Query window will appear. Click the File menu and then click New. The Choose Data Source window will appear.
  5. Scroll down the list and select your data source (named in step 1).
  6. Uncheck the box entitled Use the Query Wizard to create/edit queries.
  7. Click OK. Enter your login credentials for SQL Server (if prompted).
  8. In the Add Tables windows, select a table from the list. Look for a table name that you recognize as containing data that you expect to see.
  9. Click Close to dismiss the Add Tables window.
  10. In the list of columns shown, double click on a set of columns.

After performing the above steps, you should see a list of records. If an error occurred during this process, it is possible that your SQL Server permissions are preventing you from accessing the data, or the SQL Server ODBC Driver is not functioning correctly in your environment. In either case, your I.T. consultant or your database administrator can assist you with resolving the issue.

To perform this test using the SQL statement from another query, click the SQL button on the toolbar and replace the text that is displayed in the edit window with the text from the other query. Upon clicking OK the query will be executed.