Sql-server – TRIM() Function With Linked Server

linked-serverpostgresqlsql-server-2008-r2trim

I have a linked server connection for a postgresql into sql server. I can run this in Sql Server Select * from OpenQuery(LinkedServer,'Select * from alphadawg') and this returns all results, but the second I try to add in any functions I start getting errors. For example, if I wanted to TRIM() the trailing spaces off of a field

Select * from OPENQUERY(LinkedServer, 
              'Select TRIM(trailing ' ' from ad.userid) As "User ID"
              FROM alphadawg ad')

I get an error of

Msg 102, Level 15, State 1, Line 5
Incorrect syntax near ' from ad.userid) As "User ID"

Okay, so maybe the issue is I am trying to execute postgresql in Sql Server, so I altered the syntax to

    Select * from OPENQUERY(LinkedServer, 
              'Select RTRIM(ad.userid) As "User ID"
              FROM alphadawg ad')

But that then threw an error of:

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

How can you use the TRIM() function with a linked server connection between postgresql and SQL Server?

Best Answer

Quotes must be escaped in the OPENQUERY argument.

The return type for the TRIM function is text, so you should cast it back to varchar.

Select * from OPENQUERY(LinkedServer, 
    'Select CAST(TRIM(trailing '' '' from ad.userid) AS varchar(8)) As "User ID"
    FROM alphadawg ad')
Related Question