One of SQL Server Enterprise Manager's many conveniences allows users to open a table in a grid layout and directly...
By submitting your email address, you agree to receive emails regarding relevant topic offers from TechTarget and its partners. You can withdraw your consent at any time. Contact TechTarget at 275 Grove Street, Newton, MA.
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