0

I am looking into establishing foreign data wrapper between PostgreSQL 14 and remote SQL Server 2014. I do not have administrative-level access to the remote SQL Server, thus need to make changes only at Postgres.

The error I currently encounter is below.

DB-Library notice: Msg #: 7405, Msg state: 1,
Msg: Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be set for the connection. This ensures consistent query semantics. Enable these options and then reissue your query.

Where do I set ANSI_NULLS and ANSI_WARNINGS? I don't mind going lower level at freetds or libdb level if necessary. The option to do this does not seem to be available from within tds_fdw. From this issue, https://github.com/tds-fdw/tds_fdw/issues/186 it does not seem that this feature will be available.

My environment is RockyLinux 8.5, PostgreSQL 14.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
zaidwaqi
  • 609
  • 1
  • 8
  • 25
  • 2
    You should issue `set ansi_nulls, ansi_warnings on;` immediately before the remote query. – Stu Feb 20 '22 at 09:58
  • where do i do that? i'm not using interactive sql server client. i'm using foreign data wrapper within postgres. – zaidwaqi Feb 20 '22 at 10:06
  • Does this error occur on connection or on query? If on query, you should be able to `SET ANSI_NULLS, ANSI_WARNINGS ON; SELECT * FROM my_table;` – FlipperPA Feb 20 '22 at 16:36
  • error on connection. i tried within query but doesn't work. Same error came out. -> Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be set for the connection – zaidwaqi Feb 20 '22 at 16:47

0 Answers0