How to change the TEXT to VARCHAR(MAX) during Schema migration to MS SQL Server Database as TEXT is getting depreciated by Microsoft? - Progress Community

Loading

Skip to Main Content

How to change the TEXT to VARCHAR(MAX) during Schema migration to MS SQL Server Database as TEXT is getting depreciated by Microsoft?

The ntext, text, and image data types will be removed in a future version of SQL Server. Avoid using these data types in new development work, and plan to modify applications that currently use them.

Aug 1, 2024
Knowledge

Title

How to change the TEXT to VARCHAR(MAX) during Schema migration to MS SQL Server Database as TEXT is getting depreciated by Microsoft?

URL Name

how-to-change-the-text-to-varchar-max-during-schema-migration-to-ms-sql-server-database-as-text-is-getting-depreciated-by-microsoft

Article Number

000263469

Environment

Product:OpenEdge Version:11.7.x, 12.2.x, 12.8.x OS: All supported platforms Other:DataServer

Question/Problem Description

How to change the TEXT to VARCHAR(MAX) during Schema migration to MS SQL Server Database as TEXT is getting depreciated by Microsoft?
- Can the migration utility use VARCHAR(MAX) instead of TEXT, and is there a way for the migration utility to generate SQL schema in VARCHAR(MAX) instead of [TEXT] when the format is greater than 8000?
- Are there any plans to change/fix TEXT to VARCHAR(MAX) during migration if the format is greater than 8k?

Steps to Reproduce

Clarifying Information

Error Message

Defect Number

Enhancement Number

Cause

As per the current behavior whenever the ABL Format or MAX-WIDTH is greater than 8000 during the migration process the CHARACTER field at the ABL end will be converted as TEXT datatype at the SQL end.

As stated in this Microsoft documentation -> 'The ntext, text, and image data types will be removed in a future version of SQL Server. Avoid using these data types in new development work, and plan to modify applications that currently use them'

Resolution

As Microsoft has deprecated the legacy LOB types(text, ntext or image), so there is a plan to make the LOB types(varchar(max), varbinary(max)) as default. Even though there is a plan to adopt this change to the product it is recommended to log an enhancement request on the same.

For detailed information on how to submit an Idea, please refer to Knowledge Base article How to submit an enhancement request for a Progress product?.

This enhancement has not been implemented in the product. As an alternative use the workaround below.

Workaround

Manually convert the existing TEXT datatype to VARCHAR(MAX) at the SQL database end and then update the Schema definitions at the Progress end using the "Update/Add Table Definitions..." option and then by selecting the tables/objects in which schema changes have been performed.

As precision and scale vary for varchar and text datatype using different ODBC drivers, care should be taken and use the dataserver schema created by the same ODBC driver. So, the same ODBC driver needs to be used to bring back the schema changes to Progress schema holder definitions.

Notes

Keyword Phrase

Last Modified Date

8/1/2024 3:07 AM

OpenEdge General DiscussionsOpenEdge DeploymentOpenEdge DevelopmentOpenEdge RDBMSOpenEdge ArchitectureOpenEdge Pro2

Drop Files

Upload FilesOr drop files

Disclaimer

The origins of the information on this site may be internal or external to Progress Software Corporation (“Progress”). Progress Software Corporation makes all reasonable efforts to verify this information. However, the information provided is for your information only. Progress Software Corporation makes no explicit or implied claims to the validity of this information.

Any sample code provided on this site is not supported under any Progress support program or service. The sample code is provided on an "AS IS" basis. Progress makes no warranties, express or implied, and disclaims all implied warranties including, without limitation, the implied warranties of merchantability or of fitness for a particular purpose. The entire risk arising out of the use or performance of the sample code is borne by the user. In no event shall Progress, its employees, or anyone else involved in the creation, production, or delivery of the code be liable for any damages whatsoever (including, without limitation, damages for loss of business profits, business interruption, loss of business information, or other pecuniary loss) arising out of the use of or inability to use the sample code, even if Progress has been advised of the possibility of such damages.

Loading

How to change the TEXT to VARCHAR(MAX) during Schema migration to MS SQL Server Database as TEXT is getting depreciated by Microsoft? - Progress Community