BUG #16110: Exporting data from SQL Server to PostgreSQL 12 using a linked server within SQL Server fails

The following bug has been logged on the website:

Bug reference: 16110
Logged by: Bart Hofland
Email address: bart(dot)hofland(dot)2(at)gmail(dot)com
PostgreSQL version: 12.0
Operating system: Windows 10 (Version 10.0.18362.449)
Description:

**The issue:**

Exporting data from a Microsoft SQL Server database table to a PostgreSQL 12
database table using a linked server using the latest psqlodbc driver and
the OPENQUERY functionality in Microsoft SQL Server fails.

Performing exactly the same export to a PostgreSQL 11.5 database works
perfectly fine, however.

**The environment:**

Platform: Windows 10 64-bit (version 10.0.18362.449)
psqlodbc: Version 12.0.0 (psqlodbc_12_00_0000-x64.zip)
PostgreSQL 11.5: "PostgreSQL 11.5, compiled by Visual C++ build 1914,
64-bit"
PostgreSQL 12.0: "PostgreSQL 12.0, compiled by Visual C++ build 1914,
64-bit"
PostgreSQL client: pgAdmin 4.14 (separately installed)
SQL Server: SQL Server 2019 Developer Edition
SQL Server client: SQL Server Management Studio 18.4

**Steps to reproduce:**

- Install PostgreSQL 11.5 for Windows (selecting only components "PostgreSQL
Server" and "Command Line Tools", using password "something" for the
postgres master login, using port 5432 (in my case), and using the C
locale).
- Install PostgreSQL 12.0 for Windows (selecting only components "PostgreSQL
Server" and "Command Line Tools", using password "something" for the
postgres master login, using port 5433 (in my case), and using the C
locale).
- Install psqlodbc (the latest 64-bit release of October 2019).
- Install SQL Server (preferrably a Developer Edition of version 2019, but
other recent versions/editions might work just fine as well).
- In both PostgreSQL servers, create a target database called "target_db".
- Install SQL client tools, like pgAdmin and SQL Server Management Studio
(or your own favorite client tools that enable you to manage SQL Server and
PostgreSQL databases).
- In the "target_db" databases in both PostgreSQL servers, create an empty
target table "target_table":

```
CREATE TABLE "target_table" ("value" int NOT NULL);
```

- In SQL Server, create a database "SourceDB".
- In the "SourceDB" database in SQL Server, create a source table
"SourceTable", using the script below:

```
CREATE TABLE [SourceDB]..[SourceTable] ([Value] INT NOT NULL);
INSERT INTO [SourceDB]..[SourceTable] ([Value]) VALUES (1), (2), (3);
```

- In SQL Server, create linked servers targeting the "target_db" databases
in the PostgreSQL 11.5 and 12.0 servers, using the scripts below:

```
EXECUTE sp_addlinkedserver
@server = N'postgres_11_target',
@srvproduct = N'PostgreSQL Unicode',
@provider = N'MSDASQL',
@provstr = N'Driver={PostgreSQL
Unicode(x64)};Server=127.0.0.1;Port=5432;Database=target_db;User
Id=postgres;Password=something;MaxVarcharsize=-4;MaxLongVarcharSize=-4';

EXECUTE sp_serveroption
@server = N'postgres_11_target',
@optname = N'rpc',
@optvalue = N'true';

EXECUTE sp_serveroption
@server = N'postgres_11_target',
@optname = N'rpc out',
@optvalue = N'true';

EXECUTE sp_addlinkedsrvlogin
@rmtsrvname = N'postgres_11_target',
@useself = N'false',
@rmtuser = N'postgres',
@rmtpassword = N'something';
```

```
EXECUTE sp_addlinkedserver
@server = N'postgres_12_target',
@srvproduct = N'PostgreSQL Unicode',
@provider = N'MSDASQL',
@provstr = N'Driver={PostgreSQL
Unicode(x64)};Server=127.0.0.1;Port=5433;Database=target_db;User
Id=postgres;Password=something;MaxVarcharsize=-4;MaxLongVarcharSize=-4';

EXECUTE sp_serveroption
@server = N'postgres_12_target',
@optname = N'rpc',
@optvalue = N'true';

EXECUTE sp_serveroption
@server = N'postgres_12_target',
@optname = N'rpc out',
@optvalue = N'true';

EXECUTE sp_addlinkedsrvlogin
@rmtsrvname = N'postgres_12_target',
@useself = N'false',
@rmtuser = N'postgres',
@rmtpassword = N'something';
```

Note: You might need to update the port numbers in the linked server
connection strings to your actual port numbers.

- In SQL Server, verify that the linked servers are working by selecting
data in the PostgreSQL target table, using these queries:

```
SELECT [value]
FROM OPENQUERY([postgres_11_target], 'SELECT "value" FROM
"target_table"');
```

```
SELECT [value]
FROM OPENQUERY([postgres_12_target], 'SELECT "value" FROM
"target_table"');
```

- In SQL Server, execute the following queries to export data from SQL
Server to the PostgreSQL target databases:

```
INSERT OPENQUERY([postgres_11_target], 'SELECT "value" FROM
"target_table"')
SELECT [Value] FROM [SourceDB]..[SourceTable];
```

```
INSERT OPENQUERY([postgres_12_target], 'SELECT "value" FROM
"target_table"')
SELECT [Value] FROM [SourceDB]..[SourceTable];
```

The first query will succeed. And when executing the SELECT query from the
previous step, the new data in the PostgreSQL target table will be shown.

The second query will fail with the following error messages:

```
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "MSDASQL" for linked server "postgres_12_target"
reported an error. The provider did not give any information about the
error.
Msg 7320, Level 16, State 2, Line 1
Cannot execute the query "SELECT "value" FROM "target_table"" against OLE DB
provider "MSDASQL" for linked server "postgres_12_target".
```