Questions tagged [error-handling]
The error-handling tag has no usage guidance.
111
questions
0
votes
0answers
16 views
MySQL error handling in a procedre
I'm basically an Oracle programmer, but now need to learn some MySQL programming. I'm having some trouble coding some error handlers and understanding how they work, as I'm really familiar with ...
6
votes
1answer
386 views
SQL Server - TRY/CATCH does not work in certain cases
How can I write guarded code that invokes the CATCH code block in every expected* case (without doing funky junk like wrapping everything in dynamic SQL)?
For example, this does not work:
Define a ...
0
votes
1answer
50 views
how to convert from raiserror to throw in the current context?
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, ...
2
votes
1answer
87 views
why the processing is sometimes terminated , sometimes not
DECLARE @Error int;
INSERT INTO Person.BusinessEntityContact
(BusinessEntityID
,PersonID
,ContactTypeID)
VALUES
(0,0,1);
SELECT @Error = @@ERROR;
PRINT ‘The Value of @Error is ‘ + CONVERT(varchar,...
0
votes
1answer
55 views
Why is my TRY CATCH block in SQL Server immediately exiting on error completely ignoring the remaining code in the block, including my error handling?
Below is a snippet of my TRY/CATCH logic within a stored procedure with error handling (I did not include all of the DECLARE statements). This is being ran within a stored procedure, however I am ...
2
votes
1answer
34 views
Object id error in sql server
I want to know the object referenced as an associatedObjectId in a deadlock graph, so I ran the below query:
SELECT OBJECT_NAME(1234560112364076)
I get the following error:
Arithmetic overflow ...
0
votes
0answers
25 views
Can I force a LOAD DATA LOCAL statement to abort if an error is not loaded?
I am trying to load a CSV file into a MySQL table with the load data local statement. After loading it, the statement reports
Query OK, 1030 rows affected, 7314 warnings (0.03 sec)
Records: 1043 ...
2
votes
1answer
38 views
Does the “don't use errors for flow control” axiom apply to postgres?
In traditional programming there is an axiom that states, "do not use errors for flow control". A general example is to throw an error then catch the error instead of using an ordinary conditional ...
1
vote
1answer
256 views
SQL Creating Error Log Table Benefits
We are conducting a datawarehouse project, moving data from Staging database to Kimball Data Warehouse.
What is the purpose of creating a custom stored procedure Error Log Table?
I am seeing a lot ...
0
votes
0answers
32 views
SQL Throw with TableErrorLog
I read Raiserror is being deprecated in SQL Server, our team uses an error log table. How would I change our previous code to utilize throw? I want to learn the appropriate modification for legacy ...
0
votes
0answers
501 views
SQL Server Error 14013 - This database is not enabled for publication
I am trying to configure a database publication with T-SQL.
use master
exec sp_replicationdboption
@dbname='anun',
@optname=N'publish',
@value = N'true';
It runs successfully and I see ...
1
vote
0answers
42 views
How get process that is locking Database on SQLite?
I'm developing a iOS app using SQLite. I've got a class to manage SQL's operations, basically read, write and update. In many cases I get a SQLITE_BUSY error, so I want know which process is using the ...
0
votes
1answer
62 views
Find if Stored Procedure Error
Is there a method in T-SQL to see if error occurred in Stored Procedure, without the use of @Output or try catch?
We have a lot of legacy code 200+ stored procedures. I have seen @Output method and ...
1
vote
1answer
42 views
How to index of filter extended event files in Sql Server
I am collecting errors data using the error_reported event and saving the data on XEL files. The problem is that there are hundreds of thousands of errors over there and querying them can take a lot ...
0
votes
2answers
63 views
Impossible to log errors after dooming a transaction within an INSERT-EXEC statement
We have a stored procedure that does some work within a transaction, wrapped within a stored procedure that returns some result data using the INSERT-EXEC method of sharing data.
After observing the ...
1
vote
1answer
97 views
Should SQL Server 2000 throw an error if update statement fails within a SP?
I'm troubleshooting an issue on SQL Server 2000 where it appears that a update statement in a stored procedure failed, and the check for @@ERROR <> 0 didn't catch it, so the transaction was ...
0
votes
2answers
49 views
how to run once a bunch of generated scripts with exception handling?
Before I restore a database, I need to save the contents of a table called dbo.ref_systemSettings if it exists.
So I created a script that calls a procedure that will save the contents of this table ...
0
votes
1answer
48 views
job send email to developers upon complexion and include DBAs if it fails
I have a job that sends an email to an operator (which contains a list of emails within it) upon complexion.
Some of these people that are on that list are the developers who build everything in that ...
0
votes
0answers
174 views
SSIS: Using Event Viewer to Trace Errors of A Package
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 ...
0
votes
1answer
931 views
SQL Server TRY..CATCH resume on terminating error
I'm trying to get a master stored procedure to execute through to completion, where the master SP calls child SPs to perform modular tasks that are independent of each other (i.e. - if one fails, that ...
2
votes
4answers
146 views
SQL Server - Any way to validate insert statements for mismatching column names?
I like to be verbose in my coding, so my typical insert statement looks like this:
INSERT INTO MyTable
(
Column1
,Column2
,Column3
,Column4
,Column5
,Column6
)
SELECT
...
0
votes
0answers
361 views
SQL Server - What is the RAISERROR substitution parameter for boolean/bit type?
I'd like to quickly dump some context into an error message including some boolean flags using the handy-dandy substitution parameters of RAISERROR such as
CREATE PROCEDURE MyProc
(
@param1 INT = ...
0
votes
1answer
318 views
SQL Server Profiler Exception tracing - Error procedure and line number omitted
Is there a trace configuration or setting that will enable the profiler to output ERROR_PROCEDURE() and ERROR_LINE() information that is always available when writing manual error handling in CATCH ...
1
vote
2answers
4k views
How to rollback a SQL Server transaction and use output param?
I am trying to set an output param in a stored procedure, but I think that when I rollback the transaction I think I'm also rolling back the assignment to @out. Is that correct?
If so, how can I ...
-1
votes
2answers
2k views
How to avoid primary key violation while doing inserts
I have a code which does insert into destination table from source, but at times we are ending up with deadlocks.
Due to deadlock , I have to rerun this code again.
while i do re-run , I end up with ...
5
votes
4answers
1k views
PostgreSQL alternative to SQL Server’s `try_cast` function
Microsoft SQL Server has what I consider a remarkably sensible function, try_cast() which returns a null if the cast is unsuccessful, rather than raising an error.
This makes it possible to then use ...
2
votes
1answer
710 views
MySQL Stored Procedure not throwing error if it contains a query that succeeds
I am writing a simple MySQL Stored Procedure, and calling it from PHP using PDO.
This is just simple enough to illustrate the problem I'm having.
This procedure test_procedure_2, does not throw any ...
3
votes
2answers
4k views
SSPI handshake failed with error code 0x8009030c, state 14
DESCRIPTION: SSPI handshake failed with error code 0x8009030c, state 14 while establishing a connection with integrated security; the connection has been closed. Reason: AcceptSecurityContext ...
0
votes
1answer
107 views
How to find out which records are causing an exception in a select statement?
I have a select query that I use as part of an insert statement,
but it raises the following exception:
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value
...
3
votes
0answers
137 views
Any way to catch when xp_readerrorlog fails? [duplicate]
I'm trying to query the ErrorLog files, but it sometimes fails on some of my servers.
For some reason a file go missing from time to time. I do not know why. I do not think that I deleted the file :-)....
7
votes
1answer
3k views
Output parameter not set if stored procedure fails inside a TRY / CATCH
In SQL Server 2008 (but also in 2014). Let's consider a procedure that has an output parameter. This procedure may produce an error (and will in the following example). I note that the behaviour of ...
2
votes
0answers
43 views
Logging multiple error message in the TRY…CATCH when doing BACKUP DATABASE [duplicate]
When I try below:
backup database not_existing_db to disk = 'E:\Backup\not_existing_db.bak'
SSMS shows two error messages:
Msg 911, Level 16, State 11, Line 22
Database 'not_existing_db' does not ...
0
votes
1answer
3k views
Execute pgScript in pgAdmin 4 while skipping errors
In pgAdmin3, via the SQL Editor, there is an Execute pgScript button which (unlike Execute Query) would run through the entire script, skipping errors. The SQL editor in pgAdmin4 doesn't appear have ...
1
vote
3answers
483 views
Purpose of Try/Catch without Transactions
I have come across the following code (SQL Server 2016, 2008-compatible database):
CREATE PROCEDURE myProc
AS BEGIN
DECLARE @ERRORMESSAGE As VARCHAR(1000)
BEGIN TRY
DELETE FROM ...
0
votes
1answer
1k views
drop table in a begin try begin catch block - is there any loss of performance?
The code below is a dynamic sql that will give you a list of all alerts in your system.
It works.
There is a temp table in the code for reasons beyond this question.
This question is related to the ...
2
votes
1answer
3k views
How to handle errors in a transaction in a stored procedure?
I need to catch errors in a transaction in a stored procedure and log them in a table in the catch block.
After starting the transaction, a loop will attempt to insert a handful of values in the ...
1
vote
1answer
1k views
Stored Procedure RAISERROR - Transaction Count Msg Returned
I have a stored procedure that should return the below RAISERROR message when called. However, the text is appended to the message when returned:
Transaction count after EXECUTE indicates a ...
2
votes
1answer
1k views
ODBC Connection Lost… Close Database
I have a Access 2013 frontend linked to a 2014 SQL server. When users leave their workstation with their copy of the frontend open their computer will go often into sleep mode and lose their ODBC ...
4
votes
2answers
501 views
Can I let PostgreSQL report the offending rows when a multi-row INSERT fails because of mismatched geometry types?
Inserting multiple rows into a table with a single INSERT INTO statement fails (as expected) when some of these rows have values of a PostGIS geometry type incompatible to the respective row's PostGIS ...
1
vote
1answer
81 views
Can I let PostgreSQL report the offending rows when a multi-row INSERT fails?
When inserting multiple rows into a table by a single INSERT INTO invocation, and the INSERT statement fails due to reasons specific to some individual rows (e.g. a value has the wrong type for the ...
1
vote
1answer
48 views
SQL Server Try Catch Transastion
I need to know if the T-SQL below is correct to roll back if something bad happens.
I first create a temp table
IF OBJECT_ID(N'tempdb..#LocalSummary') IS NOT NULL
DROP TABLE #LocalSummary
...
1
vote
1answer
6k views
how to handle foreign key constraints on a merge statement?
I am currently doing a merge on the following table between my test_db (target) and the same table on a different database called tablebackups (source).
IF OBJECT_ID('[dbo].[tblBCatalogueType]') ...
1
vote
1answer
93 views
how to get any record in any table that would violate a foreign key constraint that would prevent my delete?
basically I am looking for something like:
before you can delete record x from my_table you need to:
1) delete x,y and z from table1
2) delete m and n from table2
3) delete a,b,c from table3
I ...
1
vote
2answers
572 views
Find the violated index on ORA-00001 (dup_val_on_index)
I have a table with several indexes. In my application code there could be instances where any of these indexes could be violated, depending on user input.
I would like to log exactly which index was ...
4
votes
2answers
944 views
How to retain good records when inserting from stored procedure and still catch the error message?
I am trying to catch the error message when inserting records into a temp table from a stored procedure without rolling back everything. To illustrate, here is what I am trying to do:
CREATE TABLE #...
2
votes
2answers
531 views
Postgresql Log message without its statement
If I want to write a message to the Postgres log I can do this:
DO $$
BEGIN
RAISE LOG 'Justin Is Awesome';
END $$;
However, it logs the statement as well so I get 5 lines of output instead of one:
...
0
votes
2answers
370 views
Unique key constraint violation not escalated to parent stored procedure
I have a stored procedure which calls 2 nested sub procedures. For Example,
MyProc
BEGIN
BEGIN TRY
BEGIN TRAN
EXEC UpdateProc
EXEC InsertProc
COMMIT TRAN
END TRY
...
4
votes
1answer
788 views
How can I handle unwanted, non-erroneous situations better?
When writing procedures, I occasionally encounter situations in which I want to abort the procedure, even if the situation didn't necessarily trigger an error.
Let's say, if I don't want John to be ...
0
votes
0answers
49 views
Exclude a Variable from getting logged in a CheckPoint file
I have an SSIS package in SQL Server 2014 which uses checkpoint to handle the cases where it needs to be run after a failure. There is a variable in the package that I want to be set to 0 whenever the ...
3
votes
3answers
874 views
Catching two error messages/Throw into table
My goal is to catch errors from backup jobs into monitoring table.
The problem is, that there are cases that backup statement returns more than one error, so ERROR_MESSAGE() is not enough:
Msg ...
