A severe error occurred on the current command - help
JP
statement to set a bit field to false where it was already NULL. Since this
wouldn’t work I begin updated the records 2 million at a time. But after a
while I couldn’t do that either
I finally decided to drop the column and re-add it, and making 0 be the
default value of the bit field and I would just write a script to recalculate
the records where it should be set to True.
I dropped the column successfully, but when I try to save the table with the
column added back, I get the same darn error. I tried warm booting the server
and I still get error. Anybody have any ideas whats going on? This is like
the most uninformative error message MS has ever come up with. They need to
be more descriptive. The table has about 20 million records in it. I know it
takes a while to update, but its not even taking 1 min for the error to come
up
JP
.NET Software Developer
Adi
Can you post the error message that you got and the update statement
that you are trying to run? Without it it is very hard to know what
went wrong.
Adi
JP
SET adrLocked = 0
WHERE (adrLocked IS NULL)
adrLocked is a bit field
when that wouldnt work I tried updating in batches. This statement worked,
but as I did more and more batches, even they started to error out
UPDATE tblNamesAddress
SET adrLocked= 0
WHERE addressID>12000000 AND (adrLocked IS NULL)
I even put index on adrLocked. It will run anywhere for 15 sec to 1 minute
before giving me the error
--
JP
.NET Software Developer
Adi
>
> - Show quoted text -
Can you also post the error message that you get? Is it timeout?
deadlock? something else?
Adi
JP
A severe error occurred on the current command. The results, if any, should
be discarded.
Update:
If I leave the column to allow nulls it will add the column fine
If I disallow nulls and set the default value to 0, then I get this error
Same error applies of I run the update statemenmt to sent the value via SQL
Adi
The error that you wrote is not the error that SQL Server sent. It is
an error that you get from the application. SQL Server might be the
cause but the error from the application doesn't send vital
information such as SQL Server's error number or SQL Server's error
description. There could be few things that went wrong. For example
- timeout, deadlock, permission problem, table's corruption etc'.
Without getting the error message and error number that you get from
SQL Server, it is impossible to know what went wrong. Instead of
running the statement from the application, try to run it from query
analyzer. This will show you the exact error that happened in the
server. After getting the error number and message it will be
possible to address the problem.
Adi
Erland Sommarskog
> - Unable to modify table.
> A severe error occurred on the current command. The results, if any,
> should be discarded.
>
> Update:
>
> If I leave the column to allow nulls it will add the column fine
> If I disallow nulls and set the default value to 0, then I get this error
>
> Same error applies of I run the update statemenmt to sent the value via
> SQL
to my newsserver. But I will try to straighten out a few things.
The error message "A severe error occurred on the current command. The
results, if any, should be discarded." means that something very bad
happened. And most of all, something that was not forseen. My experience
is that this error indicates that one of two things:
o A bug in SqlClient (the API that Mgmt Studio uses).
o A severe problem in SQL Server, which forces SQL Server to close the
connection.
From what I can understand of what you are doing, it looks like the latter
is happening to you. The severe problem would be an access violation,
stack overflow, assertion error etc after which continued execution would
not be safe, which is why SQL Server kills the process on the spot. The
underlying reason for this accident could be a bug in SQL Server, but
it could also be corruption in the database which would indicate a hardware
problem. If you examine the SQL Server errorlog, you should find a stack
dump that corresponds to your failed operation.
In any case, you should run DBCC CHECKDB on the database, or at least
DBCC CHECKTABLE on the table to see if there is any corruption.
You can also try the ALTER TABLE command from SQLCMD and OSQL, which are
command-line utilities. They use different APIs (OLE DB and ODBC), and
you may get different error messages from these that could more hints.
And, if the problem really is a bug in SqlClient, you may even be able
to run the command successfully.
Finally, as for how to change the table definition, never use the Table
Designer in SSMS. There are several very serious bugs with it, and you
might cause even bigger mess than using this tool. The reason you don't
get the error when you say "Allow null", is that in this case SSMS will
use ALTER TABLE to add the column which is a pure metadata operation.
(Assuming that you put the column last in the table.) But if you add a
non-nullable column with a default value, SSMS will rename the old
table and create a new table and copy data over, even if there is an
ALTER TABLE command available.
--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx