Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW

How to externally test an OLEDB Connection String using Windows PowerShell

No ratings
cancel
Showing results for 
Search instead for 
Did you mean: 
Andre_Sostizzo
Digital Support
Digital Support

How to externally test an OLEDB Connection String using Windows PowerShell

Last Update:

May 14, 2021 10:25:09 AM

Updated By:

Andre_Sostizzo

Created date:

Apr 10, 2015 7:32:55 PM

Attachments

When having any issue fetching data from Data Source to QlikView or QlikSense using a given Connection String, it is crucial to determine whether it is QlikView or QlikSense failing to fetch data, or the Connection String is incorrect or Data Source is rejecting the request.

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 QlikView or QlikSense, then the issue falls outside of Qlik product.
  • If a 3rd party tool can fetch data but QlikView cannot, or gets different result as QlikView or QlikSense, then the issue falls inside Qlik product.

Notes:


The complete script in Test_Connection_String.ps1 is below:


##############################################################################
##
## Test_Connection_String.ps1
##
## This script tries to establish a DB connection as instructed by a given
## Connection String, then execute the given SQL query and save the result to
## out.csv file
##
##############################################################################
## Please provide Connection String
$ConnectionString = "Connection String to be tested"
## Please provide SQL Query
$SQLquery = "SQL query to be tested"
##############################################################################
$conn = New-Object System.Data.OleDb.OleDbConnection
$conn.ConnectionString = $ConnectionString
$comm = New-Object System.Data.OleDb.OleDbCommand($SQLquery,$conn)
$conn.Open()
$adapter = New-Object System.Data.OleDb.OleDbDataAdapter $comm
$dataset = New-Object System.Data.DataSet
$adapter.Fill($dataSet)
$conn.Close()
$table = $dataset.Tables[0]
$table | Export-CSV "out.csv"

 

 

Resolution:


First of all, one needs to enable Script Execution on the PC. In order to do so, follow these steps:

  1. Start Windows PowerShell as Administrator
    • 000006551a.jpg

       

    • 000006551b.jpg
  2. Run this script and answer 'Y': Set-ExecutionPolicy RemoteSigned000006551c.jpg
  3. Ready to go.

Steps to test Connection String:

  1. Open "Test_Connection_String.ps1" and edit following items:
    • $ConnectionString: the Connection String to be tested.
      • Password needs to be provided as clear text. Also the entire string needs to be inside a pair of double quotes("").
      • For generating Connection String externally, please refer to article How to generate Connection String without using QlikView.
      • Example: $ConnectionString = "Provider=OraOLEDB.Oracle.1;Persist Security Info=False;User ID=userid;password=password;Data Source=oraclexe"
    • $SQLquery: the SQL query to be tested.
      • The entire SQL query needs to be inside a pair of double quotes("").
      • Example: $SQLquery = "select * from employees"
  2. Save the file.
  3. In Windows PowerShell, navigate to the folder that contains Test_Connection_String.ps1, and run it using command: .\Test_Connection_String.ps1
  4. Verify there is no error generated.000006551d.jpg
  5. Open "out.csv" in the same folder and verify the result.
Labels (1)
Version history
Last update:
‎2021-05-14 10:25 AM
Updated by: