It is possible to catch an exception in plpgsql: EXCEPTION WHEN unique_violation THEN But how to check for a violation of a particular constraint? The following example creates two keys: one for a and one for b. How to know which one has caused the exception? ~~~sql create table t ( a text unique, b text unique ); create procedure insert_t(a text, b text) language plpgsql as $$ begin insert into t values (a, b); exception when unique_violation then raise notice 'is it a or b?'; end $$; call insert_t('x', 'foo'); call insert_t('x', 'bar'); select * from t; ~~~

I have some sql procedure (MSSQL 2019) if relevant

Create PROCEDURE [dbo].[test] @tmp varchar(32)  AS
print @tmp

why when i do

EXEC	@return_value = [dbo].[test]
		@tmp = N'SSL99999999999999999999999999999900000006785999999999999999'

I se it use 32 chars substring in @tmp ? SSL99999999999999999999999999999 And no exception that string or binary data would be truncated as i expected ? Can i turn on such behavouur ? i would need this if possible ;) Thanks and regards !

When I execute this: DROP TABLE IF EXISTS #QueryHashes; CREATE TABLE #QueryHashes (query_hash BINARY(8) NOT NULL PRIMARY KEY); PRINT CAST(0x0169857CCD5BBBE4E AS BINARY(8)); -- just testing that the value is a legitimate BINARY(8) /* Insert your query_hash values inside the outer parenthesis below, each wrapped in parenthesis, comma-separated (for example, "(0x89E35F2D4C638298), (0x89E35F2D4C638298)") */ INSERT INTO #QueryHashes (query_hash) VALUES (0x0169857CCD5BBBE4E); PRINT CAST(0x0169857CCD5BBBE4E AS BINARY(8)); -- just testing that the value is a legitimate BINARY(8) I get this error: > 0x00169857CCD5BBBE Msg 2628, Level 16, State 1, Line 9 String or > binary data would be truncated in table > 'tempdb.dbo.#QueryHashes________________________________________________________________________________________________________000000000D06', column 'query_hash'. Truncated value: ''. The statement has been > terminated. 0x00169857CCD5BBBE Note that the value in the error message seems to be an empty string. Clearly, the insert is failing, while the casts work. Can anyone help me understand what is wrong with the insert or table definition? Even if I remove the "NOT NULL PRIMARY KEY", I still get the same error. (The eventual intent is to insert a few values into the table, and join it into a big query.)

Is there anyway in Oracle to get the error code associated with a user-defined exception type without that exception being actually raised? I don't want to hard-code the code in the entire pl/sql package, and it should be persistent. example: create package body some_package is item_not_found exception; pragma exception_init(item_not_found , -20010); function do_something is begin -- a select statement -- exception when no_date_found then raise_application_error( [ here I need to pass the error code associated with item_not_found ], 'The item you requested was not found'); end ; end some_package; In this example, sqlerr is not what I need.

Is there a way I could check the status of an insert query whether it was successful or not. I want to write to another table only if the first insertion was successful. Below is the table I want to do the first insert and also it should return the conversation_id which I need for to make an insert to the second table. Conversations table - create table conversations ( id uuid not null, conversation_id text UNIQUE PRIMARY KEY not null, participants jsonb not null, created_at timestamp with time zone, updated_at timestamp with time zone, avatar_url varchar(500), last_message varchar(32000) ) After the successful insert on the first table, I should use the returned conversation_id and insert a record onto the messages table.After successful insertion it should return the value of the message field returned. Messages Table - create table messages ( id uuid not null primary key, conversation_id text references conversations.conversation_id not null, message text, sender_id text, receiver_id text, message_type text, created_at timestamp with time zone DEFAULT now() ) And after the two inserts I need to make an update to the first table again with the returned value of the message field. Here is the full function to do the above, the below function works when calling using select, but when I call it through my client I get this error -- Could not find the public.new_conversation(members, the_conversationId) function in the schema cache CREATE OR REPLACE FUNCTION public.new_conversation(the_conversationId text, members jsonb, sent_message text) RETURNS text LANGUAGE plpgsql AS $function$ declare conv_id text; the_message text; BEGIN SELECT conversation_id into conv_id FROM conversations WHERE conversation_id = the_conversationId; if not found then insert into conversations(conversation_id, participants) values(the_conversationId,members) Returning conversation_id into conv_id; if found then insert into messages(conversation_id,message) values(conv_id,sent_message) Returning message into the_message; if found THEN update conversations set last_message = the_message where conversation_id = conv_id; end if; end if; end if; return conv_id; END; $function$ Should I be doing any exceptions handling for my insert statements?

Can you please provide syntax for to use user defined exception in PostgreSQL function? Suppose I want to handle below user defined exception.

SQL Error : ERROR: password is too short.

There are multiple SQLSTATE error code but not able to find what is the SQLSTATE code for this error. I used above which is 22023 but not resolved. We have below code we are able to manage unique violation related exception but not able to manage for "password is too short". could you help me with the syntax? Code:

begin
EXECUTE 'ALTER USER ' || $1 || ' WITH PASSWORD '''|| $2||'''' ;
        EXCEPTION WHEN "Password is too short" 
        THEN RAISE DETAIL 'Please check your password';
        
INSERT INTO pwdhistory (usename,password,changed_on) values($1,md5($2),now());
        EXCEPTION WHEN unique_violation 
        THEN RAISE DETAIL 'Password already used earlier. Please try again with another password.';

end;

So, I have two tables, one where I import data from an excel file all in string format without considering any sorts of constraints or data type validation. And then the data is transferred to the actual table where those data are stored in proper structure. I have something like

insert into "actualtable" (cols)
select cols
from "dumptable"
where condition;

Now I want to handle exception, for example, I want to determine the type of error occurred during transfer and update the dumptable with proper status message for each row, which will be shown to the client so that they know what specific row of their excel file has what specific problem. How do I do that? I wanted to do something like

exception when  then
    update "dumptable" set status =  where "id" =

But I am unable to identify the exceptionRowId thing. Is there a way to do that? Since the excel files are really huge (millions of rows), I thought something like this would be preferable over looping over a cursor. Correct me if I am wrong, and suggest the best approach.

I have added an EXIT handler in my procedure that captures the SQLSTATE and SQLCODE, and even found a way to get the procedure name, but I also need to know where the error occurred. Suggestions greatly appreciated.

declare EXIT handler for SQLEXCEPTION
begin
	select sysibm.routine_specific_name, SQLSTATE, SQLCODE 
	into v_sp_name, v_sqlstate, v_sqlcode 
	from sysibm.sysdummy1;

	call dbms_output.put_line('Error in '||v_sp_name ' ['||v_sqlstate, v_sqlcode||']');
end;

I would like to know if it is possible to automate some task when an insertion or update on a table generates an error (for example error codes 1062, 1451 or 1452) Finding an answer to that is not easy, I replies I could find were about catching an error on some statement executed by the trigger, while I'm looking to catch an error on the statement that fired the trigger. It could be that the constraint is checked before any trigger is called, but it would make sense if the before trigger was called. For lack of a better idea, I tried this silly test but of course it doesn't work.

CREATE TABLE test1 (
    id1 int PRIMARY KEY,
    val char(10)
) ENGINE =InnoDB;

CREATE TABLE test2 (
    id2 int PRIMARY KEY,
    id1 int,
    val char(10),
    FOREIGN KEY (id1) REFERENCES test1(id1)
) ENGINE =InnoDB;
DELIMITER $$
CREATE TRIGGER test2_before_insert
    BEFORE insert ON test2
FOR EACH ROW
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'before, sqlexception';
    DECLARE EXIT HANDLER FOR SQLWARNING
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'before, sqlwarning';
    DECLARE EXIT HANDLER FOR NOT FOUND
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'before, not found';
    -- Do the work of the trigger.
END $$
CREATE TRIGGER test2_after_insert
    AFTER insert ON test2
FOR EACH ROW
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'after, sqlexception';
    DECLARE EXIT HANDLER FOR SQLWARNING
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'after, sqlwarning';
    DECLARE EXIT HANDLER FOR NOT FOUND
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'after, not found';
    -- Do the work of the trigger.
END $$
DELIMITER ;
INSERT INTO test1 VALUES(1,'A');
INSERT INTO test2 VALUES(1,1,'A');
-- hoping it'll show the custom exception but it shows the normal one.
INSERT INTO test2 VALUES(2,2,'B');
DROP TABLE test2;
DROP TABLE test1;

If I have a stored procedure with a handful of error checks that come before the queries are executed and throw exceptions as necessary, could this affect performance? Is it bad practice to do this? For example, say I had 10 logical case checks against the parameters of this stored procedure, would that be fine?...What if I had 100 check cases? (The functional programmer in me wants to not leave any holes, but the relational developer in me feels like this could lead to a bad query plan being generated.) Example of how I'm doing the error checking: IF @Parameter1 IS NULL OR @Parameter2 IS NULL OR @Parameter3 IS NULL BEGIN THROW 50000, 'Error Message 1', 1; END ELSE IF @Parameter1 = 'Value1' AND @Parameter2 @Parameter3 BEGIN THROW 50001, 'Error Message 2', 1; END

I'm having trouble with ON CONFLICT not working for foreign key columns where the foreign key is compound. Here's an example. create table foreign_table ( id_a text not null, id_b text not null, id integer primary key, constraint ft_a_b_key unique (id_a, id_b) ); create table my_table ( id integer, ftable_id_a text, ftable_id_b text, constraint my_table_a_b_fk foreign key (ftable_id_a, ftable_id_b) references foreign_table (id_a, id_b) ); Using this query: insert into tcell_test.my_table (id, ftable_id_a, ftable_id_b) values (3, 'a3', 'b3') on conflict do nothing ; where, say, 'a3' isn't in foreign_table, I would expect the ON CONFLICT to handle the error. Instead I get the error: > ERROR: insert or update on table "my_table" > violates foreign key constraint "my_table_a_b_fk" > Detail: Key (ftable_id_a, ftable_id_b)=(a3, b3) > is not present in table "foreign_table". Is there a way to correct this so ON CONFLICT handles the error?

I've been asked this question but neither I seem to be able to answer it on my own, nor can I find anything related on the web. So what are the cases that might cause a runtime exception when committing a transaction in Oracle?The only thing that I can think of is the low disk space. Are there any other?

I have a function that checks is the file is present on disk in an specific location . Using that function I am checking for the latest backup, in the code below. If we don't have a backup anymore, for a specific date, I want to raise an error. Currently I am using raiserror but because of Deprecated raiserror detection I would like to use throw instead . How can I do it? this is part of my stored procedure: IF @ServerNameToRestoreOn IS NULL THROW 50001, '@ServerNameToRestoreOn cannot be NULL - it must be the name of server\instance you want to restore the database to', 1 IF @Database IS NULL THROW 50001, '@Database cannot be NULL - it must be the name of the database you want to restore', 1 IF NOT EXISTS ( SELECT MAX(backup_set_id) FROM msdb.dbo.backupset a INNER JOIN msdb.dbo.backupmediafamily b ON a.media_set_id = b.media_set_id WHERE a.database_name = @Database AND a.type='D' AND a.is_copy_only = 0 AND a.backup_finish_date < @StopAt AND master.dbo.fn_FileExists(b.physical_device_name) = 1 GROUP BY database_name ) BEGIN SELECT @sql = 'The database called "%s" does not have a backup before' + CAST (@StopAt AS VARCHAR(20)) + @vCrlf + @vCrlf RAISERROR(@sql ,16,1,@Database) END How can I replace the raiserror by throw? I want to show the @Database and the @stopat in the error message or, in better wording: how do you pass THROW() a message where you can still use printf-style substitution like you can with RAISERROR?

I have noticed something odd, and I am hoping someone in the community has an answer for me. I have noticed that when a stored procedure is nested it **seems** that all the blocks prior to last catch block have a weird bug with the ERROR_PROCEDURE() function where it returns the previous blocks procedure name. **NOTE:** This only occurs when using RAISERROR. When using THROW, the very last procedure in the chain is always reported. Since that was probable confusing, here is an example. SET NOCOUNT ON IF OBJECT_ID (N'tempdb..#spCatchTest1') IS NOT NULL BEGIN DROP PROCEDURE #spCatchTest1 END GO CREATE PROCEDURE #spCatchTest1 AS BEGIN BEGIN TRY EXEC #spCatchTest2 END TRY BEGIN CATCH PRINT 'CATCH EXPECTED=#spCatchTest1; ACTUAL=' + ERROR_PROCEDURE() DECLARE @Err NVARCHAR(4000) = ERROR_MESSAGE() RAISERROR(@Err, 16, 10); --;THROW END CATCH; END GO IF OBJECT_ID (N'tempdb..#spCatchTest2') IS NOT NULL BEGIN DROP PROCEDURE #spCatchTest2 END GO CREATE PROCEDURE #spCatchTest2 AS BEGIN BEGIN TRY EXEC #spCatchTest3 END TRY BEGIN CATCH PRINT 'CATCH EXPECTED=#spCatchTest2; ACTUAL=' + ERROR_PROCEDURE() DECLARE @Err NVARCHAR(4000) = ERROR_MESSAGE() RAISERROR(@Err, 16, 10); --;THROW END CATCH; END GO IF OBJECT_ID (N'tempdb..#spCatchTest3') IS NOT NULL BEGIN DROP PROCEDURE #spCatchTest3 END GO CREATE PROCEDURE #spCatchTest3 AS BEGIN BEGIN TRY EXEC #spCatchTest4 END TRY BEGIN CATCH PRINT 'CATCH EXPECTED=#spCatchTest3; ACTUAL=' + ERROR_PROCEDURE() DECLARE @Err NVARCHAR(4000) = ERROR_MESSAGE() RAISERROR(@Err, 16, 10); --;THROW END CATCH; END GO IF OBJECT_ID (N'tempdb..#spCatchTest4') IS NOT NULL BEGIN DROP PROCEDURE #spCatchTest4 END GO CREATE PROCEDURE #spCatchTest4 AS BEGIN BEGIN TRY SELECT 1/0 END TRY BEGIN CATCH PRINT 'CATCH EXPECTED=#spCatchTest4; ACTUAL=' + ERROR_PROCEDURE() DECLARE @Err NVARCHAR(4000) = ERROR_MESSAGE() RAISERROR(@Err, 16, 10); --;THROW END CATCH; END GO EXEC #spCatchTest1 This will output: CATCH EXPECTED=#spCatchTest4; ACTUAL=#spCatchTest4 CATCH EXPECTED=#spCatchTest3; ACTUAL=#spCatchTest4 CATCH EXPECTED=#spCatchTest2; ACTUAL=#spCatchTest3 CATCH EXPECTED=#spCatchTest1; ACTUAL=#spCatchTest2 Msg 50000, Level 16, State 10, Procedure #spCatchTest1, Line 11 [Batch Start Line 81] Divide by zero error encountered. As you can see on the second line, the top level catch incorrectly reported the procedure name. This appears to **only** affect the procs that occur in the chain after the error. Has anyone else noticed this? Plus, it is a bug, correct? Is there anyway to get the correct procedure name when nested? EDIT: To clarify, I am essentially trying to build a way to get the stacktrace from a very nested set of stored procs we are inheriting. Think spaghetti monster. With the native CATCH->THROW you only get the top level stored proc name. Here is an example of what I was trying to do: USE tempdb GO IF OBJECT_ID (N'GetErrorInfo') IS NOT NULL BEGIN DROP PROCEDURE GetErrorInfo END GO CREATE PROCEDURE GetErrorInfo ( @ErrorNumber INT, @ErrorMessage nvarchar(4000) OUTPUT, @ErrorSeverity INT OUTPUT, @ErrorState INT, @ErrorProcedure nvarchar(128), @ErrorLine INT, @NestLevel INT ) AS BEGIN DECLARE @msg NVARCHAR(4000), @StackTrace NVARCHAR(4000), @i INT = PATINDEX('%%', @ErrorMessage) -- find the stacktrace seperator if there is on on the message -- set the error procedure, and the error severity, if the caller is not sysadmin, limit the severity SELECT @ErrorProcedure = ISNULL(@ErrorProcedure, 'NA'), @ErrorSeverity = CASE WHEN IS_SRVROLEMEMBER('sysadmin') = 0 THEN (SELECT MIN(Num1) FROM (VALUES (@ErrorSeverity), (18)) AS Vals(Num1)) ELSE @ErrorSeverity END IF @i ' + @ErrorProcedure + '.' + CAST(@ErrorLine AS VARCHAR(20)) + ';' END ELSE BEGIN -- split the stacktrace seperator and add the new stack onto it SELECT @msg = LEFT(@ErrorMessage, @i - 1), @StackTrace = RIGHT(@ErrorMessage, LEN(@ErrorMessage) - @i - 1) SET @ErrorMessage = @msg + '' + @ErrorProcedure + '.' + CAST(@ErrorLine AS VARCHAR(20)) + ';' + @StackTrace END -- we are at the top of the call chain, so lets format the final error message. IF @NestLevel = 1 BEGIN SET @ErrorMessage = FORMATMESSAGE( N'Error %d, Level %d, State %d, Procedure %s, Line %d, Message: ' + REPLACE(@ErrorMessage, '', CHAR(13) + CHAR(10)), @ErrorNumber, 1, @ErrorState, @ErrorProcedure, @ErrorLine) ; END END GO /* DECLARE @ErrMessage NVARCHAR(4000) = 'Divide by zero', @ErrSeverity INT = 16, @ErrProcedure NVARCHAR(128) = 'spCatchTest3'; EXEC GetErrorInfo @ErrorMessage = @ErrMessage OUTPUT, @ErrorSeverity = @ErrSeverity OUTPUT, @ErrorProcedure = @ErrProcedure OUTPUT, @ErrorLine = 32, @NestLevel = 1 SELECT @ErrMessage, @ErrSeverity, @ErrProcedure SELECT @ErrMessage = 'Divide by zerospCatchTest3.32;', @ErrSeverity = 16, @ErrProcedure = 'spCatchTest2'; EXEC GetErrorInfo @ErrorMessage = @ErrMessage OUTPUT, @ErrorSeverity = @ErrSeverity OUTPUT, @ErrorProcedure = @ErrProcedure OUTPUT, @ErrorLine = 34, @NestLevel = 2 SELECT @ErrMessage, @ErrSeverity, @ErrProcedure */ GO IF OBJECT_ID (N'spCatchTest1') IS NOT NULL BEGIN DROP PROCEDURE spCatchTest1 END GO CREATE PROCEDURE spCatchTest1 AS BEGIN DECLARE @trancount int = @@trancount IF @trancount = 0 BEGIN BEGIN TRANSACTION; END --only start a transaction if we are not already under another transaction BEGIN TRY EXEC spCatchTest2 IF @trancount = 0 BEGIN COMMIT TRAN; END END TRY BEGIN CATCH IF @trancount = 0 BEGIN ROLLBACK TRAN; END DECLARE @ErrorMessage NVARCHAR(4000) = ERROR_MESSAGE(), @ErrorNumber int = ERROR_NUMBER(), @ErrorState int = ERROR_STATE(), @ErrorSeverity INT = ERROR_SEVERITY(), @ErrorProcedure NVARCHAR(128) = OBJECT_NAME(@@PROCID), @ErrorLine int = ERROR_LINE(); EXEC GetErrorInfo @ErrorNumber = @ErrorNumber, @ErrorMessage = @ErrorMessage OUTPUT, @ErrorSeverity = @ErrorSeverity OUTPUT, @ErrorState = @ErrorState, @ErrorProcedure = @ErrorProcedure, @ErrorLine = @ErrorLine, @NestLevel = @@NESTLEVEL RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState) END CATCH; END GO IF OBJECT_ID (N'spCatchTest2') IS NOT NULL BEGIN DROP PROCEDURE spCatchTest2 END GO CREATE PROCEDURE spCatchTest2 AS BEGIN DECLARE @trancount int = @@trancount IF @trancount = 0 BEGIN BEGIN TRANSACTION; END --only start a transaction if we are not already under another transaction BEGIN TRY EXEC spCatchTest3 IF @trancount = 0 BEGIN COMMIT TRAN; END END TRY BEGIN CATCH IF @trancount = 0 BEGIN ROLLBACK TRAN; END DECLARE @ErrorMessage NVARCHAR(4000) = ERROR_MESSAGE(), @ErrorNumber int = ERROR_NUMBER(), @ErrorState int = ERROR_STATE(), @ErrorSeverity INT = ERROR_SEVERITY(), @ErrorProcedure NVARCHAR(128) = OBJECT_NAME(@@PROCID), @ErrorLine int = ERROR_LINE(); EXEC GetErrorInfo @ErrorNumber = @ErrorNumber, @ErrorMessage = @ErrorMessage OUTPUT, @ErrorSeverity = @ErrorSeverity OUTPUT, @ErrorState = @ErrorState, @ErrorProcedure = @ErrorProcedure, @ErrorLine = @ErrorLine, @NestLevel = @@NESTLEVEL RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState) END CATCH; END GO IF OBJECT_ID (N'spCatchTest3') IS NOT NULL BEGIN DROP PROCEDURE spCatchTest3 END GO CREATE PROCEDURE spCatchTest3 AS BEGIN DECLARE @trancount int = @@trancount IF @trancount = 0 BEGIN BEGIN TRANSACTION; END --only start a transaction if we are not already under another transaction BEGIN TRY EXEC spCatchTest4 IF @trancount = 0 BEGIN COMMIT TRAN; END END TRY BEGIN CATCH IF @trancount = 0 BEGIN ROLLBACK TRAN; END DECLARE @ErrorMessage NVARCHAR(4000) = ERROR_MESSAGE(), @ErrorNumber int = ERROR_NUMBER(), @ErrorState int = ERROR_STATE(), @ErrorSeverity INT = ERROR_SEVERITY(), @ErrorProcedure NVARCHAR(128) = OBJECT_NAME(@@PROCID), @ErrorLine int = ERROR_LINE(); EXEC GetErrorInfo @ErrorNumber = @ErrorNumber, @ErrorMessage = @ErrorMessage OUTPUT, @ErrorSeverity = @ErrorSeverity OUTPUT, @ErrorState = @ErrorState, @ErrorProcedure = @ErrorProcedure, @ErrorLine = @ErrorLine, @NestLevel = @@NESTLEVEL RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState) END CATCH; END GO IF OBJECT_ID (N'spCatchTest4') IS NOT NULL BEGIN DROP PROCEDURE spCatchTest4 END GO CREATE PROCEDURE spCatchTest4 AS BEGIN DECLARE @trancount int = @@trancount IF @trancount = 0 BEGIN BEGIN TRANSACTION; END --only start a transaction if we are not already under another transaction BEGIN TRY SELECT 1/0 IF @trancount = 0 BEGIN COMMIT TRAN; END END TRY BEGIN CATCH IF @trancount = 0 BEGIN ROLLBACK TRAN; END DECLARE @ErrorMessage NVARCHAR(4000) = ERROR_MESSAGE(), @ErrorNumber int = ERROR_NUMBER(), @ErrorState int = ERROR_STATE(), @ErrorSeverity INT = ERROR_SEVERITY(), @ErrorProcedure NVARCHAR(128) = OBJECT_NAME(@@PROCID), @ErrorLine int = ERROR_LINE(); EXEC GetErrorInfo @ErrorNumber = @ErrorNumber, @ErrorMessage = @ErrorMessage OUTPUT, @ErrorSeverity = @ErrorSeverity OUTPUT, @ErrorState = @ErrorState, @ErrorProcedure = @ErrorProcedure, @ErrorLine = @ErrorLine, @NestLevel = @@NESTLEVEL RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState) END CATCH; END GO EXEC dbo.spCatchTest1

In a SQL Server code block, what is the best place to place the commit transaction? Inside the try catch block or outside it?. For example, is option A or option B the correct approach or are they subjective choices? **Option A** CREATE PROCEDURE DummyProc BEGIN TRY BEGIN TRANSACTION INSERT sometable(a, b) VALUES (@a, @b) INSERT sometable(a, b) VALUES (@b, @a) COMMIT TRANSACTION END TRY BEGIN CATCH IF @@trancount > 0 ROLLBACK TRANSACTION DECLARE @msg nvarchar(2048) = error_message() RAISERROR (@msg, 16, 1) RETURN 55555 END CATCH **Option B** CREATE PROCEDURE DummyProc BEGIN TRY BEGIN TRANSACTION INSERT sometable(a, b) VALUES (@a, @b) INSERT sometable(a, b) VALUES (@b, @a) END TRY BEGIN CATCH IF @@trancount > 0 ROLLBACK TRANSACTION DECLARE @msg nvarchar(2048) = error_message() RAISERROR (@msg, 16, 1) RETURN 55555 END CATCH IF @@trancount > 0 COMMIT TRANSACTION In Option B, is there a possibility of some error happening when its doing a commit outside the TRY-CATCH block ?

According to the docs , there are options to RAISE called TABLE and COLUMN. However , if I explicitly set them, they do not end up in the resulting error message. It seems only DETAIL and HINT are ever used in the error message. How can you get the other options to show up? E.g.:

RAISE EXCEPTION 'invalid_parameter' USING
    DETAIL = FORMAT('The specified user "%s" was not found.', _usr),
    HINT = 'Enter the username of an existing user.',
    COLUMN = 'username',
    TABLE = 'accounts';

Issues an error message:

... ERROR:  invalid_parameter
... DETAIL:  The specified user "aanders" was not found.
... HINT:  Enter the username of an existing user.
... CONTEXT:  PL/pgSQL function _api.find_user(text) line 24 at RAISE
... PL/pgSQL function _api.accounts_update_trigger() line 97 at assignment
... STATEMENT:
...     WITH pg_source AS (...

There is no "TABLE" or "COLUMN" shown. **UPDATE** The key term I needed was "VERBOSITY" - once I searched for that I found duplicate questions, e.g. https://stackoverflow.com/q/41959752/1571426 (not sure if a question on a different forum makes this "duplicate" or not.)

Consider the following (incomplete) block of PL/pgSQL inside a function:

CREATE OR REPLACE FUNCTION my_calc(myvar1 NUMERIC, myvar2 NUMERIC)
    RETURNS NUMERIC
    RETURNS NULL ON NULL INPUT
    IMMUTABLE
    LANGUAGE plpgsql
    AS $$
    BEGIN
        RETURN some_third_party_function(myvar1, myvar2);
    EXCEPTION WHEN internal_error THEN
        IF SQLERRM LIKE 'KnownErrorPrefix:%' THEN
            RETURN 0;
        ELSE
            -- Reraise the original exception here
            RAISE EXCEPTION '%', SQLERRM;
        END IF;
    END
    $$

When an unanticipated error occurs, this code will throw a new exception with the same message. However, it won't preserve the original type or context. How can I reraise or rethrow the original exception unmodified?

The SQL 2011 Spec supports a CORRESPONDING clause, > Feature F301, “CORRESPONDING in query expressions”: What is this feature? How is it used? And, is it supported by any industry RDBMs?

**Overview** A package was developed due to a planned database server migration, and other teams with a dependency on legacy cross-database queries. There is a policy of not having Linked Servers, so an SSIS package was setup to push the latest data to the required tables on the old server. There was a restriction defined for this new package, where it will need to finish before a backup to a read-only server that is set to run nightly against the old server. This read-only server allows for getting data without many hits to the PROD database server, and the cross-database queries do not impact mission-critical processes. In case there are missed tables, some dynamic functionality was put in place to limit repetitive code/query changes. There are still scenarios that will require a physical change, but I will branch on that further down. I am going to start at the dynamic functionality, but I cannot confirm that the error is explicitly occurring there. I have two items that are attempts at being dynamic. The first loops over a lookup table to truncate the destination tables using a dataflow with a source and OLE DB Command. The second will loop over the same lookup table to define the tables in code and utilize SQLBulkCopy via a Script Task. The first hurdle tackled was dealing with large datasets. Large meaning size of data. Some examples are XML columns or poorly structured tables containing duplicate information. (For a table with the XML column, the column contains 2 to 4 GB alone) For those large datasets, they were pieced out of the dynamic SQLBulkCopy by using a normal data flow task in SSIS. A flag was added to the lookup table to determine the tables to exclude. For the table with an XML column, this was still fairly slow, so it was determined to only insert the delta and perform an update for changed rows. All that to try saying the context of the package that produced an error. **Issue** Exception handling was not defined, since data was not being transformed to some new data. It was essentially a normally backup process without creating a backup file. An error did occur, but it only showed a message of "Exception has been thrown by the target of an invocation.". We are looking into adding exception handling, since that would allow us to get a better error message. The confusing part begins that following day. The package ran successfully, and the way the package is setup picked up the missing data. No changes to the physical package, and data is only ever added or updated to the main tables. (no deletion of records) In an attempt to find a root cause, I am trying to step through Event Viewer, where the packages are deployed and DTEXEC exists. I have found the event that states the package failing, but there are no clear events surrounding that timeframe to see a pattern of the failure. Here are the events that I have found, but all may not actually apply: - Application - VSS: Event Id 12289; Volume Shadow Copy Service Error - SQLISPackage130: Event Id 12291; Package 'MyPackage' failed. - System - DistributedCOM: Event Id 10016; application-specific permission settings do not grant Local Activation Permission - disk: Disk 2 was surprise removed **Questions** Before I go further down this rabbit hole, is this even a viable way to trace that generic of an error message for SSIS? If it is, then do any of the above events state anything that I could be missing? As far as it throwing an error one day then not throwing error for the subsequent week (happened last week), is this throwing a flag or smell that I may not be understanding? Lastly, is there another way to trace this kind of issue that I have not stated above? Again, there is no exception handling in the Script Task, so the available exception message at that level appears to be lost. Admittedly, this might be something to let go and add the exception handling to trace down the road, if it happens again. Please let me know if I need to clarify anything, or if I am having a misconception on any of the above.

Today I realized a number of points where PostgreSQL (v10.3) is rather lackluster and sparse in its error messages. The first point is illustrated by this code: drop schema if exists X cascade; create schema X; create domain X.an_illegal_regex as text check ( value ~ '(' ); create table X.table_with_illegal_constraint ( a text, constraint "column a must have a bogus value" check ( a::X.an_illegal_regex = a ) ); select * from X.table_with_illegal_constraint; insert into X.table_with_illegal_constraint values ( 'xxx' ), -- ( 'xxx' ), ( 'foo' ), ( 'xyx' ); This code will throw with psql:db/experiments/pg-error-fail-illegal-regex.sql:17: ERROR: invalid regular expression: parentheses () not balanced There are several problems with this error message: FAILURE: the error is really in line 5 where a syntactically invalid RegEx is created; the fact that it is a RegEx and not a general string is obvious from the semantics of the ~ (tilde) operator at that point in time. FAILURE: the offending RegEx is not referred to and not quoted in the error message. As such, it could be anywhere in my many, many kLOCs big DB definition. I cannot even search the RegEx with a RegEx because all I know is some parenthesis is missing, somewhere: RegExes cannot match parentheses, and PG RegExes do not have a unique syntactic marker to them. FAILURE: before the insert statement, everything runs dandy. We could have built an entire data warehouse application on top of a table definition that can never be syntactically processed but which will only fail when someone accidentally tries to insert a line. FAILURE: I can select from a table with a syntactically invalid definition. The second point is related: drop schema if exists X cascade; create schema X; create domain X.a_legal_regex as text check ( value ~ '^x' ); create table X.table_with_constraints ( a text, constraint "column a must start with x" check ( a::X.a_legal_regex = a ), constraint "field b must have 3 characters" check ( character_length( a ) = 3 ) ); insert into X.table_with_constraints values ( 'xxx' ), ( 'foo' ), /* A: violates first constraint */ -- ( 'xxxx' ), /* B: violates second constraint */ ( 'xyx' ); With only line B active, this gives: psql:db/experiments/pg-error-fail-no-constraint-name.sql:16: ERROR: new row for relation "table_with_constraints" violates check constraint "field b must have 3 characters" DETAIL: Failing row contains (xxxx). SUCCESS: we get the name of the relation *and* the name of the violated rule. SUCCESS: the offending piece of data is quoted. FAILURE: we don't get the full name of the relation, which is "X"."table_with_constraints". Neither do we get the name of the column that received the offending value. Lastly, with only line A (not line B) active: psql:db/experiments/pg-error-fail-no-constraint-name.sql:16: ERROR: value for domain x.a_legal_regex violates check constraint "a_legal_regex_check" FAILURE: no reference to the affected table, column is made. FAILURE: no reference to the offending piece of data is made. FAILURE: no reference to the offended constraint is made ("column a must start with x"). What are the best practices or workarounds for the above shortcomings? I've been trying for several hours to figure out what causes an error message a la value for domain xxx violates check constraint "xxx_check" by rewriting table definitions, inserting data row by row and so on, to no avail. What I need is a full chain of the objects (column -> table -> constraint -> domain -> check) that are involved in the error.