Sql-server – EDIT TOP 200 ROWS – conversion error

sql serversql-server-2016ssms

I have a user who is working with a new manufacturing SCADA platform. He has a simple table with the following design:

Column Name       DataType     AllowNulls
identkey (PK)        INT           NO
Move                 INT           YES
Wind                 FLOAT         YES
Traverse             FLOAT         YES

This is the T-SQL DDL for the table:

CREATE TABLE dbo.tbl_Rv8WindProfile
( 
    identkey int IDENTITY(1,1) NOT NULL
    , [Move] int NULL
    , Wind float NULL
    , Traverse float NULL
    , CONSTRAINT PK_tbl_Rv8WindProfile 
        PRIMARY KEY CLUSTERED (
             identkey ASC 
             )
        WITH (
            PAD_INDEX = OFF
            , STATISTICS_NORECOMPUTE = OFF
            , IGNORE_DUP_KEY = OFF
            , ALLOW_ROW_LOCKS = ON
            , ALLOW_PAGE_LOCKS = ON
            ) ON [PRIMARY]
) ON [PRIMARY];
GO

He currently has only 5 rows of data in the table. In the past, he has right-clicked and selected EDIT TOP 200 ROWS and manually changed the value of the column "Move" by entering an integer via the numeric keypad on his keyboard. It has worked (as he says) for years.

In the past few weeks, he has been getting an error which reads:

No row was updated.
The data in row 5 was not committed.
Error Source .Net SQLClient Data Provider.
Error Message: Conversion failed when converting the varchar value 'Move' to data type int.
Correct the errors and retry or press ESC to cancel the change(s).

If I edit the data using this SQL:

UPDATE dbo.tbl_xyzProfile
 SET [move] = 4
 WHERE [identkey] = 5

the data is updated.

If I use this query (with a character instead of an INT):

UPDATE dbo.tbl_Rv8WindProfile
 SET [move] = '5'  
 WHERE [identkey] = 5

It recognizes the conversion and it still works.

Things I've checked so far:

  • I've double-checked and it does not appear that MOVE is a reserved word.
  • It doesn't appear that he has updated his Microsoft SQL Server install in the past few weeks.
  • It works in some tables for him, but not others and I cannot see any pattern which would explain why.

He is editing the integer data in column named MOVE. In the example shown here, he is trying to change row 4 (identkey = 4) from a integer value of 4 to 6.

enter image description here

I created the table in another database and replicated the data. It also gave me the same error. Then I renamed the column MoveZ and immediately, it let me modify the integers through the EDIT TOP 200 ROWS option.

I did look at the T-SQL reserved words, ODBC reserved words, Future reserved words and C# reserved words and MOVE doesn't appear on any of the lists. I don't find it on any .NET reserved words list either.

Best Answer

Using XEvents Trace, I can see SQL Server Management Studio is getting confused around the [move] column.

The DML query it uses to update the row actually works, however the query it issues to retrieve the updated row looks like:

DECLARE @Param1 int, @Param2 float,@Param3 float;
SET @Param1 = 1;
SET @Param2 = 2;
SET @Param3 = 3;

SELECT TOP (200) identkey, Move, Wind, Traverse 
FROM tbl_Rv8WindProfile 
WHERE ('Move' = @Param1) 
    AND (Wind = @Param2) 
    AND (Traverse = @Param3);

This looks like a bug in SQL Server Management Studio (SSMS). I tested this with SSMS 17.4, SSMS 2016, SSMS 2012; all of them exhibit this behavior. SSMS is wrapping the [move] column with single-quotes instead of square-brackets or double-quotes as would be expected.

Interestingly, SSMS 2008 R2 won't even open the "Edit top 200 rows" window. It raises an error-dialog containing the text "Invalid prefix or suffix characters (MS Visual Database Tools)".

FYI, the INSERT statement used by SSMS to insert new rows is:

DECLARE @Move int, @Wind float,@Traverse float;
SET @Move = 1;
SET @Wind = 2;
SET @Traverse = 3;

INSERT TOP (200) 
INTO tbl_Rv8WindProfile(Move, Wind, Traverse) 
VALUES (@Move, @Wind, @Traverse)

The INSERT and subsequent SELECT statement do not appear to be executed in the same transaction; in my tests, I ended up with multiple rows in the table, one for each error I received. You may want to manually confirm the table contains the correct data.

Related Question