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.