Fix: SQL Server to PostgreSQL Linked Server Error - "Requested conversion is not supported."

by Pio Balistoy on Jun 3, 2019 12:00:00 AM

Problem:

You created a linked server on SQL Server to a PostgreSQL database. You can open and navigate the linked server on SSMS up to listing the tables, but can not perform select to certain tables, returning the below errors:

The OLE DB provider "MSDASQL" for linked server "LinkedServername" returned message "Requested conversion is not supported.".

Msg 7341, Level 16, State 2, Line 43

Cannot get the current row value of column "[MSDASQL].ColumnName" from OLE DB provider "MSDASQL" for linked server "LinkedServername".

Msg 7356, Level 16, State 1, Line 53

The OLE DB provider "MSDASQL" for linked server "pLinkedServername" supplied inconsistent metadata for a column. The column "ColumnName" (compile-time ordinal 15) of object ""DatabaseName"."SchemaName"."ColumnName"" was reported to have a "DBCOLUMNFLAGS_ISLONG" of 128 at compile time and 0 at run time.

The data type throwing the error can either be a varchar, text, or Boolean. It also happens whether the specific column is included in the select statement or not.

Cause:

This happens because of a mismatch with the length of the data type or an incorrect conversion from the source data type in PostgreSQL and SQL Server.

Workaround:

You can work around this issue by force casting the selected column to a specific data type. This solution will work for data types that have different lengths on Postgresql and SQL Server. It will not work for incorrect conversions. eg. the column in PostgreSQL is a Boolean and the linked server want to receive it as a char value. For the proper solution, refer to to the section below.

Solution:

To address the issue properly, it all comes down to creating your Linked server correctly. Here are the proper steps for creating a SQL Server linked server to a PostgreSQL database:

1. Download and install the latest ODBC driver for the PostgreSQL version that you are using. There used to be a lot of third party ODBC for PostgreSQL since they didn't come out of one for 64 bit - and most SQL Server deployments out there are 64 bit. But now PostgreSQL has released their own. You can download the drivers here.

2. After installing the driver, create a 64 bit ODBC System DSN pointing to your PostgreSQL. Launch the ODBC Data Source Administrator on the server where the SQL Server is. If you want the DSN to be available for everyone which you should if you are going to use it for the linked server, go to the System DSN tab. Click Add.

3. Now there are two drivers that the Postgres has added. One for Unicode and a default one. The Unicode driver is for modern applications that have wider character sets and for Postgresql databases that are encoded with UTF-8/Unicode. You should likely be using this. The other details are self-explanatory. Those are the details for your PostgreSQL, database, server, user name, SSL Mode, port, password, description for the DSN, and the data source which will be the name of your DSN. You can click test after filling out the details to verify the connectivity.

4. Now this is the important part if you are receiving the conversion errors mentioned in this post. Under Options, click on Datasource. This will launch the Advanced Options window for the Datasource. From here you can set how the DSN will treat the data being retrieved from the source.

5. You can set how it will treat certain data types. For this scenario/issue, you'll need to uncheck the "Bools as Char" under data type options and treat Text as LongVarChar. You can also set Unknown Sizes to Maximum. The Max Varchar and MaxLongVarChar values can be left as default. But if you are hitting issues with length for varchar data types, you may adjust them accordingly.

6. Now that you have your ODBC DSN set correctly, you can recreate/create your Linked Server. You can use SSMS GUI. Log in to your SQL Server and navigate to Server Objects -> Right click Linked Servers and choose New Linked Servers.

7. Key in the name you want for your linked server. Choose Other Data source on server type and choose Microsoft OLE DB Provider for ODBC Drivers. You can put PostgreSQL on Product Name for clarity, then on Data Source input the DSN Name you created earlier.

8. Choose Security on the Left tab to configure the security for the linked server. Choose "Be made using this security context" and key in your username and password.

9. Choose Server Options on the left tab to change some settings as shown below. Note the changes on RPC settings.

10. Click OK. Now it will attempt to connect first. If all is well, it will be able to save and appear on your Linked Server list. If it can't connect currently it will tell you.

11. Now try selecting again. You should be able to select properly without encountering the errors.

12. You can also create the SQL Server Linked Server using the TSQL script below:

USE [master] go EXEC master . dbo . Sp_addlinkedserver @server = N'LinkedServerName' , @srvproduct = N'Postgresql' , @provider = N'MSDASQL' , @datasrc = N'DSN.Name' go EXEC master . dbo . Sp_serveroption @server = N'LinkedServerName' , @optname = N'collation compatible' , @optvalue = N'false' go EXEC master . dbo . Sp_serveroption @server = N'LinkedServerName' , @optname = N'data access' , @optvalue = N'true' go EXEC master . dbo . Sp_serveroption @server = N'LinkedServerName' , @optname = N'dist' , @optvalue = N'false' go EXEC master . dbo . Sp_serveroption @server = N'LinkedServerName' , @optname = N'pub' , @optvalue = N'false' go EXEC master . dbo . Sp_serveroption @server = N'LinkedServerName' , @optname = N'rpc' , @optvalue = N'true' go EXEC master . dbo . Sp_serveroption @server = N'LinkedServerName' , @optname = N'rpc out' , @optvalue = N'false' go EXEC master . dbo . Sp_serveroption @server = N'LinkedServerName' , @optname = N'sub' , @optvalue = N'false' go EXEC master . dbo . Sp_serveroption @server = N'LinkedServerName' , @optname = N'connect timeout' , @optvalue = N'0' go EXEC master . dbo . Sp_serveroption @server = N'LinkedServerName' , @optname = N'collation name' , @optvalue = NULL go EXEC master . dbo . Sp_serveroption @server = N'LinkedServerName' , @optname = N'lazy schema validation' , @optvalue = N'false' go EXEC master . dbo . Sp_serveroption @server = N'LinkedServerName' , @optname = N'query timeout' , @optvalue = N'0' go EXEC master . dbo . Sp_serveroption @server = N'LinkedServerName' , @optname = N'use remote collation' , @optvalue = N'true' go EXEC master . dbo . Sp_serveroption @server = N'LinkedServerName' , @optname = N'remote proc transaction promotion' , @optvalue = N'true' go USE [master] go EXEC master . dbo . Sp_addlinkedsrvlogin @rmtsrvname = N'LinkedServerName' , @locallogin = NULL , @useself = N'False' , @rmtuser = N'pgsqlUserName' , @rmtpassword = N'pgsqlUserNamePasswordHere' go

This should address the conversion issues and you'll be able to select without any issue.

Topics: Microsoft SQL Server Technical Track Service Broker Guid Mismatch

Fix: SQL Server to PostgreSQL Linked Server Error - "Requested conversion is not supported."

Problem:

Cause:

Workaround:

Solution:

Comments (1)

Subscribe by email

Share this

Fix: SQL Server to PostgreSQL Linked Server Error - "Requested conversion is not supported."

Problem:

Cause:

Workaround:

Solution:

Share this

Comments (1)

Subscribe by email