MS SQL Linked Server errors on VARCHAR bigger than 8000 - Forum - Community Groups - Progress Community

MS SQL Linked Server errors on VARCHAR bigger than 8000

 Forum

MS SQL Linked Server errors on VARCHAR bigger than 8000

This question is not answered

I added the database as a Linked Server in MS SQL, so I can query and use SSMS for all my queries.

It's a simple query but the tables has a field with VARCHAR(13255), when the limit in MSSQL is 8000. I cannot find a way to cast, convert, truncate or anything. The query only works if I leave the field out.



Msg 7341, Level 16, State 2, Line 43
Cannot get the current row value of column "[PICASQA].[PICAS].[PUB].[crop_note].cn_text" from OLE DB provider "MSDASQL" for linked server "PICASQA".
OLE DB provider "MSDASQL" for linked server "PICASQA" returned message "Requested conversion is not supported.".

All Replies
  • Hi,

    You can try Authorized Data Truncation (-SQLTruncateTooLarge OUTPUT) if you use OpenEdge latest version

    documentation.progress.com/.../index.html

  • Which Progress DataDirect ODBC driver are you using? This information is not clear from the post.
     
    But most of our drivers do support MaxVarcharSize (MVS) Connection option. You can add this option in your ODBC DSN with MaxVarcharSize=8000. With this option driver would report the VARCHAR field size as 4000 in column Metadata as well as the result set metadata and SQL Server Linked Server will be able to work with the queries which include this field.
     
    Please note this might cause data truncation in cases were actual data is longer than 8000 characters. Also, though most of the ODBC driver has this option, it’s not there for all of them, so the answer is still subject to the driver you are using.
    Hope this helps.
     
    Regards,
    Avadhoot
     
    Thanks and Regards,

    Avadhoot Kulkarni
    Lead Principal Software Engineer
    Progress Software Development Private Ltd.
  • Is that something I can configure on the MS SQL Server Linked Server? I don't much access to the configuration of the DB, I'm just consuming as a read-only client.

  • I'm using Progress OpenEdge 11.7 Driver, which was installed by our system developer. I've asked them about this issue but they didn't have many suggestions.

    Where can I set the MVS option on the ODBC? The only place I can think of is on the Advance\Extended Options. See screenshot below.

    Thanks!

  • Sorry, this option is not implemented for OpenEdge driver.

    One approach I can suggest you is to, Create a Views with casting the long columns to VARCHAR (8000) and then use those views in your SQl Server Linked Server environment.

    Hope that helps!

    Thanks and Regards,

    Avadhoot Kulkarni
    Lead Principal Software Engineer
    Progress Software Development Private Ltd.
  • Enter "truncateTooLarge=on" or "truncateTooLarge=output" in the Extended Options field. Remember this setting is case sensitive. The parameter is connection specific and is remembered by SQL only during the connection session.

  •  Same result. I tried with ON, OUTPUT and ALL.

  • Hi,

    My recommendation was based on this article from ProKB:

    knowledgebase.progress.com/.../How-to-enable-Authorized-Data-Truncation-in-a-JDBC-or-ODBC-connection

    According to the information in this article this should work for OpenEdge starting from version 11.5.

    What version of the OpenEdge database do you have (not the ODBC driver version)?

    I think you need to contact Progress Technical Support for a more detailed investigation of your problem.

  • Well, the article says OpenEdge 11.5.x and 11.6.x, but we are on 11.7. Not sure if that means our version is included too.

  • Hi,

    The issue is indeed caused by the datatype mapping done by MS SQL studio for OpenEdge text fields allowing more than 4000 (unicode) or 8000 (non unicode) characters (as defined by SQL-WIDTH on the OpenEdge side).

    Those OpenEdge text fields are not mapped to a datatype on the MS Studio side that allow that many characters.

    The mapping from the OpenEdge character type is done to varchar by the MS SQL Server Management Studio those text fields should probably be mapped to varchar(max) on the Microsoft side for those large text fields.

    When I investigated a similar issue in the past I did not find an option to force this type of mapping to varchar(max) on the Microsoft side, Microsoft might be able to give more info about this, or implement that mapping as a product enhancement in MS SQL Server Management Studio.

    (this kind of mapping issue is not specific to OpenEdge)

    About the ODBC MaxVarcharSize (MVS) Connection option you could open an idea as described on

    knowledgebase.progress.com/.../P11255

    and provide a link to that idea in this communities thread so that this idea can be upvoted to get more attention from our product management team.

    About creating views to truncate the data to 8000 characters in a column the syntax should be something like this:

    CREATE VIEW "TestView" AS SELECT left('note_comment',8000) as note_comment from openquery([FormNote],'SELECT "note_comment" FROM PUB."crop_note" ') ;

    Lowering the SQL-Width of the column note_comment from 13255 to 8000 and using  -SQLTruncateTooLarge as described by Valeriy is also likely to work, the -SQLTruncateTooLarge option is available in OpenEdge 11.7 and can be used either as database startup parameter or as an ODBC option:

    knowledgebase.progress.com/.../Does-the-SQLTruncateTooLarge-parameter-have-to-be-used-with-a-primary-broker

    Hopes this help,

    Tinco