One of SQL Server Enterprise Manager's many conveniences allows users to open a table in a grid layout and directly edit the values. This is particularly handy if you need to make a quick-and-dirty change to something in a relatively small table.

Enterprise Manager usually has no problems dealing with integer or small fixed-length text columns (i.e., varchar(32) or something similar). However, if you are dealing with a text, ntext or char/varchar, where the column could contain more than 1,024 characters, using Enterprise Manager as the editor may create problems. And, they might not be immediately visible, which can pose a threat to the integrity of the data you're editing.

Enterprise Manager has a maximum field size of 1,024 characters to edit any column regardless of the underlying data type. If you copy and paste a string longer than that into a field, it will be truncated, and the data you've supplied will be damaged. The table editor in Enterprise Manager isn't intended to be a full replacement for an editing application -- it's just a way to browse the data without much extra hassle.

There are a couple of ways around this problem when updating the table, neither of which involve using Enterprise Manager.

1. Use an UPDATE statement or a specifically designed stored procedure in the Query Analyzer. If your tables are well designed (i.e., they have some variety of unique identifiers on each row), it should not be hard

    Requires Free Membership to View

to write an UPDATE statement to do this on the fly. Pasting in long text should work exactly the same way.

2. Use a third-party product that will not truncate text fields, such as a Web interface to SQL Server.

Note: When you attempt to retrieve a column with more than 255 characters in Query Analyzer, it may be truncated. This is not due to a limitation in Query Analyzer per se, but a setting in its options that limits column sizes. If you want to change the maximum column width, go to Tools | Options | Results, and set the value in "Maximum characters per column."

About the author: Serdar Yegulalp is editor of the Windows Power Users Newsletter. Check it out for the latest advice and musings on the world of Windows network administrators -- and please share your thoughts as well!


More information from SearchSQLServer.com

  • Step-by-Step Guide: Ensuring data integrity for SQL Server
  • Tip: Preserving Unicode data integrity
  • Tip: Test for hardware problems with PHYSICAL_ONLY


  • This was first published in September 2005

    There are Comments. Add yours.

     
    TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

    REGISTER or login:

    Forgot Password?
    By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
    Sort by: OldestNewest

    Forgot Password?

    No problem! Submit your e-mail address below. We'll send you an email containing your password.

    Your password has been sent to:

    Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.