BUG #16110: Exporting data from SQL Server to PostgreSQL 12 using a linked server within SQL Server fails - Mailing list pgsql-bugs
From | PG Bug reporting form |
---|---|
Subject | BUG #16110: Exporting data from SQL Server to PostgreSQL 12 using a linked server within SQL Server fails |
Date | |
Msg-id | 16110-58018b81fc7ade4c@postgresql.org Whole thread Raw |
List | pgsql-bugs |
The following bug has been logged on the website: Bug reference: 16110 Logged by: Bart Hofland Email address: bart.hofland.2@gmail.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". ```
pgsql-bugs by date: