Postgres ODBC : SQL Server EXEC AT : Errors Extracting CHAR(MAX) type columns

Discussion of open issues, suggestions and bugs regarding usage of ODBC Drivers
Post Reply
camelCase
Posts: 2
Joined: Wed 02 Jun 2021 11:30

Postgres ODBC : SQL Server EXEC AT : Errors Extracting CHAR(MAX) type columns

Post by camelCase » Wed 02 Jun 2021 12:00

SQL SERVER.
Using DEVART ODBC connector to link to Postgres DB (as LinkedServer).

The ODBC driver reports an error when extracting the Postgres equivalent of a CHAR(MAX) type column (i.e. citext(-1)) using the EXEC AT method...
EXEC ('SELECT "CiTextColumn" FROM postgresdatabase."myTable"') AT DevArtODBCLinkedPostgres;
-- FAILS with ERROR
-- OLE DB provider "STREAM" for linked server "(null)" returned message "Multiple-step OLE DB operation generated errors
EXEC ('SELECT "CiTextColumn"::char(36) FROM postgresdatabase."myTable"') AT DevArtODBCLinkedPostgres;
-- WORKAROUND converting citext(-1) to char(36) does work. :) - but is not a sustainable workaround.

evgeniym
Devart Team
Posts: 44
Joined: Thu 13 May 2021 07:08

Re: Postgres ODBC : SQL Server EXEC AT : Errors Extracting CHAR(MAX) type columns

Post by evgeniym » Thu 03 Jun 2021 09:01

Hi there,

Can you please send us trace log when the error message appears?
You may find more information about how to create trace log in our documentation:
https://www.devart.com/odbc/postgresql/ ... racing.htm

Note that as alternative you may use full path instead of "AT DevArtODBCLinkedPostgres".
For instance:
EXEC ('SELECT "CiTextColumn" FROM DevArtODBCLinkedPostgres.[path].postgresdatabase."myTable"')

Or use OPENQUERY (please find additional information in the documentation below):
https://docs.microsoft.com/en-us/sql/t- ... rver-ver15

Best regards,
Evgeniy

camelCase
Posts: 2
Joined: Wed 02 Jun 2021 11:30

Re: Postgres ODBC : SQL Server EXEC AT : Errors Extracting CHAR(MAX) type columns

Post by camelCase » Thu 10 Jun 2021 15:28

I imagine the pertinent part of the log is the mention of :
  • "<invalid buffer length!> [-3]"
NOTE : similar 'invalid buffer length' entries appear in logs for BOTH the [error returning citext(-1) query] and the [non-error returning char(36) query].
odbcad32 397c-3e84 ENTER SQLDriverConnectW
HDBC 0x00000240D3147620
HWND 0x0000000000000000
WCHAR * 0x00007FFC42A52190 [ -3] "******\ 0"
SWORD -3
WCHAR * 0x00007FFC42A52190
SWORD -3
SWORD * 0x0000000000000000
UWORD 0 <SQL_DRIVER_NOPROMPT>

odbcad32 397c-3e84 EXIT SQLDriverConnectW with return code 0 (SQL_SUCCESS)
HDBC 0x00000240D3147620
HWND 0x0000000000000000
WCHAR * 0x00007FFC42A52190 [ -3] "******\ 0"
SWORD -3
WCHAR * 0x00007FFC42A52190 <Invalid buffer length!> [-3]
SWORD -3
SWORD * 0x0000000000000000
UWORD 0 <SQL_DRIVER_NOPROMPT>
Please advise if the full log will better help resolve the issue.

NOTE : OPENQUERY is more tricky to read/devise when including dynamic variables in the SQL.

evgeniym
Devart Team
Posts: 44
Joined: Thu 13 May 2021 07:08

Re: Postgres ODBC : SQL Server EXEC AT : Errors Extracting CHAR(MAX) type columns

Post by evgeniym » Fri 11 Jun 2021 09:06

Hey,
Can you please send us the log file when the error message appears?
Also, we need DDL scripts to create and fill-in test database objects so we could reproduce the issue on our side.
You may send this information using the form on our site:
https://www.devart.com/company/contactform.html

Regards,
Evgeniy

Post Reply