Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

OpenQuery to Postgres Error on text field Expand / Collapse
Author
Message
Posted Wednesday, January 3, 2007 8:18 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, November 4, 2009 12:31 PM
Points: 5, Visits: 42

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

Post #334022
Posted Monday, January 8, 2007 8:00 AM
SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, May 18, 2007 3:36 PM
Points: 10,039, Visits: 1
No one has responded to this topic yet. Even if you don't have a complete answer, the original poster will appreciate any thoughts you have!
Post #335042
Posted Tuesday, August 12, 2008 12:30 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, January 27, 2010 1:41 PM
Points: 3, Visits: 60
may be you need to increase the max varchar size in the postgres driver

and

change the code to

substr("Text",1,4000)
Post #551325
Posted Thursday, August 14, 2008 8:16 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, October 24, 2014 10:44 AM
Points: 279, Visits: 513

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.

Post #552737
Posted Tuesday, July 9, 2013 7:04 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: 2 days ago @ 11:13 AM
Points: 331, Visits: 559
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.



Post #1471598
Posted Tuesday, July 9, 2013 7:34 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 8:36 AM
Points: 40,615, Visits: 37,080
Please note: 6 year old thread.


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1471614
Posted Tuesday, July 9, 2013 6:00 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 5:50 PM
Points: 7,923, Visits: 9,649
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
Post #1471904
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse