I know how to read value from database using connectionstring, i.e.
Establish database connection to read
$conn = New-Object System.Data.SqlClient.SqlConnection
$conn.ConnectionString = "Server=10.10.10.10;Initial Catalog=database_name;User Id=$username;Password=$password;"
$SQL = "..."
$conn.Open()
# Create and execute the SQL Query
$cmd = New-Object System.Data.SqlClient.SqlCommand($sql,$conn)
$count=0
do{
try{
$rdr = $cmd.ExecuteReader()
while ($rdr.read()){
$sql_output += ,@($rdr.GetValue(0), $rdr.GetValue(1))
$count=$count + 1
}
$transactionComplete = $true
}
catch{
$transactionComplete = $false
}
}until ($transactionComplete)
# Close the database connection
$conn.Close()
How can I accomplish the same thing with ODBC, i.e I have DSN (data source name) set up on the server?
According to https://www.connectionstrings.com/odbc-dsn/ you would use something like...
Can probably just go with
DSN=...
if creds not required.This works if your ODBC connection is under User DSN but not under System DSN. I cannot find a way to make it check for System DSN connections.
You may want to put this in front of the code...
If you want to do it on your local machine instead of in the context of SQL server then I would use the following. It is what we use at my company.
Always running in 32bit PowerShell at this point.