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.".
Hi,
You can try Authorized Data Truncation (-SQLTruncateTooLarge OUTPUT) if you use OpenEdge latest version
[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.
[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.
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!
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.
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:
Hopes this help,
Tinco