THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Aaron Bertrand

Aaron is a Product Evangelist for SentryOne, makers of Plan Explorer and a performance monitoring and event management platform for the Microsoft Data Platform and VMware. He has been blogging here at sqlblog.com since 2006, focusing on manageability, performance, and new features, and also blogs at blogs.sentryone.com and SQLPerformance.com; has been a Microsoft MVP since 1997; tweets as @AaronBertrand; and speaks frequently at major conferences, user group meetings, and SQL Saturday events worldwide.

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 the CTP of R2 is in our hands and it remains unfixed.
 



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.
 
Published Thursday, February 04, 2010 11:27 AM by AaronBertrand

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Adam Machanic said:

February 4, 2010 11:50 AM
 

AaronBertrand said:

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

February 4, 2010 12:12 PM
 

Terry said:

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.

February 4, 2010 12:21 PM
 

Zack Jones said:

// 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.

February 4, 2010 1:10 PM
 

Ranga Narasimhan said:

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!

February 4, 2010 5:14 PM
 

RamiReddy said:

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....... :)

February 5, 2010 12:30 AM
 

Daniel Fountain said:

Love this article.

Popped a link to it on my blog also:

www.cardboardcoder.com

Dan

February 5, 2010 11:46 AM
 

Aaron Bertrand said:

Upgrading a database with read-only filegroups Earlier today, I complained that I should be able to upgrade

February 6, 2010 6:32 PM
 

Brian Tkatch said:

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;

February 8, 2010 11:37 AM
 

AaronBertrand said:

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.  :-)

February 8, 2010 11:43 AM
 

Brian Tkatch said:

February 8, 2010 1:03 PM
 

Madhivanan said:

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).

February 11, 2010 4:41 AM
 

AaronBertrand said:

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...

February 12, 2010 1:07 PM
 

Aaron Bertrand said:

Okay, I'm following the lead of Joe Webb ( blog | twitter ), who recently posted " My Most Popular Posts

February 3, 2011 5:05 PM
 

Horrible Messages said:

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

October 27, 2011 6:07 PM
 

JOSEPH said:

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

February 17, 2012 9:07 PM

Leave a Comment

(required) 
(required) 
Submit

About AaronBertrand

...about me...

This Blog

Syndication

Archives

Privacy Statement