OpenQuery to Postgres Error on text field

Question

Post reply

OpenQuery to Postgres Error on text field

Viewing 5 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

rrv Old Hand Points: 319 More actions · Answer 1

may be you need to increase the max varchar size in the postgres driver

and

change the code to

substr("Text",1,4000)

jgrubb SSCrazy Points: 2542 More actions · Answer 2

You've a datatype problem here. You'll need need to explicitly trim the results to be less than 8000 bytes. Postgres text is similar to MSSQL text. Postgres varchar (or character varying) is not similar to MSSQL varchar. It can be bigger. Postgres implicit casts do not always work like you expect.

If you explore the resultset return types, betcha you're getting something unexpected.

Linda Johanning SSCertifiable Points: 6727 More actions · Answer 3

I agree that there is a datatype issue as I am trying to do the same thing. I was stuck on a PostgreSQL BPCHAR datatype and got the same error as you did (using openquery and also the 4-part name) however, I stumbled on this and it seems to work:

declare @sql nvarchar(2000)

Set @sql = 'select * from schema.table'

exec (@sql) At LinkedServerName

This query usually shows a better explanation of a PostgreSQL error, but when I run it against this particular table the data is returned. For me the column in question contains Y, N or Null. Interesting. I hope this works for you.

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 4

Please note: 6 year old thread.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

TomThomson SSC Guru Points: 104768 More actions · Answer 5

GilaMonster (7/9/2013)
Please note: 6 year old thread.

But note that the site owners pushed it after it had gone unanswered for 5 days more than a year - which should make us ask ourselves how old does something have to be before it is old?

Tom