This article describes basic steps on how to test a connection with ODBC DSN in Powershell.
Also see How to fetch data via ODBC DSN without using Qlik Products
Environment:
- QlikView
- Qlik Sense Enterprise on Windows
- Microsoft Windows Server 2012 and above
Steps:
- Configure a DSN connection using the ODBC Data Source Administrator
- Open PowerShell ISE
- Use the following code and change values accordingly
Get-Date
$dnsName= "DSNNAME"
$user="USERID"
$password="PASSWORD"
$csvPath= "C:\<path>\<filename>.csv"
$sqlQuery= "select * from DB.dbo.Table"
$conn = New-Object Data.Odbc.OdbcConnection
$conn.ConnectionString= "dsn=$dnsName;uid=$user;pwd=$password;"#"dns=$dnsName;uid=sa;pwd=Password123!;"
$conn.open()
$command =$conn.CreateCommand();
$command.CommandText=$sqlQuery
$dataAdapter = New-Object System.Data.Odbc.OdbcDataAdapter $command
$dataTable = new-object "System.Data.DataTable"
$dataAdapter.Fill($dataTable)
$conn.close()
$dataTable | Export-csv -Path $csvPath -NoTypeInformation
Get-Date
Where:
DSNNAME is the name was configured in the ODBC Data Source Administrator tool
USERID is the user id will be use to connect to the database
PASSWORD is the password for the provided user id
- Click the "Run Script" button or press F5, verify the execution was completed successfully and open the path where the file was generated.
Use as reference both dates to verify how long it took to complete the query from the server/computer from where are you testing.