Invoke-Sqlcmd fail when connecting to Postgresql Database and doing SQL command

Question

I have been able to connect to the PostgreSQL Server with OdbcConnection.

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

However I wish to connect to the PostgreSQL with command Invoke-Sqkcmd. I was told to always try to use the cmdlets and their commands. I tried it with:

$MyPort = '5432'
$MyUid = 'postgres'
$MyPass = 'pass'
$DBSvr = 'server'
$DBase = 'database'
$sqlcmd = 'SELECT email_addr FROM member_subscribers LIMIT 20'
$data = Invoke-Sqlcmd -query $sqlcmd -Database $DBase -ServerInstance    $DBSvr$MyPort -Username $MyUid -Password $MyPass 
Write-Output $data

However, I get the error.

Invoke-Sqlcmd : A network-related or instance-specific error occurred while establishing a connection to 
SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and 
that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - 
Could not open a connection to SQL Server)
At line:13 char:9
 + $data = Invoke-Sqlcmd -query $sqlcmd -Database $DBase -ServerInstance    ...
+         ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo          : InvalidOperation: (:) [Invoke-Sqlcmd],   SqlException
+ FullyQualifiedErrorId : SqlExectionError,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand

 Invoke-Sqlcmd : 
 At line:13 char:9
 + $data = Invoke-Sqlcmd -query $sqlcmd -Database $DBase -ServerInstance ...
 +         ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  + CategoryInfo          : ParserError: (:) [Invoke-Sqlcmd],  ParserException
   + FullyQualifiedErrorId : ExecutionFailureException,Microsoft.SqlServer.Management.PowerShell.GetScri 
ptCommand

I know the credentials should be correct. So maybe the formatting is just wrong? I am not sure....

Mike Shepard · Accepted Answer · 2016-10-19 20:10:58Z

4

Invoke-SQLCmd is a SQL Server cmdlet. You can't connect to postgres using it.

answered Oct 19, 2016 at 20:10

Mike Shepard

18.4k7 gold badges59 silver badges72 bronze badges

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

2 Comments

It's Just a Printer Driver Over a year ago

I am guessing there is no cmdlet that can connect to postgres?

Mike Shepard Over a year ago

There isn't a native one, but I've written a "framework" that works for any ADO.NET provider. github.com/MikeShepard/POSH_Ado. I haven't written a submodule for Postgres, but it should be pretty simple.

Collectives™ on Stack Overflow

Invoke-Sqlcmd fail when connecting to Postgresql Database and doing SQL command

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related