SQL Server

Error in SSMS data editor when keyword is used in column name with BIT data type

There is still the same bug many years back starting from SSMS 2005. And yes, it is still not corrected in SSMS 2016. If you will create table where one of the columns has name SEND (keyword in SQL Server) and BIT data type, you are not able to edit table data using data editor. Let´s try it now.

Create sample table with one column of BIT datatype called SEND:

CREATE TABLE [dbo].[TestTable](
	[SEND] [bit] NOT NULL

Now go to SSMS and open data editor:


Then edit column value to insert True:


You can see that an error has occurred. It is not saying to much until you check SQL statements sent from SSMS to SQL engine:

exec sp_executesql N'INSERT TOP (200) INTO TestTable(SEND) VALUES (@SEND)',N'@SEND bit',@SEND=1

exec sp_executesql N'SELECT TOP (200) SEND FROM TestTable WHERE (''SEND'' = @Param1)',N'@Param1 bit',@Param1=1

The first statement has completed successfully. But the second one has failed because SEND is an keyword and it is treated as string value and cast to BIT data type has failed. Looks like the issue is that apostrophe is used instead of square brackets.

If we will try the same but instead of SEND we will use column name which isn’t a keyword, i.e. [AAA], following statement is generated and executed without an error:

exec sp_executesql N'SELECT TOP (200) AAA FROM TestTable WHERE (AAA = @Param1)',N'@Param1 bit',@Param1=1

This example is another plus point for the best practice to eliminated key/reserved words in development.

Leave a Reply

Your email address will not be published. Required fields are marked *