MS SQL Linked Server errors on VARCHAR bigger than 8000

Posted by mstock.speedling.com on 11-Sep-2019 21:56

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

Posted by Valeriy Bashkatov on 12-Sep-2019 07:51

Hi,

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

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

Posted by Avadhoot Kulkarni on 12-Sep-2019 08:52

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
 

Posted by mstock.speedling.com on 13-Sep-2019 14:38

[mention:819ac0812c524a3b9718403d142f1ba2:e9ed411860ed4f2ba0265705b8793d05] 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.

Posted by mstock.speedling.com on 13-Sep-2019 14:41

[mention:60cbe3b6c5f34d9ebd76ac9eefe0d762:e9ed411860ed4f2ba0265705b8793d05] 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!

Posted by Avadhoot Kulkarni on 16-Sep-2019 06:35

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!

Posted by Valeriy Bashkatov on 16-Sep-2019 07:29

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.

Posted by mstock.speedling.com on 16-Sep-2019 20:50

[mention:819ac0812c524a3b9718403d142f1ba2:e9ed411860ed4f2ba0265705b8793d05] Same result. I tried with ON, OUTPUT and ALL.

Posted by Valeriy Bashkatov on 17-Sep-2019 07:22

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.

Posted by mstock.speedling.com on 17-Sep-2019 13:17

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.

Posted by Tinco on 18-Sep-2019 14:27

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

This thread is closed