Re: BUG #9182: Data type (text) issue with MS SQL 2012

Hi Tomas,

The problem is the PostgreSQL ODBC driver that is used to translate the query from the PostgreSQL database to the MSSQL server by means of ODBC and Linked server

The path is:

1. The PostgreSQL is configured in the ODBC using PostgreSQL driver

2. The ODBC is configured in the Linked server in MS SQL server

I have Isolate the issue to the data type of the column. The below example will show this.

With the pgAdmin tool help I could Isolate the issue
PGAdmin query tool: SELECT * FROM public.inbox WHERE Created_dtm > '20140211'
Result:
[cid:image004(dot)png(at)01CF2722(dot)BE4136F0]

On MS SQL SERVER 2012 using linked server: SELECT * FROM [SENDQUICKSMS].spooldb.[public].inbox WHERE Created_dtm > '20140211'
Result:
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "MSDASQL" for linked server "SENDQUICKSMS" reported an error. The provider did not give any information about the error.
Msg 7312, Level 16, State 1, Line 1
Invalid use of schema or catalog for OLE DB provider "MSDASQL" for linked server "SENDQUICKSMS". A four-part name was supplied, but the provider does not expose the necessary interfaces to use a catalog or schema.

But if you run: SELECT MSGID, Created_dtm FROM [SENDQUICKSMS]...inbox WHERE Created_dtm > '20140211'
Result:
MSGID Created_dtm
IN240431612473483 2024-02-12 16:12:21.8590130
IN240432023473520 2024-02-12 20:23:04.7716200
IN240461647480233 2024-02-15 16:47:03.2086720

Now I Add column Modem_Imei : SELECT MSGID, Created_dtm, Modem_Imei FROM [SENDQUICKSMS]...inbox WHERE Created_dtm > '20140211'
OLE DB provider "MSDASQL" for linked server "SENDQUICKSMS" returned message "Requested conversion is not supported.".
Msg 7341, Level 16, State 2, Line 1
Cannot get the current row value of column "[SENDQUICKSMS]...[inbox].modem_imei" from OLE DB provider "MSDASQL" for linked server "SENDQUICKSMS".

Now I Add column smsc : SELECT MSGID , Created_dtm, smsc FROM [SENDQUICKSMS]...inbox WHERE Created_dtm > '20140211'
OLE DB provider "MSDASQL" for linked server "SENDQUICKSMS" returned message "Requested conversion is not supported.".
Msg 7341, Level 16, State 2, Line 1
Cannot get the current row value of column "[SENDQUICKSMS]...[inbox].smsc" from OLE DB provider "MSDASQL" for linked server "SENDQUICKSMS".

Now I Add column Mobile_Numb : SELECT MSGID , Created_dtm, Mobile_Numb FROM [SENDQUICKSMS]...inbox WHERE Created_dtm > '20140211'
MSGID Created_dtm Mobile_Numb
IN240431612473483 2024-02-12 16:12:21.8590130 +61427923485
IN240432023473520 2024-02-12 20:23:04.7716200 +61403073634
IN240461647480233 2024-02-15 16:47:03.2086720 +61449144907

Now I Add column msg_content : SELECT MSGID, Created_dt, msg_content FROM [SENDQUICKSMS]...inbox WHERE Created_dtm > '20140211'
OLE DB provider "MSDASQL" for linked server "SENDQUICKSMS" returned message "Requested conversion is not supported.".
Msg 7341, Level 16, State 2, Line 1
Cannot get the current row value of column "[SENDQUICKSMS]...[inbox].msg_content" from OLE DB provider "MSDASQL" for linked server "SENDQUICKSMS".

Conclusion:
All the columns that have a issue are from the data type text. It seems that ODBC driver is not translating the data type (Text) to the correct format for MS SQL 2012.

Any Idea how to solve this issue? Can the driver be corrected or as work around can some new views be created that translate the text columns to character varying(max)

-----Original Message-----
From: Tomas Vondra [mailto:tv(at)fuzzy(dot)cz]
Sent: Wednesday, 12 February 2014 10:01
To: Roger Berkelmans; pgsql-bugs(at)postgresql(dot)org
Subject: Re: [BUGS] BUG #9182: Data type (text) issue with MS SQL 2012

On 11.2.2014 03:27, RogerBerkelmans(at)arlcollect(dot)com(dot)au wrote:

> The following bug has been logged on the website:

>

> Bug reference: 9182

> Logged by: Roger

> Email address: RogerBerkelmans(at)arlcollect(dot)com(dot)au

> PostgreSQL version: 9.3.1

> Operating system: Windows server 2008R2

> Description:

>

> we have a SMS system taht is using a pg-database. we connect the database by

> ODBC to a linked server to a MS SQL 2012

> sehen running a select query from the MS SQL 2012 on the pg-database and the

> data type of the column is a text we get the following error:

> OLE DB provider "MSDASQL" for linked server "linked server name" returned

> message "Requested conversion is not supported.".

> Msg 7341, Level 16, State 2, Line 1

> Cannot get the current row value of column "[linked server

> name]...[inbox].ABC" from OLE DB provider "MSDASQL" for linked server

> "linked server name".

Apparently your bug report is about MS SQL Server, while this list is

about PostgreSQL. So you'll have to report the issue somewhere else, I'm

afraid.

regards

Tomas