OpenQuery to Postgres Error on text field

Question

Post reply

OpenQuery to Postgres Error on text field

cenko

SSC Veteran

Points: 281
More actions
January 3, 2007 at 8:18 am

#171430

I am getting the error below when executing the following query to extract data from a linked server (CORE_ODBC) to Postgres. The column "Text" is of text datatype. The values in the column can be over 1MB in size. I’ve increased the length for casting the "Text" column but still get the same error. Any thoughts on how to handle this? style="FONT-SIZE: 13px; COLOR: rgb(0,0,255); FONT-FAMILY: Courier New"> >
Select * From Openquery(CORE_ODBC, 'Select "TCKT_MessageID", "CONT_ContactID", "Date", cast("Text" as varchar(4000)), "Private", "TCKT_val_SourceID", "SourceContact", "SentToCustomer", "UnusedColumn", "PrivatizeContact", "DatePrivatized", "TCKT_TicketID", cast("creation_date" as varchar(30)), cast("modification_date" as varchar(30)) From "TCKT_Message" ') style="FONT-SIZE: 13px; COLOR: rgb(128,128,128); FONT-FAMILY: Courier New">>
Msg 7347, Level 16, State 1, Line 1 style="FONT-SIZE: 10px; FONT-FAMILY: Courier New">>
OLE DB provider 'MSDASQL' for linked server 'CORE_ODBC' returned data that does not match expected data length for column '[MSDASQL].Text'. The (maximum) expected data length is 8000, while the returned data length is 8318. style="FONT-SIZE: 10px; FONT-FAMILY: Courier New">>
January 8, 2007 at 8:00 am

This was removed by the editor as SPAM

Viewing 7 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: 1004504 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: 104773 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