0

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?

  • What datatype is alphadawg.userid ? Sounds numeric to me – Philᵀᴹ Aug 24 '16 at 12:54
  • Datatype is character(8) - contains alpha-numeric values – user2676140 Aug 24 '16 at 12:57
  • IMHO you have to double the single quotes within the query string: Select * from OPENQUERY(LinkedServer, 'Select TRIM(trailing '' '' from ad.userid) As "User ID" FROM alphadawg ad'). But if you want to remove spaces there's no need to specifiy it at all as it's the default: TRIM(trailing from ad.userid) – dnoeth Aug 24 '16 at 13:09
  • Same OLE DB error as above. Requested conversion is not supported. – user2676140 Aug 24 '16 at 13:10
  • 1
    Did you try adding a cast? CAST(TRIM(trailing from ad.userid) AS VARCHAR(8))? Btw, PostgreSQL supports TRIM/RTRIM/LTRIM/BTRIM – dnoeth Aug 24 '16 at 13:12
1

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')
  • This returns an error of requested conversion is not supported. – user2676140 Aug 24 '16 at 13:10
  • It must be a unicode conversion issue. Trim returns text, which is unicode. Is the linked server declared as collation compatible? See edited answer – spaghettidba Aug 24 '16 at 13:15
  • Edited syntax produces error of ERROR: syntax error at or near "varchar"; – user2676140 Aug 24 '16 at 13:26
  • Now it should work. I have no Postgresql around to test. – spaghettidba Aug 24 '16 at 13:33
0
You might need to use SQL Server syntax on this one.

Select * from OPENQUERY(LinkedServer, 
'SELECT RTRIM(ad.UserId) AS [User ID]'
 FROM alphadawg AS [ad])

OR

Select * from OPENQUERY(LinkedServer, 
'SELECT REPLACE(RTRIM(ad.UserId),' ','') AS [User ID]'
 FROM alphadawg AS [ad])
  • The 2nd option you listed produces an error of Incorrect syntax near ',') AS [User ID]'. – user2676140 Aug 24 '16 at 13:29
  • 1
    OPENQUERY executes the query on the remote server using syntax of the remote system, so this must be PostgreSQL and not T-SQL. – dnoeth Aug 24 '16 at 13:42
  • Good call, @dnoeth I actually thought the remote server was the SQL Server one. – MguerraTorres Aug 24 '16 at 13:44
  • 1
    OPENQUERY doesn't exist in PostegreSQL :) – dnoeth Aug 24 '16 at 13:46
  • haha I guess I should have taken that hint initially :) – MguerraTorres Aug 24 '16 at 13:49
0

OPENQUERY executes given query at remote server to retrieve requested data. So you can at SQL Server if you want as well as at PostgreSQL.

To trim at PostgreSQL, your syntax is incorrect. It should be.

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

To trim at SQL Server, TRIM is divided into LTRIM (Left Trim) and RTRIM (Right Trim). So you should use bellow query

Select RTRIM(LTRIM([User ID])) from OPENQUERY(LinkedServer, 
              'Select ad.userid As [User ID]
              FROM alphadawg ad')

Thanks

0

I don't have a testing environment but I would be surprised if the following didn't work...

Select RTRIM([User ID]) [User ID]  from OPENQUERY(LinkedServer, 
          'Select ad.userid As "User ID"
          FROM alphadawg ad')
  • I like this one. Return it as normal from SQL Server, and handle the trimming on the Postgresql side. – MguerraTorres Aug 24 '16 at 13:22
  • I get an error of 'TRIM' is not a recognized built-in function name. – user2676140 Aug 24 '16 at 13:28
  • @user2676140: Of course you have to use RTRIM, it's on the SQL Server side. – dnoeth Aug 24 '16 at 13:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.