Subject: SSMS ODBC Linked Server Issue

From: Dulanic <[email-masked]>
Date: Tue, 17 Aug 2021 13:36:01 -0500

Hello,

I have reviewed a few different examples of SQL Server/Postgres ODBC linked
server connections. I have gotten some to work, but some tables refuse.
This is the query I am attempting to run and the methods I have tried so
far.

select *
from POSTGRES.teslamate.[public].addresses

and

 select * from openquery(POSTGRES,'
select *
from public.addresses
')

but both error out /w this error:

Msg 7356, Level 16, State 1, Line 1
The OLE DB provider "MSDASQL" for linked server "POSTGRES" supplied
inconsistent metadata for a column. The column "display_name" (compile-time
ordinal 2) of object "
select *
from public.addresses
" was reported to have a "LENGTH" of 512 at compile time and 174 at run
time. It seems like it is saying it's a 512 length field, and it is. It is
a character varying(512). However, when the query runs, it comes back
saying it's a 174 length field which is the current max length. These are
the settings I am using for ODBC, though I have tried every
possible variation of these that I could find to solve.

These are my options in SSMS:
https://dulanic.com/img/ssms.png

These are my options in ODBC.
https://dulanic.com/img/odbc.png
https://dulanic.com/img/odbc1.png
https://dulanic.com/img/odbc2.png

---

From: David Parenteau <[email-masked]>
Date: Tue, 17 Aug 2021 18:48:45 +0000

Maybe you could try replacing the start with casted enumerated list of columns.
For my part, calling PGSQL directly from SQL SSMS was also unstable so we now use to call our PGSQL code using powershell script and using the same ODBC. For now, we have not encountered unstable issues like in SSMS!

Good luck
David Parenteau

From: Dulanic <[email-masked]>
Sent: August 17, 2021 14:36
To: [email-masked]
Subject: SSMS ODBC Linked Server Issue

Hello,

I have reviewed a few different examples of SQL Server/Postgres ODBC linked server connections. I have gotten some to work, but some tables refuse. This is the query I am attempting to run and the methods I have tried so far.

select *

and

Msg 7356, Level 16, State 1, Line 1
The OLE DB provider "MSDASQL" for linked server "POSTGRES" supplied inconsistent metadata for a column. The column "display_name" (compile-time ordinal 2) of object "
select *
from public.addresses
" was reported to have a "LENGTH" of 512 at compile time and 174 at run time. It seems like it is saying it's a 512 length field, and it is. It is a character varying(512). However, when the query runs, it comes back saying it's a 174 length field which is the current max length. These are the settings I am using for ODBC, though I have tried every possible variation of these that I could find to solve.