When having any issue fetching data from Data Source to Qlik Products(QlikView or Qlik Sense) via ODBC DSN, it is crucial to determine whether it is general or Qlik specific.
In order to investigate, the best way is to establish connection and fetch data from a 3rd party tool. Chances are:
- If a 3rd party tool cannot fetch data either, or gets the same result as Qlik Products, then the issue falls outside of Qlik Products.
- If a 3rd party tool can fetch data but Qlik Products cannot, or gets different result, then the issue falls inside Qlik Products.
Note this article is for testing ODBC connection only. For testing OLE DB Connection String, please refer to article How to externally test an OLEDB Connection String using Windows PowerShell
First of all, DSN must be already created and tested.
It is possible to use a tool called "DTM ODBC SQL runner" to do the test. Steps:
- Download the zip file from this page:http://www.sqledit.com/odbc/runner.html
- Unzip the file to a folder.
- Edit "example.ini" to include the following information:
- DSN: The DSN to be tested
- User: Username of Data Source
- Password: Password of Data Source
- Edit source.sql to include the SQL statement that is intended to be tested.
- Run the following command:
- 64-bit OS: sqlrun_x64.exe -Iexample.ini
- 32-bit OS: sqlrun.exe -Iexample.ini
Verify if the connection was established successfully.
- Open "out.txt" and verify the result.
It is also possible to do the test using Microsoft Excel. Steps:
- Create a TXT file includes 4 lines:
DSN=[DSN name];User Id=[Username];Password=[Password];
SELECT * FROM [tablename]
Rename this file from .TXT to .DQY file.
Double-click the .DQY file. Excel will show a Security Notice. Click "Enable".
Input the requested information(depends on the actual ODBC Driver)
Data will be fetched to Excel.