Tag Info

Hot answers tagged

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


7

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

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

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

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


6

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

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


4

you need to use RAISERROR with LOG BEGIN TRY -- Error here SELECT 1/0 END TRY BEGIN CATCH RAISERROR('Ouch... divie by zero', 16,1) WITH LOG END CATCH


4

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


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

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


4

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


4

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

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


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

The purpose of SQL Server error states is for the SQL Server development team to be able to identify in code the exact place system errors were raised, given that many errors are raised in multiple places. You as an end user (ie. developer of applications using SQL Server) can similarly use the state passed in to RAISERROR so that your product support can ...


3

No. Informational messages cannot be suppressed.


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

You are using 'NOT FOUND' and SQLSTATE '02000'. There were bugs reports that addressed this: http://bugs.mysql.com/bug.php?id=42834 (March 5, 2009 : STATUS Closed) http://bugs.mysql.com/bug.php?id=55843 (August 9, 2010 : STATUS Closed) http://bugs.mysql.com/bug.php?id=59169 (December 25, 2010 : STATUS Duplicate) Here is something enlightening about the ...


2

This is an informational message for SQL Server stating successful connection. It is one of three that it will usually return. You can find more info here.


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

The remote database has compatibility level 80, which doesn't work with db_id(). More info on a related StackOverflow question. (Just dumping this into an answer from the question comments so it disappears from the Unanswered list of questions.)


2

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

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

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


2

Set the output of the T-sql job step to output to a file. It's on the advanced tab of the job step properties.



Only top voted, non community-wiki answers of a minimum length are eligible