OpenQuery to Postgres Error on text field

  • 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">>

  • This was removed by the editor as SPAM

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

    and

    change the code to

    substr("Text",1,4000)

  • 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.

  • 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.

  • 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
  • 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

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

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