1

I've routinely connected to a postgres database via ODBC using a powershell script. Like this....

$DBConnectionString = "Driver={PostgreSQL Unicode(x64)};Server=$MyServer;Port=$MyPort;Database=$MyDB;Uid=$MyUid;Pwd=$MyPass;Timeout=$timeout;CommandTimeout=$commandTimeout;"
$script:DBConn = New-Object System.Data.Odbc.OdbcConnection;
$DBConn.ConnectionString = $DBConnectionString;
$DBConn.Open();

I've just started getting the error....

Exception calling "Open" with "0" argument(s): "ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified"

Since this script is running a new build, I'm guessing a configuration error. Server is windows 11, Postgre is a fresh installation of 14.2 downloaded from the official site The latest ODBC drivers came from the file psqlodbc_13_02_0000-x64.zip also downloaded from the offical site

I think the ODBC driver installation was successful because if I run ODBC 64 bit administrator, click on the 'drivers' tab I can see the 4 drivers I expect

  1. PostgreSQL ANSI
  2. PostgreSQL ANSI(x64)
  3. PostgreSQL Unicode
  4. PostgreSQL Unicode(x64)

I've tried editing the script to try each of the 4 drivers I think I have - but get the same error each time. I know the database is up and working, I can connect via pgAdmin and psql.

It's a new, clean installation of something that has worked on previous servers for several years. What have I forgotten or done wrong?

The script is being run from a desktop shortcut with this target (I think this demonstrates I'm running the 64 bit version)

"%SystemRoot%\syswow64\WindowsPowerShell\v1.0\powershell.exe " -NoExit -command "&c:\pwrShl\TestToDb_015.ps1

I've tried setting up a DSN (just for test) and I the ODBC 'test' button says the connection worked. I've tried running the powershell script as administrator - makes no difference.

4
  • Do you have a DSN configured?
    – MelvinLusk
    Commented May 16, 2022 at 19:15
  • @MelvinLusk No DSN. I've not needed one before Commented May 16, 2022 at 19:20
  • It may be worth creating one just to test that the ODBC driver is working as it should.
    – MelvinLusk
    Commented May 16, 2022 at 19:33
  • 1
    That's the error you get when the driver's not installed. Are you sure your powershell is running in a 64bit process? Commented May 16, 2022 at 20:35

1 Answer 1

1

It should work:

$DBConnectionString = "Driver={PostgreSQL Unicode(x64)};Server=$MyServer;Port=$MyPort;Database=$MyDB;Uid=$MyUid;Pwd=$MyPass;Timeout=$timeout;CommandTimeout=$commandTimeout;"
$DBConn = [System.Data.Odbc.OdbcConnection]::new($DBConnectionString)
$DBConn.Open();

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.