Article

How to adjust the SQL width of a field migrated with DataServer for MS SQL Server

« Go Back

Information

 
Article Number000037357
EnvironmentProduct: OpenEdge
Version: 10.x, 11.x
OS: All supported platforms
Other: MS SQL DataServer, ODBC DataServer
Question/Problem Description
How to adjust the SQL width of a field migrated with DataServer for MS SQL Server.

How to adjust the SQL width of a field migrated with DataServer for MS SQL Server, ensuring that the foreign database, schema holder and OpenEdge database use the same value.
Steps to Reproduce
Clarifying Information
OpenEdge database has been migrated to MS SQL Server via the "OpenEdge DB to MS SQL Server" utility.

The option "Width" in the "For field widths use" section of the migration tool, meaning that the SQL width setting of the field in the OpenEdge database would be used to define the width used in the MS SQL Server database.

After completing the migration, it was discovered that some fields had an incorrect SQL width, causing such problems as error 6182.
Error MessageYou tried to compare or to update a character field with a value longer than the maximum length. (6182)
Defect/Enhancement Number
Cause
Resolution
1.  Adjust the SQL Width setting in the original OpenEdge database 
  • Connect to the original OpenEdge database using Data Dictionary. 
  • Select the table(s) containing fields with incorrect Width values.
  • Go to Options > Adjust Field Width.
  • Set the Width of the field(s) in question to the required value. 

2.  Adjust the SQL Width setting in MS SQL Server database 
  • In the SQL Server database, select the appropriate table.
  • Right-click on it and select Design.
  • Choose the field in question and change the Length to match the value specified for SQL Width in Step #1.

3a. Re-pull the adjusted table definitions from MS SQL Server to the schema holder 
  • Connect to the schema holder database in Data Administration.
  • Select the MSS schema as the Working Database. 
  • Go to DataServer > MS SQL Server Utilities > Update / Add Table Definitions. 
  • Re-pull the modified table(s).

3b. Synchronize additional table information not stored in MS SQL Server, e.g. labels, description, etc., between the schema holder and the original OpenEdge database
  • Connect the original OpenEdge database to Data Administration. 
  • Go to DataServer > MS SQL Server Utilities > Schema Migration Tools > Adjust Schema. 
  • Enter the name of the OpenEdge database in the "Original OpenEdge DB" field.
  • Enter table name(s) in the "FIles to Compare" section and click OK. 
The OpenEdge database, schema holder database and MS SQL Server database should now be synchronized. 

Using DBTOOL may also be an option but the utility will only make changes to SQL Width if it is found to contain data whose length exceeds the current setting, e.g. SQL Width is 10 but the field contains 20 characters.

SQL Width is represented as MAX-WIDTH in a .DF file. Refer to Article 000011409, What is MAX-WIDTH or SQL_WIDTH in a df file?   
Workaround
Notes
Attachment 
Last Modified Date1/22/2018 11:08 AM


Feedback
 
Did this article resolve your question/issue?

   

Your feedback is appreciated.

Please tell us how we can make this article more useful. Please provide us a way to contact you, should we need clarification on the feedback provided or if you need further assistance.

Characters Remaining: 1025