How to get the error message rather than the code error

Hi,

After sending a request, I would get the possible error message.
Not the code @.@.error, nor the exact content of sysmessages, but the
message like it could be in the log file.

TIA,
TSalmTSalm (tsalm@.free.fr) writes:

Quote:

Originally Posted by

After sending a request, I would get the possible error message.
Not the code @.@.error, nor the exact content of sysmessages, but the
message like it could be in the log file.

"Sending a request", that sounds like you are issuing a call from a
client program. In that case you should be able to pick up the error
message. If you tell which client API you are using, I may even be
able to tell you how.

If you mean in a stored procedure, it depends on which version of SQL
Server you are on. If you are on SQL 2000, the answer is: you can't.

If you are on SQL 2005, you can use the new function error_message()
and its sisters: error_severity(), error_number(), error_state(),
error_procedure() and error_line(). But they only return data, if you
are in a CATCH handler, or a procedure called from a catch handler:

BEGIN TRY
-- Do something bad here
END TRY
BEGIN CATCH
SELECT error_message()
END CATCH

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

How to get rid of a corrupt record

I have a corrupt record in my database that I can't query on. Nor can I
delete or update. Running any of the Select, Update or Delete statements in
QA using the WHERE clause with ID = <the corrupt record ID> causes the
system to hang until it eventually times out.
How can I get rid of this record ?Are you getting any errors? Did you try running DBCC CHECKTABLE on that
table?
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
What hardware is your SQL Server running on?
http://vyaskn.tripod.com/poll.htm
"RB" <rakhi.bhatia@.verizon.net> wrote in message
news:%23O8P%23jWnDHA.2512@.TK2MSFTNGP09.phx.gbl...
I have a corrupt record in my database that I can't query on. Nor can I
delete or update. Running any of the Select, Update or Delete statements in
QA using the WHERE clause with ID = <the corrupt record ID> causes the
system to hang until it eventually times out.
How can I get rid of this record ?|||Rakhi
You could try running DBCC Checktable. You can run this
with parameters that can try to repair errors. See BOL for
details.
Hope this helps
John|||Have you tried:
DBCC CHECKTABLE ('tablename', REPAIR_ALLOW_DATA_LOSS)
...Most likely the problem is NOT a corrupt record, but rather a hanging
connection that has the record locked.
Run your query and check the "management" section to see if your connection
is blocked.
Bruce
"RB" <rakhi.bhatia@.verizon.net> wrote in message
news:%23O8P%23jWnDHA.2512@.TK2MSFTNGP09.phx.gbl...
> I have a corrupt record in my database that I can't query on. Nor can I
> delete or update. Running any of the Select, Update or Delete statements
in
> QA using the WHERE clause with ID = <the corrupt record ID> causes the
> system to hang until it eventually times out.
> How can I get rid of this record ?
>|||Hi,
Try running dbcc checktable on that table with Repair options after taking a
database backup or table backup (Select * into bckuptable from table). If it
fails again u can try the below steps:
1. Create a new table same as the corrupted table
2. Write a script to copy the data in batches excluding the corrupted data.
3. Rename the corrupted table to table_corrupt
4. Rename the new table to original name
5. Create all indexes for this new table
Thanks
Hari
MCDBA
"RB" <rakhi.bhatia@.verizon.net> wrote in message
news:#O8P#jWnDHA.2512@.TK2MSFTNGP09.phx.gbl...
> I have a corrupt record in my database that I can't query on. Nor can I
> delete or update. Running any of the Select, Update or Delete statements
in
> QA using the WHERE clause with ID = <the corrupt record ID> causes the
> system to hang until it eventually times out.
> How can I get rid of this record ?
>|||Thanks everyone. Will try out all these steps and report the results.
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:eq#M#3WnDHA.644@.TK2MSFTNGP11.phx.gbl...
> Hi,
> Try running dbcc checktable on that table with Repair options after taking
a
> database backup or table backup (Select * into bckuptable from table). If
it
> fails again u can try the below steps:
> 1. Create a new table same as the corrupted table
> 2. Write a script to copy the data in batches excluding the corrupted
data.
> 3. Rename the corrupted table to table_corrupt
> 4. Rename the new table to original name
> 5. Create all indexes for this new table
> Thanks
> Hari
> MCDBA
>
> "RB" <rakhi.bhatia@.verizon.net> wrote in message
> news:#O8P#jWnDHA.2512@.TK2MSFTNGP09.phx.gbl...
> > I have a corrupt record in my database that I can't query on. Nor can I
> > delete or update. Running any of the Select, Update or Delete statements
> in
> > QA using the WHERE clause with ID = <the corrupt record ID> causes the
> > system to hang until it eventually times out.
> >
> > How can I get rid of this record ?
> >
> >
>|||To add to the other posts:
If this indeed is a corrupted row, you want to find out why it became corrupted in the first place.
If you have a hw problem, you want to fix that!
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"RB" <rakhi.bhatia@.verizon.net> wrote in message news:%23O8P%23jWnDHA.2512@.TK2MSFTNGP09.phx.gbl...
> I have a corrupt record in my database that I can't query on. Nor can I
> delete or update. Running any of the Select, Update or Delete statements in
> QA using the WHERE clause with ID = <the corrupt record ID> causes the
> system to hang until it eventually times out.
> How can I get rid of this record ?
>