When bad error messages happen to good people

Over the years I have largely been amused by the variance in error messages that come out of SQL Server. Some are very verbose and some even border on provide too much information, but the ones that irk me are the ones that leave you scratching your head. I wanted to point out a few of these, and ask if you have any misleading or unhelpful error messages that you see a lot?

Msg 8152, Level 16, State 14, Line 5
String or binary data would be truncated.

What string or binary data? Could you be a bit more specific? This error message could be a lot more helpful. For example, it could tell me the column name that is rejecting a string value, the value that exceeded the length, and the data type of the column. Since the message could be bubbled up from a trigger or otherwise not related to the table in the statement I am executing, the schema name and table name would also be useful. Something like:

String or binary data would be truncated. The value "wow that was a bad one" exceeds the size of the column foo in table dbo.bar (nvarchar(15)).

This issue keeps getting deferred. In Connect #125347 (also see #339410), we were told "too late for 2005, we'll fix it in 2008!" and then, "too late for 2008, we'll fix in 2008 R2!" Now SQL Server 2017 is in our hands and it remains unfixed (ed.: fixed in 2016 SP2 CU6, 2017 CU12, 2019).

Msg 10735, Level 15, State 1, Line 3
Incorrect WHERE clause for filtered index 'c' on table 'dbo.splunge'.

What is incorrect about it? There are several restrictions with the use of filtered indexes, but I spent a bit of time trying to figure out which rule I had violated. This specific message comes from the following code:

CREATE TABLE dbo.splunge
(
    foo INT,
    bar INT
);
GO
CREATE INDEX c 
    ON dbo.splunge(foo, bar)
    WHERE foo + bar > 1;
GO

I have learned elsewhere that you apparently can't reference more than one column in the WHERE clause of a filtered index — but I certainly did not get this from the documentation. *Something* has to help me out here, no? This is documented in Connect #341891, but it is closed as "by design."

Msg 16943, Level 16, State 4, Line 59
Could not complete cursor operation because the table schema changed after the cursor was declared.

What table schema changed? This occurs on the line:

 FETCH NEXT FROM @cursor INTO @variable;

So it could be any of the table(s) mentioned in the DECLARE statement for the cursor, or any table referenced within work performed by the cursor. How do you start debugging this one? It turns out that the developer was lazy with the cursor declaration. Typically we use:

 DECLARE @cursor CURSOR 
  LOCAL FORWARD_ONLY STATIC READ_ONLY  
  FOR ...

But in this case, the developer simply used:

 DECLARE @cursor CURSOR
  FORWARD_ONLY 
  FOR ...

Changing it to our usual implementation made the error go away (though maybe it was just coincidence, since the error was intermittent). The only mention of this one I could find on Connect was in the comments for #278763.

Any error message that indicates a name without schema

This involves both the message itself, and the error header which sometimes indicates which module raised the error. I assume the change would be simple – just pass schema_name.object_name into the token, as opposed to just object_name. Some examples include:

Msg 16915, Level 16, State 1, Procedure y, Line 14
A cursor with the name 'c' already exists.

Msg 16905, Level 16, State 1, Procedure y, Line 19
The cursor is already open.

Msg 16916, Level 16, State 1, Procedure y, Line 21
A cursor with the name 'c' does not exist.

Msg 2714, Level 16, State 3, Procedure y, Line 3
There is already an object named 'y' in the database.

Now picture a case where I have tables dbo.y and foo.y, and stored procedures bar.y and splunge.y. While those aren't exactly optimal architecture choices, the system is making it even harder for me to correct them. I complained about this problem in Connect #525308.

System.Data.SqlClient
SqlException: An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.

I saw this error last week from a web app that was connecting to a SQL Server 2008 instance. The mention of "SQL Server 2005" in the error message had people scrambling, trying to figure out if someone "downgraded" their instance to 2005 without telling anyone, and when they were assured that this did not happen, looking through their config files to see if any of their connection strings had changed to point to an old server. It turned out to be a simple network error, but I think the error message wording could be improved to be version-agnostic (since it can't connect, it really has no knowledge of what version is on the other end).

While Buck told us via Connect that this was fixed in April of 2008 (see Connect #296637), I am still seeing it in the latest version of SQLNCLI.

Msg 0, Level 20, State 0, Line 0
A severe error occurred on the current command.
The results, if any, should be discarded.

There are dozens of Connect items reporting this nondescript error, and they are all raised by various circumstances (my favorites are #411154, #530712, #406332, and #480713). In all honesty, I envision the code in the engine goes something like this:

try { }
catch(e) 
{
  if (e.number IN (x,y,z)) 
  {
    // raise meaningful error
  }
  else 
  {
    // it must have been severe! Say something scary and generic!
  } 
}

Now in reality some of these errors are not as easy to fix as I'm making them out to be, and in some cases they aren't even in the engine but rather in external components. Still, I think they are some good examples of error messages that Microsoft as a whole could work on improving.

Aaron Bertrand

I am a passionate technologist with industry experience dating back to Classic ASP and SQL Server 6.5. I am a long-time Microsoft MVP, write at Simple Talk, SQLPerformance, and MSSQLTips, and have had the honor of speaking at more conferences than I can remember. In non-tech life, I am a father of two, a huge hockey and football fan, and my pronouns are he/him. If I've helped you out, consider thanking me with a coffee. :-)

14 Responses

  1. JOSEPH says:

    Me ayudo muchísimo en mi proyecto este articulo, gracias…

  2. Horrible Messages says:

    Mind if I use your image at my twitter? @horriblemessage

  3. AaronBertrand says:

    Here is another one,
    Msg 5074, Level 16, State 1, Line 43
    ALTER TABLE DROP COLUMN <foo> failed because one or more objects access this column.
    How about telling me at least one of the objects that accesses the column?  People go scrounging all over the place trying to figure out if this is a default constraint directly on the column, or a computed column, or a system default, or who knows what else…

  4. Madhivanan says:

    Regarding your first error message, it shoud tell us more informations like you get when you use Bulk Insert command
    Msg 4863, Level 16, State 1, Line 2
    Bulk load data conversion error (truncation) for row 5, column 2 (test_name).

  5. AaronBertrand says:

    Yes, that's a good one Brian, but it's not so much that the error message is poor – the parser should be smart enough to just ignore the semi-colon and treat it like the statement terminator on any other statement.  GO and GO; should both be valid batch separators IMHO.  🙂

  6. Brian Tkatch says:

    OK, not exactly related, but…
    GO 200;
    ——-
    A fatal scripting error occurred.
    Incorrect syntax was encountered while parsing go.
    ——
    I keep thinking: Go to hundred, Go directly to hundred, do not parse go, do not collect $200;

  7. Daniel Fountain says:

    Love this article.
    Popped a link to it on my blog also:
    http://www.cardboardcoder.com
    Dan

  8. RamiReddy says:

    Interesting post…  
    enjoyed the lines of "too late for 2005, we'll fix it in 2008!" and then, "too late for 2008, we'll fix in 2008 R2!"  Now the CTP of R2 is in our hands and it remains unfixed."
    and
    "I saw this error last week from a web app that was connecting to a SQL Server 2008 instance.  The mention of "SQL Server 2005" in the error message had people scrambling, trying to figure out if someone "downgraded" their instance to 2005 without telling anyone"
    Kids stuff……. 🙂

  9. Ranga Narasimhan says:

    Another message that drives me nuts is this:
    If you do this:
    drop table InvalidTable
    Msg 3701, Level 11, State 5, Line 1
    Cannot drop the table 'InvalidTable', because it does not exist or you do not have permission.
    "you do not have permission" – I am the sysadmin! If I do not have permission, who else has!
    That is just an example, I have seen this "you do not have permission" on several other cases. If a sysadmin is logged in, just do not include this text "you do not have permission" in the error message!

  10. Zack Jones says:

    // it must have been severe! Say something scary and generic!
    Ha, ha — thanks for the laugh. You bring up some very valid points. Hopefully they will be fixed one of these days. I've been bitten by the string or binary data would be truncated a few times and it is a pain to figure out the exact column that's causing the issue.

  11. Terry says:

    Go Aaron Go!!!!! I am absolutely ecstatic that you've been speaking up about this. MS tools are usually fantastically productive but when something goes wrong IT REALLY GOES WRONG!!!!!  Fixing misleading and vague error messages are key to boosting productivity just that much higher. This really is a pet peeve of mine. Thanks for this.

  12. AaronBertrand says:

    Good one Adam.  At least from the response it looks marginally hopeful that they may fix it in R2 or SQL 11.