Powershell to read from database using ODBC DSN instead of connection string

Question

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?

Did the answer below work for you?

Robino
– Robino

2014-09-16 14:49:05 +00:00
Commented Sep 16, 2014 at 14:49 — Robino
– Robino, Commented Sep 16, 2014 at 14:49

andyb · Accepted Answer · 2014-08-14 14:42:27Z

9

According to https://www.connectionstrings.com/odbc-dsn/ you would use something like...

DSN=myDsn;Uid=myUsername;Pwd=;

Can probably just go with DSN=... if creds not required.

answered Aug 14, 2014 at 14:42

andyb

2,7821 gold badge20 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Glowie Over a year ago

@Robino yes it worked, even with encrypted $password $qt = $ws.QueryTables.Add("ODBC;DSN=$DSN;UID=$username;PWD=$password", $ws.Range("A1"), $SQL1)

Douglas Plumley Over a year ago

This didn't work for me either for the SqlClient, this works for an ODBC connection. With the SqlClient I get the following error: Exception calling ".ctor" with "1" argument(s): "Keyword not supported: 'dsn'."

Mark · Accepted Answer · 2024-08-28 11:22:12Z

8

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.

$conn = new-object System.Data.Odbc.OdbcConnection
$conn.connectionstring = "DSN=DSNNAME"
$conn.open()

$cmd = New-object System.Data.Odbc.OdbcCommand($sqlCommand,$conn)
$dataset = New-Object System.Data.DataSet
(New-Object System.Data.Odbc.OdbcDataAdapter($cmd)).Fill($dataSet) | Out-Null
$conn.Close()

edited Aug 28, 2024 at 11:22

Mark

6,3212 gold badges38 silver badges35 bronze badges

answered Oct 27, 2016 at 21:28

Iconiu

3875 silver badges5 bronze badges

Comments

Julian Camilleri · Accepted Answer · 2018-08-14 14:50:25Z

1

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.

if ($env:Processor_Architecture -ne "x86")   
{ write-warning 'Launching x86 PowerShell'
&"$env:windir\syswow64\windowspowershell\v1.0\powershell.exe" -noninteractive -noprofile -file $myinvocation.Mycommand.path -executionpolicy bypass
exit
}

Always running in 32bit PowerShell at this point.

$env:Processor_Architecture

edited Aug 14, 2018 at 14:50

Julian Camilleri

3,1451 gold badge28 silver badges39 bronze badges

answered Aug 14, 2018 at 13:57

Cary

111 bronze badge

Collectives™ on Stack Overflow

Powershell to read from database using ODBC DSN instead of connection string

Establish database connection to read

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

Establish database connection to read

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related