|
dev
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
SQL 2005 Management Studio can't edit text rows with > 4000 characI cannot edit any rows in SQL Server 2005 Management Studio (SSMS) that have
a text-type field that contains more than 4000 characters in it. If I try to edit such a row, I get an error saying "String or binary data would be truncated". When I look at the query SSMS generates to do the edit, it uses the nvarchar(max) for the text-type parameters, which should work fine. I also check the TEXTSIZE field, and it is set to 2147483647, so everything should work. Despite this, it does not work. Any ideas? Can Microsoft please confirm this works or does not? It seems ironic that in the update that extends the support of nvarchar to support the MAX syntax, SSMS doesn't handle such things properly! I have found one reference that says it does not work: http://forums.asp.net/thread/1432762.aspx THanks! asanford (asanford2000_at_hotmail.com@newsgroups.nospam) writes:
> I cannot edit any rows in SQL Server 2005 Management Studio (SSMS) that Well, it kind of works actually. That is, data is inserted/updated. The> have a text-type field that contains more than 4000 characters in it. > If I try to edit such a row, I get an error saying "String or binary > data would be truncated". When I look at the query SSMS generates to do > the edit, it uses the nvarchar(max) for the text-type parameters, which > should work fine. I also check the TEXTSIZE field, and it is set to > 2147483647, so everything should work. Despite this, it does not work. > Any ideas? Can Microsoft please confirm this works or does not? It > seems ironic that in the update that extends the support of nvarchar to > support the MAX syntax, SSMS doesn't handle such things properly! place where things goes wrong is when Mgmt Studio tries to read back the data. It then issues something like: exec sp_executesql N'SELECT x, b FROM gurka WHERE (x = @PARAM1) AND (b LIKE @PARAM2)',N'@PARAM1 int,@PARAM2 nvarchar(max)', @PARAM1=1, @PARAM2=N'Concatenating row values in ...' Books Online says about @patter, the right-hand site operator of LIKE: Is the specific string of characters to search for in match_expression, and can include the following valid wildcard characters. pattern can be a maximum of 8,000 bytes. So that is bound to fail. I guess the correct behaviour of MgmtStudio would be to prevent you from entering more than 4000 characters. But, there is a simple workaround! Actually, I would not even call it a workaround. I would call it best practice. Define a primary key for your table, and then Mgmt Studio will use that to refresh the grid and not guess with LIKE. -- Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx Hi,
Thanks for using Microsoft Managed Newsgroup. From you description, I understand that: You could not edit a row with a text-type field which contains more that 4000 characters. The error message was "String or binary data would be truncated". If I have misunderstood, please let me know. This issue should be a in-row behavior of tables with varchar(max), nvarchar(max), varbinary(max), xml, text, ntext, or image columns. Generally we cannot see the data stored in a text field. If we use SQL Enterprise Manager or Management Studio to explicitly edit the text-type field, the editable length is limited. In SQL Server no matter 2000 or 2005, the default maximum size that can be stored in a row for a BLOB (binary large object: text, ntext, or image data) is 256 bytes. So by default an edit with a large data over 256 bytes will fail. In SQL Server 2005, you can use two methods to manually edit the field: 1. Change the data type to varchar(max)/nvarchar(max); 2. Use the stored procedure sp_tableoption to enable "text in row". When the "text in row option" is enabled by using "exec sp_tableoption 'your table name','text in row',7000", the text-type field can support maximum 7000 bytes stored in a row. This method is also available for SQL Server 2000. For more information, you may refer to SQL Server 2005 Books Online regarding sp_tableoption. I can understand your concern is that since TEXTSIZE is set to 2147483647 you should be able to edit the field. Generally I appreciate your understanding that such design may consider the data storage optimization and the compatibility with SQL Server 2000. For large data objects, it is recommended that you use database providers such as ADO/ADO.NET to update the fields. If you are very concerned with this issue, I recommend that you give Microsoft feedback via the link: http://connect.microsoft.com Your suggestions will be routed to SQL team so that the feature will be improved in the next release. If you have any other questions or concerns, please feel free to let me know. It is my pleasure to be of assistance. Sincerely, Charles Wang Microsoft Online Community Support ====================================================== When responding to posts, please "Reply to Group" via your newsreader so that others may learn and benefit from this issue. ====================================================== This posting is provided "AS IS" with no warranties, and confers no rights. ====================================================== Thanks for your reply.
Some of what you wrote doesn't make sense to me, since: 1) in SQL 2000 (visual studio tools and I think enterprise manager), I could delete rows that have text fields greater than 4000 2) in SSMS (2005), I cannot do the same thing. I cannot delete the row, and I cannot update ANY fields in a row that contain a text field > 4000 characters - for example, if my row has two fields, and int (primary key) and a text field, if I edit the int field, it fails. I even captured the update query SSMS is generating, and it is NOT trying to edit the text field, the problem is the where clause that converts the text field to an nvarchar(max) to do a like operator (using an optimistic concurancy pattern.) 3) Furthermore, I can always view the full value of the text field in SSMO, regardless of the size (which you say I should not be able to do). If the text field contains just under 4000 characters (say, 3999), it works fine - I can update it. It just when the field pops over the 4000 threshold that I can only view it, but not update it (or any other fields in that row.) Yes, I did verify that everything works if I change the column type from text to nvarchar(max). THe issue is that I have some old dbs that have the text column, and I can no longer easily work with them in 2005 like I could in 2000. I think this is a bug in SSMS Show quote "Charles Wang[MSFT]" wrote: > Hi, > Thanks for using Microsoft Managed Newsgroup. > > From you description, I understand that: > You could not edit a row with a text-type field which contains more that > 4000 characters. The error message was "String or binary data would be > truncated". > If I have misunderstood, please let me know. > > This issue should be a in-row behavior of tables with varchar(max), > nvarchar(max), varbinary(max), xml, text, ntext, or image columns. > Generally we cannot see the data stored in a text field. If we use SQL > Enterprise Manager or Management Studio to explicitly edit the text-type > field, the editable length is limited. > > In SQL Server no matter 2000 or 2005, the default maximum size that can be > stored in a row for a BLOB (binary large object: text, ntext, or image > data) is 256 bytes. So by default an edit with a large data over 256 bytes > will fail. In SQL Server 2005, you can use two methods to manually edit the > field: > 1. Change the data type to varchar(max)/nvarchar(max); > 2. Use the stored procedure sp_tableoption to enable "text in row". When > the "text in row option" is enabled by using "exec sp_tableoption 'your > table name','text in row',7000", the text-type field can support maximum > 7000 bytes stored in a row. This method is also available for SQL Server > 2000. > For more information, you may refer to SQL Server 2005 Books Online > regarding sp_tableoption. > > I can understand your concern is that since TEXTSIZE is set to 2147483647 > you should be able to edit the field. Generally I appreciate your > understanding that such design may consider the data storage optimization > and the compatibility with SQL Server 2000. For large data objects, it is > recommended that you use database providers such as ADO/ADO.NET to update > the fields. > If you are very concerned with this issue, I recommend that you give > Microsoft feedback via the link: > http://connect.microsoft.com > Your suggestions will be routed to SQL team so that the feature will be > improved in the next release. > > If you have any other questions or concerns, please feel free to let me > know. It is my pleasure to be of assistance. > > Sincerely, > Charles Wang > Microsoft Online Community Support > > ====================================================== > When responding to posts, please "Reply to Group" via your newsreader > so that others may learn and benefit from this issue. > ====================================================== > This posting is provided "AS IS" with no warranties, and confers no rights. > ====================================================== > > > > > Hi Asanford,
Could you please mail me (chang***@microsoft.com) a test dabase backup file so that I can reproduce your issue? Per my test, I can delete or update a row with a text field over 4000 bytes in SQL Server Management Studio. You may only export one table to a test database, back up the database, compress the .bak file and mail it to me. I will perform further research on it and let you know the result as soon as possible. If that is indeed the issue, I will submit it to our SQL team. Your suggestions and feedbacks are greatly appreciated. Sincerely yours, Charles Wang Microsoft Online Community Support I've emailed you the backup you requested.
BTW, I think Erland Sommarskog is right in that it is likely the LIKE operator's 8000 byte limit. The problem is that SSMO chooses to use this operator for all text columns, regardless of whether the size exceeds the LIKE operator's limits. Thanks for your help. Show quote "Charles Wang[MSFT]" wrote: > Hi Asanford, > Could you please mail me (chang***@microsoft.com) a test dabase backup file > so that I can reproduce your issue? > Per my test, I can delete or update a row with a text field over 4000 bytes > in SQL Server Management Studio. > > You may only export one table to a test database, back up the database, > compress the .bak file and mail it to me. > I will perform further research on it and let you know the result as soon > as possible. > > If that is indeed the issue, I will submit it to our SQL team. Your > suggestions and feedbacks are greatly appreciated. > > Sincerely yours, > Charles Wang > Microsoft Online Community Support > > asanford (asanford2000_at_hotmail.com@newsgroups.nospam) writes:
> I even captured the update query SSMS is generating, and it is NOT Yes, that just seem plain wrong to me. Why not use a simple =? LIKE looks> trying to edit the text field, the problem is the where clause that > converts the text field to an > nvarchar(max) to do a like operator > (using an optimistic concurancy pattern.) like a recipe for failure. Note the least with @param1 LIKE '1'... Of course, with text/ntext/image, it would not work with = neitehr, but it would work with the MAX data types. -- Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx Yes, SSMS uses LIKE for text types, which I think is the problem when the
text field is bigger than the 8000 byte limit of the LIKE operator, which you pointed out (thanks!) BTW, SSMS does use the equals operator for varchar types, as you thought it should. I guess it can't with text fields. In any regard, maybe SSMS shouldn't put a where clause when the text field is too big to allow the LIKE operator. Also, the WHERE clause is added even if you have a properly defined primary key (I had the PK on the integer field.) Thanks for your help! Show quote "Erland Sommarskog" wrote: > asanford (asanford2000_at_hotmail.com@newsgroups.nospam) writes: > > I even captured the update query SSMS is generating, and it is NOT > > trying to edit the text field, the problem is the where clause that > > converts the text field to an > nvarchar(max) to do a like operator > > (using an optimistic concurancy pattern.) > > Yes, that just seem plain wrong to me. Why not use a simple =? LIKE looks > like a recipe for failure. Note the least with @param1 LIKE '1'... > > Of course, with text/ntext/image, it would not work with = neitehr, but > it would work with the MAX data types. > > -- > Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se > > Books Online for SQL Server 2005 at > http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx > Books Online for SQL Server 2000 at > http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx > asanford (asanford2000_at_hotmail.com@newsgroups.nospam) writes:
> Yes, SSMS uses LIKE for text types, which I think is the problem when Well, neither = nor LIKE will work. The text data type is a pain, simply.> the text field is bigger than the 8000 byte limit of the LIKE operator, > which you pointed out (thanks!) > BTW, SSMS does use the equals operator for varchar types, as you thought Ah, I shot too quick. If you insert, it works OK, because they use only> it should. I guess it can't with text fields. In any regard, maybe > SSMS shouldn't put a where clause when the text field is too big to > allow the LIKE operator. Also, the WHERE clause is added even if you > have a properly defined primary key (I had the PK on the integer field.) the PK to retrieve the data. But it does not work with UPDATE, because they add the condition on the text column for optimistic locking. Someone though that LIKE should work when "=" does not - but they were wrong. There is still a possible way out. I added a timestamp column on the table. Since a timestamp column is updated each time a row is updated, Mgmt Studio could use that for optimistic locking, but it was not that smart. I'm not sure if want to call this a bug. You should not get a cryptic error message, but there does not really seem to be a way for Open Table to provide you a solution within the specs. Of course, you can always run a query yourself. -- Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx Hi Andy,
Thank you very much for your email. I have reproduced your issue. However I believe this issue is most likely a SSMS design issue which is caused by the .NET SQL client provider. It seems that by design the text and ntext types were both designed for supporting only 4000 characters if you use SSMS editor to edit the rows. Per my test, no matter text field or ntext field, you can fill in 4000 characters in the editor of "Open Table". However, if you directly execute the INSERT or UPDATE statements in SSMS, you can find that data with over 8000 bytes does not cause the issue at all. I have reported this issue to our SQL team and they will give me a confirmation on this issue. The process may need a long time, but I will let you know the result as soon as possible. If you have any other questions or concerns, please feel free to let me know. It is my pleasure to be of assistance. Sincerely yours, Charles Wang Microsoft Online Community Support Hi,
I had got the replies from our SQL team. For this behavior, they have begun to pay attention to it and will make appropriate decisions after the reviewing. I believe there will be a reasonable explanation in the next release. Appreciate your understanding on this. Thank you for using Microsoft Online Managed Newsgroup. If you have any other questions or concerns, please feel free to let me know. It is my pleasure to be of assistance. Charles Wang Microsoft Online Community Support  |
|||||||||||||||||||||||
Other interesting topics