Hot answers tagged error-handling
10
There isn't a built-in assertion procedure in SQL or PL/SQL, so you will have to write your own.
There are two ways of going about this. You can either manually raise an exception, as discussed in this Oracle article, or you can write a wrapper for the raise_application_error procedure, which is documented in the Oracle exception handling section of the ...
9
It looks like the pre SQL Server 2012 behaviour is a bug.
I infer this from this Connect article:
https://connect.microsoft.com/SQLServer/feedback/details/712003/sql-server-2012-error-in-exception-handling-mechanism
I could be wrong of course...
6
The built in DBMS_ASSERT package is a narrowly scoped version of what you are looking for. For other asserts Phil is correct, you will have to build your own. Here is a simple demonstration of the second option in Phil's answer+1:
set serveroutput on size 1000000
Declare
Procedure Assert (pCondition In Number, pMessage In Varchar2) Is
Begin
If ...
6
AFAIK, the only way to do this is to use the WITH LOG option of the RAISERROR function. Note from that MSDN page that there are certain security requirements that must be met for you to do this.
That said, the SQL Server error log really isn't meant for application-based logging. If you need to add monitoring/logging to your application, this should ...
6
I could not find a direct way to output the the "CONTEXT" line with a user-defined exception. This option is just not implemented (yet) in PostgreSQL 9.1. Read the manual here.
However, I found a ...
Workaround
... that should perform flawlessly. You can make plpgsql behave as desired by calling another function that raises the error for you. This works ...
6
for /r %%i in (*.sql) do sqlcmd -E -S %SERVER% -d %DATABASE% -i"%%i" -m0 -w500 -b -r >%%i.log 2>%%i.err
Not perfect since it creates an empty error output file when there are no errors, but a .err file with > 0 bytes will have the errors.
for %%a in (*.err) do if %%~za==0 del "%%a"
..if that's a problem...
5
Pros and Cons of Checking if value exist for unique column or let db raise unique error on inserting
Let the DB raise an error.
Testing first isn't safe for concurrency because you'll get a collision eventually because 2 threads may pass the "NOT EXIST" and both will try to write. This applies to both "READ COMMITTED" and MVCC/Snapshot lock strategies.
You can use lock hints to force isolation, but you reduce performance.
I call this the JFDI pattern ...
5
If the unique field is properly indexed, a UNIQUE violation should be fairly quick.
Honestly though, I think doing a "two inner select" (I think I know what you mean here) is a better solution. Even with trapping for an exception (at the application level), that's just not a very clean way to handle programatic logic. Exception handling should be reserved ...
4
I wrote something a while back, perhaps this link will help you.
http://www.mssqltips.com/sqlservertip/2017/script-to-check-all-your-linked-server-connections-for-sql-server/
I had a need to verify that the linked servers were working, especially after a server rebuild. And I had the same issues you are seeing with regards to using straight t-sql. So I ...
4
While RAISERROR ... WITH LOG is possible, don't forget that
Only a member of the sysadmin fixed server role or a user with ALTER
TRACE permissions can specify WITH LOG.
In production you would have to wrap the WITH log generating code in a stored procedure properly signed.
Depending on what you're trying to achieve, there are far better options for ...
3
In Profiler, click "show all events" and go to the Errors and Warnings listing. You should be able to check Exception and User Error Message. The Exception class will show you the actual error and User Error Message will show you the message displayed (e.g., "Incorrect syntax near ..." or whatnot).
You mention a message going into the error log; there's ...
3
Do I have any option other than raising the error and having the msg_id being 50000?
You can define custom messages that have an id > 50000, or you can choose to not define a message and use id = 50000. Internal error numbers can't be raised by users because allowing that would open up all sorts of avenues of abuse...
How you implement this really ...
3
No, it does not help you find anything out about where the error occurred. Here is a quick example. If you try to divide by 0, you get an error message with a bunch of details:
SELECT 1/0;
Result:
Msg 8134, Level 16, State 1, Line 1
Divide by zero error encountered.
See the one called State, with a value of 1? ERROR_STATE() returns this value. So if ...
3
I believe SIGNAL (introduced in MySQL 5.5) is what you want, for automatic logging.
However, the current implementation cannot use the various conditions such as SCHEMA_NAME after the SIGNAL executes:
Other condition information items can be set, but currently have no effect, in the sense that they are not accessible from error returns. For example, ...
2
The transaction is doomed with pretty much any exception and must be rolled back.
From "Using TRY...CATCH in Transact-SQL" on MSDN
Inside a TRY…CATCH construct, transactions can enter a state in which the transaction remains open but cannot be committed. The transaction cannot perform any action that would generate a write to the transaction log, such ...
2
Each redo log file (and archived redo log file) contains starting SCN and ending SCN. In case it is a last redo, the ending SCN is 0xffffffffffff.
nap01:~/oradata/jt10g$ strings redo01.log|head -3
z{|}
JT10G
Thread 0001, Seq# 0000000004, SCN 0x0000000b05b5-0x0000000bd34f
nap01:~/oradata/jt10g$ strings redo02.log|head -3
z{|}
JT10G
Thread 0001, Seq# ...
2
I use this pattern proposed in Exception Handling and Nexted Transactions:
create procedure [usp_my_procedure_name]
as
begin
set nocount on;
declare @trancount int;
set @trancount = @@trancount;
begin try
if @trancount = 0
begin transaction
else
save transaction usp_my_procedure_name;
-- Do ...
2
Pros and Cons of Checking if value exist for unique column or let db raise unique error on inserting
I do not think your question is really database agnostic. The right answer could depend on implementation details, which may vary from vendor to vendor and change with the next version. I would test under concurrency before choosing any approach on any RDBMS.
Right now, on SQL Server 2008 R2, I am using the following:
Low concurrency and low amount of ...
2
When BACKUP DATABASE generates an error, it actually generates two. Unfortunately TRY/CATCH is not capable of capturing the first error; it only captures the second error.
I suspect your best bet to capture the real reason behind a failed backup is to automate your backups through SQLCMD (with -o to send output to a file), SSIS, C#, PowerShell etc. All of ...
2
This seems to be an elusive bug nobody wants to fix
http://bugs.mysql.com/bug.php?id=24761 (2006-12-01 : Status Triaged)
http://bugs.mysql.com/bug.php?id=41602 (2008-12-18 : Status Closed)
http://serverfault.com/questions/235369/how-can-i-get-mysql-5-5-to-log-warnings-to-one-of-the-log-files (2011-02-14)
According to ...
2
Looking at the paths of the datafile, I assume/hope this is no production database. If this database is setup as a regular production database, it is running archivelog mode. In that case it is quite simple to recover, if you have a valid backup for the damaged datafile[s], including all archived logfiles created since the start of that backup.
restore the ...
2
I had similar error using different databases. All these errors were related to storing unicode varchar into non unicode varchar field. The problem was not displaying always, but it happened only when the text used contained multi byte characters.
So, first of all: does you database uses multi byte NVARCHAR instead of old style VARCHAR? And, second, are you ...
2
The problem may actually be that in using your cursor, you're not encountering errors, but databases are simply getting skipped. I have had situations in the past where a default cursor will skip records. I've never been able to determine the cause, but I have found some ways to handle it better in the code.
One is to put some better definition around ...
1
The response from Oracle is that this is not really a bug because the application is catching the 4068 and handling it, thereby not allowing the normal resolution of the error to occur. Their explanation doesn't seem adequate to me because if the package throws an ORA-01476-Divide By Zero (for example) doing a raise_application_error does not effect whether ...
1
We implemented a pretty simple C# command-line program that acted as a pre-parser before bulk inserting. It worked quite fast and you could create something with the free Visual Studio Express (or even with just the compiler, I imagine).
To be clear, you would only potentially need Visual Studio to CREATE the program (you can do it without Visual Studio, ...
1
You can log the error details to a table. You could also create a log file, but that may require a CLR or xp_cmdshell to do. You can also send database mail, but that may cause spam issues and is not a proper log.
The table is simplest.
Create a table for storing the errors
Create a stored procedure that inserts into the error table
Call the stored ...
1
In Apex specifically, use validations. You can specify them per field or use a custom one that checks all the fields at once.
http://docs.oracle.com/cd/E23903_01/doc/doc.41/e21674/bldr_validate.htm#BABCDBDE
1
You can use sqlsplus (or SQLS*Plus) to execute multiple sql files - check http://www.sqlsplus.com/ - SQLS*Plus is like an Oracle© SQL*Plus / sqlplus SQL command line tool for Microsoft© SQLServer 2008, 2005 and 2000
execute file like:
@1.sql
@2.sql
@3.sql
Spool errors:
spool 1.log
...
spool off
spool 2.log
...
spool off
1
recover database;
when it asks for an archivelog file, put in the path and name of one of your redologs. If that one doesn't work, try another redolog. Keep it going until you run out of redologs ;-)
If that didn't work, try to recover from your last cold backup and roll it forward to a point in time prior to you taking the database down. If you didn't ...
1
Per the Oracle Docs and MetaLink support note 66431.1, the standard undo mechanism is not used by LOB segments, which have their own page versioning (controlled via PCTVERSION and the LOB_retention_clause).
"Snapshot too old" generally means that a transactionally-consistent version of your data could not be hold long enough because of lack of space. It ...
Only top voted, non community-wiki answers of a minimum length are eligible
