When to avoid using Enterprise Manager

If you need to make a quick-and-dirty change to data in a relatively small table, SQL Server Enterprise Manager will get the job done. But if your column contains more than 1,024 characters -- take your editing needs elsewhere.

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 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

    Dig deeper on Microsoft SQL Server Performance Monitoring and Tuning

    Pro+

    Features

    Enjoy the benefits of Pro+ membership, learn more and join.

    0 comments

    Oldest 

    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:

    SearchBusinessAnalytics

    SearchDataCenter

    SearchDataManagement

    SearchAWS

    SearchOracle

    SearchContentManagement

    SearchWindowsServer

    Close