5

In SQL Management Studio for a local database you can pressCTRL+0 to set a cell value to null

With SQL Azure this doesn't work with the following error message:

enter image description here

I submitted a bug here: https://feedback.azure.com/forums/908035-sql-server/suggestions/35683135-add-ctrl-0-back-for-sql-azure-in-smss

As no one is voting this up I have the feeling there is another way to set the value to null, and I'm just using some legacy path no one cares about.

To be clear:

  • I strongly prefer to not write every time a SQL statement, because there will be a time that I forget to write the where clause before running it, corrupting all my data.
  • I also don't like to write a CMS everytime I have to set something to null.

Any workarounds for setting an individual cell to null?

For people not believing me:

CREATE TABLE [dbo].[NewsSource](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Name] [nvarchar](120) NOT NULL,
    [Thumbnail] [nvarchar](150) NULL,
    [RssFormatId] [int] NULL,
    [Active] [bit] NOT NULL,
    [Interval] [int] NULL,
    [LastStarted] [datetime2](7) NOT NULL,
    [LastSuccess] [datetime2](7) NOT NULL,
    [TopicId] [int] NULL,
 CONSTRAINT [PK_NewsSource] PRIMARY KEY CLUSTERED

As for some reason I'm not being believed. To be clear: for me it's very easily reproducable.

  1. Create any table with a nullable column on SQL Azure.
  2. Set the column to any value.
  3. Use SMSS to try to set the value of this column to null through CTRL+0
  4. 100% of the times I'm getting the error message attached.

If this is really rare it could mean I have somehow a corrupted SMSS.

Version numbers

  • Microsoft SQL Azure (RTM) - 12.0.2000.8 May 2 2019 20:11:13 Copyright (C) 2019 Microsoft Corporation
  • SQL Server Management Studio 15.0.18040.0
  • Microsoft Analysis Services Client Tools 15.0.900.156
  • Microsoft Data Access Components (MDAC) 10.0.17763.1
  • Microsoft MSXML 3.0 6.0
  • Microsoft Internet Explorer 9.11.17763.0
  • Microsoft .NET Framework 4.0.30319.42000
  • Operating System 6.3.17763

To show that the normal manual Update statement succeeds:

enter image description here

UPDATE NewsSource
SET Thumbnail = null
WHERE Id = 17

UPDATE

As some people mention they don't have this problem. I have tried reinstalling all elements from SQL Management Studio and SQL Server and I still have this issue.

Dirk Boer
  • 8,522
  • 13
  • 63
  • 111
  • You can't store a NULL in a non-nullable column. SSMS is just a client tool, no different than your own code or any other application. – Panagiotis Kanavos May 22 '19 at 11:24
  • This isn't a bug at all, unless the table schema specifies that the column is *nullable*. I highly doubt that. This is such a humongous bug that people would have noticed – Panagiotis Kanavos May 22 '19 at 11:25
  • It's not a bug, that's why no one is up voting it. It would be like raising a bug because SQL Server isn't allowing you to store the value `'one'` in an `int` column. `'one'` is a literal string, it's not an integer. – Thom A May 22 '19 at 11:25
  • Hi @PanagiotisKanavos, this is a nullable column of course. – Dirk Boer May 22 '19 at 11:26
  • @DirkBoer no, it isn't, look at the error "Cell does not allow NULL". – Thom A May 22 '19 at 11:26
  • @DirkBoer how do we know that? And how come no other customer noticed that? You haven't posted *any* code, table schema or ways to reproduce the problem. It's far more likely that you're targeting the wrong table or column, or that the column is *NON-nullable* by mistake – Panagiotis Kanavos May 22 '19 at 11:27
  • Hi @PanagiotisKanavos, I updated the screenshot. – Dirk Boer May 22 '19 at 11:27
  • That picture doesn't mean anything. Especially as the error says row 16 column 3, and you appear to have highlighted row 15, column 2. Post the DDL for your table and show us the rowset you're trying to `INSERT` as formatted `text` (or an `INSERT` statement). – Thom A May 22 '19 at 11:28
  • @DirkBoer screenshots aren't code, they can't be used to reproduce anything. They don't contain table creation statements. That screenshot doesn't say *anything* more than the messagebox anyway - you tried to edit column and set a NULL, but the **server** denied this because the column isn't nullable – Panagiotis Kanavos May 22 '19 at 11:29
  • @DirkBoer please don't post any more screenshots. Post the *table creation statements* as **text**. Post an `UPDATE` statement that actually updates the column you want. *That* will allow us to try and reproduce the problem. – Panagiotis Kanavos May 22 '19 at 11:31
  • @PanagiotisKanavos, it's already in there now. The update statement is not there - because it's about the *interface of SMSS*. I can't see what update stament they execute - at least not how I know. – Dirk Boer May 22 '19 at 11:33
  • @Lamu, it is row 15 - because there is now row with Id 5 (has been deleted in the past). – Dirk Boer May 22 '19 at 11:34
  • @DirkBoer what is? You haven't posted anything. Nothing that can be used to reproduce the problem. You claim that you're the only one of possibly hundreds of thousands of programmers that has encountered this error. If that's true, only **YOU** can even reproduce the problem. – Panagiotis Kanavos May 22 '19 at 11:35
  • @PanagiotisKanavos, chill out man, no need for attacking me. I have this problem. Maybe it is a corrupt SMSS installation, I'm not claiming anything else. Just looking for a way to fix it. – Dirk Boer May 22 '19 at 11:36
  • I have provided the table. For me it's with every nullable column I try to set to *NULL* in SMSS. If the answer is "You have a corrupt SMSS" then I'm perfectly fine with that and going to look for that further. – Dirk Boer May 22 '19 at 11:39
  • @DirkBoer requests for information aren't attacks. I won't go and create a new Azure SQL database to test an improbable claim though, one that doesn't appear in local databases and hence, can't be due to the SSMS interface. It's far more likely that there's a *different* reason for the error, like a trigger that tried to put a NULL to some other column. – Panagiotis Kanavos May 22 '19 at 11:40
  • Hi @PanagiotisKanavos, thanks for letting me know. I hope someone has a (test) SQL Azure database so they can test it without little effort. So I know if it's just my machine or something else. I have tried on my (ex)colleague's machine a while back and they had the same problem back then - can't ask now anymore. – Dirk Boer May 22 '19 at 11:44
  • I'm having the same issue. The exact same database deployed to Azure does not allow typing in NULLs in nullable columns. The same row works just fine in local SQL server instance. Interesting - if I delete entire row and then modify it in my local server to have the NULL and then copy over to Azure, it allows pasting it. Only editing to NULL in a cell does not work. – JustAMartin Nov 30 '21 at 14:49
  • I have the same problem in my local database. I have 2 tables Account and Group, both have login field, in group everything works properly, but in Account I can set NULL only through Update. UPD. Restarting SSMS helped – Nuril Aug 27 '22 at 07:41

1 Answers1

2

I could not reproduce this.

  • When I press CTRL-0 in a nullable field, it enters a NULL
  • When I press CTRL-0 in a non nullable field, it has the exact error message that was posted originally

SSMS version

Microsoft SQL Server Management Studio                      14.0.17289.0
Microsoft Analysis Services Client Tools                        14.0.1016.283
Microsoft Data Access Components (MDAC)                     10.0.17763.1
Microsoft MSXML                     3.0 6.0 
Microsoft Internet Explorer                     9.11.17763.0
Microsoft .NET Framework                        4.0.30319.42000
Operating System                        6.3.17763

SQL Azure version

Microsoft SQL Azure (RTM) - 12.0.2000.8   May  2 2019 20:11:13   Copyright (C) 2019 Microsoft Corporation

To be sure, I suggest you run an UPDATE and see if you get the same error

Nick.Mc
  • 18,304
  • 6
  • 61
  • 91
  • Hi @NickMcDermaid, I guess because people can't reproduce this issue they assume I might be crazy. Of course it works with the update - that is the workaround that I'm doing at the moment. To clarify I have a platform with +120.000 registered users and +20 millions views per year, I know how SQL works :) – Dirk Boer May 22 '19 at 13:10
  • As I've upgrated many times SQL Management Studio in the past (coming from 2012 and now having v15.0.18040.0) I now suspect some module is broken on my machine. Thanks for trying it out - I'll investigate further. – Dirk Boer May 22 '19 at 13:11
  • Glad to verify it for you. Sometimes it's difficult to tell how experienced the user is on here.... and even experienced users do dumb things! I think the only way to avoid that is to post the complete repro, that includes the UPDATE statement. Unfortunately I feel SSMS is a lot more unstable than it used to be. For completion, do you mind editing your question and dumping the SSMS version numbers? – Nick.Mc May 22 '19 at 13:15
  • Hi Nick, I've updated the question with the version numbers. They almost all seem to be the same, except that I'm using a preview version now (but I also had the problem with previous versions - therefore I always try the latest in the hope that it finally works). Maybe I should try to uninstall everything slightly SMSS related and install from scratch again – Dirk Boer May 22 '19 at 13:21
  • Hi Nick, would you know a way to see which SQL statement SSMS is executing in the background? Like extensive logging or anything like that. – Dirk Boer May 22 '19 at 13:23
  • Actually that would be very enlightening. You used to be able to see use SQL Profiler. Not sure if that works against Azure though – Nick.Mc May 22 '19 at 13:36
  • And really for completeness, not that I don't believe you, can you add your `UPDATE` statement to your question? – Nick.Mc May 22 '19 at 13:38
  • Hi Nick, I've added the Update statement. BTW - I have a strong feeling it goes wrong before the UPDATE statement - but that is has to do with the converter logic in SSMS that takes the string (from the user input) and needs to convert it to the target type (i.e. an int or (nullable) string). Based on the type of error message (it refers to .NET types) – Dirk Boer May 22 '19 at 13:49
  • @Nick.McDermaid SQL Server Profiler was replaced by XEvents. [XEvents will work](https://blogs.msdn.microsoft.com/azuresqldbsupport/2018/03/13/extended-events-capture-step-by-step-walkthgrough/) even if the profiler doesn't. – Panagiotis Kanavos May 22 '19 at 13:51
  • I briefly fiddled with XEvents because yes that’s the tool you’re meant to use but I found it difficult. I see it has a wizard now so maybe I’ll give it another go. – Nick.Mc May 22 '19 at 14:09