Error codes in SQL are generated by the server to provide information about what has gone wrong. They have different meanings depending on the SQL version you’re using, but they usually indicate the inability to perform a requested operation.
The easiest way to explore it is the simplest programming language called BASIC and its programs like “Hello world”. Type the following into the interface:
PRINT "Hello, World!"If you type PRINT as PRRRR, you’ll get an error message (an equivalent in real life would be someone telling you to drive on the right side of the road, e.g. obeying the rules).
That’s relatively easy when it comes to simple operations, but what about more complex systems? We’re also including SQL code examples here. Enjoy!
The following code imports the necessary functions from the standard library, then creates a console, gets a pointer to its standard output stream, and prints the message to this stream, and releases objects in use:
Option Explicit
Declare Function AllocConsole Lib "kernel32" () As Long
Declare Function FreeConsole Lib "kernel32" () As Long
Declare Function CloseHandle Lib "kernel32" (ByVal hObject As Long) As Long
Declare Function GetStdHandle Lib "kernel32" (ByVal nStdHandle As Long) As Long
Declare Function WriteConsole Lib "kernel32" Alias "WriteConsoleA" _
(ByVal hConsoleOutput As Long, lpBuffer As Any, ByVal _
nNumberOfCharsToWrite As Long, lpNumberOfCharsWritten As Long, _
lpReserved As Any) As Long
Declare Function Sleep Lib "kernel32" (ByVal dwMilliseconds As Long) As Long
Private Sub Main()
'create a console instance
AllocConsole
'get handle of console output
Dim hOut As Long
hOut = GetStdHandle(-11&)
'output string to console output
Dim s As String
s = "Hello, World!" & vbCrLf
WriteConsole hOut, ByVal s, Len(s), vbNull, vbNull
'make a pause to look at the output
Sleep 2000
'close the handle and destroy the console
CloseHandle hOut
FreeConsole
End Sub
If you make an error in this code, it takes a while to find it, especially if you have 500 pages of code like this. So, the computer will find errors and point you to where they are.
What are SQL Error Codes for and How Do they Work?
The usefulness of SQL error codes is that the software finds your code and points to it (example). You don’t have to manually check thousands of code lines yourself. Imagine getting just one error code ever (“Better luck next time, loser!” or “Who taught you how to code, a horse?”) and having to go over the whole project again!
In a sense, error codes are excellent Santa’s little helpers: clear, useful, and save tons of time. You just have to get to know them.
Starting at the basic level, databases are collections of information put together and classified. The type we’re interested in is relational databases, meaning there are relationships between the cells the data is stored in.
For example, you may have a whole group of students in a university and want to award everyone who got more than 90% at their exams with a prize. You could manually write their names, genders, addresses, bank account numbers (these are all related, hence relational databases), grades, and then manually choose the ones with high scores.
Archaic? Yes, but you’d be surprised to learn how many companies still do business this way in the 21st century. It could take months to complete operations that would take a computer seconds to do. Especially if we’re talking about hundreds or thousands of students.
Now, putting all these students in a database you could use a language like SQL:
SELECT * FROM Student WHERE Percentage>=90;Done! The problems, however, begin when you write up your code.
Understandably it gets very complex, so the more you write, the higher are the chances that your code contains errors. Here, error codes will be most useful. When we see error codes, we should be grateful (which doesn’t stop us from swearing at them every single time). They do all the legwork for you, and all you need to do is to go to the source and fix the problem.
Give me Some Specifics!
When a database fails to produce the desired results, an error code is thrown. It helps identify the problem and how to fix it. SQL Error Codes, like the ones below, will be useful when troubleshooting any issues with your databases:
Oracle9i Database Error Messages
Release 2 (9.2)
ORA-00904: “specified number of rows exceeds maximum”.
ORA-00900: “insufficient privileges on object”.
ORA-00900: “invalid SQL statement”.
ORA-00902: “invalid datatype”.
We have many different types of errors in SQL. But if you aren’t going to frame them and put them on your wall, you need to know how to deal with them. The good thing is, SQL errors come with a link to the precise location of the error in a code, and also provide information about what’s wrong.
Let’s start with the simplest error example:
ORA-00900: “invalid SQL statement”.
As you’ve probably guessed, you need to write up the command properly. There could be a typo or a comma somewhere it doesn’t belong. Or, as the case may be, you may need to install additional software:
“The statement is not recognized as a valid SQL statement.
This error can occur if the Procedural Option is not installed and a SQL statement is issued that requires this option (for example, a CREATE PROCEDURE statement). You can determine if the Procedural Option is installed by starting SQL*Plus. If the PL/SQL banner is not displayed, then the option is not installed.
Action: Correct the syntax or install the Procedural Option”.
The same goes for the type of formatting or putting wrong data types where they don’t belong:
ORA-00902 invalid datatype
“Cause: The datatype entered in the CREATE or ALTER TABLE statement is not valid.
Action: Correct the syntax”.
You can find more information in Oracle documentation.
SQL error codes are the most common way to debug SQL queries. Whenever you query the database, and the issue happens, the database engine generates the SQL error code. These codes represent the location of the problem in the query and give programmers information on how to fix it or how to interpret what caused it.
Main Types of Errors
Format Errors
For example, when you’re using SELECT, you need to follow it with a certain sequence (list the columns in the table, which contains our students and their grades), then a star. If you don’t follow the format, swap a star and a comma around, you’ll get an error message.
“if there’s a BEGIN TRANSACTION, it always must end with a COMMIT or ROLLBACK transaction.”
Another example: after FROM you use operators like WHERE which requires a condition. It can be any condition, including conditions that extract data, e.g., all students with grades under 30. If you leave this field blank, you’ll get a format error.
Operator Error
Commands need to be compatible with SQL. You can’ include SUM and COUNT with WHERE. Otherwise, you get an error.
Procedural Errors
Procedural code is the code stored on the server that you can use for your purposes with minor changes.
“A stored procedure is a prepared SQL code that you can save, so the code can be reused over and over again… So if you have an SQL query that you write over and over again, save it as a stored procedure, and then just call it to execute it.” (W3 Schools)
To create a stored procedure from a table called Customers with all its records and execute it, we have the following code:
CREATE PROCEDURE SelectAllCustomers
AS
SELECT * FROM Customers
GO;
EXEC SelectAllCustomers;
In other words, procedures are like templates stored on a server that you can take, amend, and use as needed.
Procedural errors are more or less usual kinds of errors. The difference is, they refer not to a single line of code but to the whole procedure (template) which you took and tried to change slightly.
Imagine you have two tables, one empty and one full. You apply the below code to the empty table:
INSERT to transfer data
SELECT and WHERE to choose data
An example of a strategic error would be using operators like IN and NOT IN operators. It is tempting yet not very well optimized (using JOIN is a much better strategic choice).
Fatal and Non-fatal Errors
A database like MySQL or PostgreSQL stores data in tables, which are made up of rows and columns. Database queries are SQL commands that tell the database what to do with its data. They could be as simple as selecting all records from a table, or complex enough to create an entirely new table.
There are two types of errors that can occur when using these commands: fatal and non-fatal.
A fatal error stops the execution of a statement, while a non-fatal error does not.
A fatal error is a database error that cannot be fixed. A non-fatal error is an issue that can be resolved in some way, for example by restarting the SQL Server service or the instance of SQL Server.
A database may have both fatal and non-fatal errors at any given time for many reasons. Sometimes, if you are aware of the issue, it’s possible to resolve it without too much difficulty. Other times, not so much.
The most common type of error is a syntax or other runtime issue with the database system that accesses data from the SQL table. These errors can be caught in testing before running the code. You can ensure that everything works properly when executed against the database system.
Creating your Error Codes with RAISERROR
“There is a RAISERROR function that can be used to generate our custom error messages which is a great way to translate confusing error messages into something a little bit more meaningful that people would understand.”
The RAISERROR function is a SQL server system command that can raise an error message. You can use it to indicate errors, warnings, or informational messages.
Errors can be caused by the programmer or by SQL Server itself. It is useful for providing feedback when something goes wrong, but also when something needs to happen without interrupting the execution of other statements in the batch.
Use the following syntax:
RAISERROR ( [ error_number ] , [ message ], [ state ])You can also apply RAISERROR to either terminate the execution of a statement or to pass on errors generated by SQL statements to another application. For instance, you can raise an error message that will cause the execution of the current batch or statement to stop, as well as display the specified message.
The most common use for RAISERROR is to generate an error message when data doesn’t meet some criteria, like entering too many characters into a field that only allows 50 characters.
Raiserror(msg) is useful for handling errors that occur during processing, and it does not require the entire transaction to fail because of one individual error.
Now you can create as many errors of your own as you want. Rejoice!
Dealing with Errors
To deal with errors, we must be able to control them and find out all the related information. It is necessary for any case that is more complicated than mistyping PRINT in “Hello World”. If your database is key to your web presence, then troubleshooting issues with it promptly is a must, as solving a critical error on your website will earn visitor trust, rather than risking your reputation.
One useful way of catching errors is using TRY…CATCH. This tool allows you to take your code and place it in an environment where it can be examined and handled safely. There, you can extract the data from it. Decide if you want to report the error, find out more about it, or fix it.
This SQL server sandbox goes like this:
BEGIN TRY
--code to try
END TRY
BEGIN CATCH
--code to run if an error occurs
--is generated in try
END CATCH
The code you want to watch is placed between BEGIN TRY and END TRY. If mistakes happen, it gets sent to the CATCH statement. This provides us with a lot of useful functionality:
- ERROR_NUMBER returns the internal number of the error
- ERROR_STATE returns the information about the source
- ERROR_SEVERITY returns the information about anything from informational errors to errors users of DBA can fix, etc.
- ERROR_LINE returns the line number at which an error happened on
- ERROR_PROCEDURE returns the name of the stored procedure or function
- ERROR_MESSAGE returns the most essential information and that is the message text of the error.
Here’s what we get when we try to divide 1 by 0:
USE AdventureWorks2014
GO
-- Basic example of TRY...CATCH
BEGIN TRY
-- Generate a divide-by-zero error
SELECT
1 / 0 AS Error;
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_STATE() AS ErrorState,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_PROCEDURE() AS ErrorProcedure,
ERROR_LINE() AS ErrorLine,
ERROR_MESSAGE() AS ErrorMessage;
END CATCH;
GO
As you can see, the TRY…CATCH function is very useful.
Summary
Now you know exactly what SQL error code is, what types of errors there are, why they happen, how they are sandboxed and studied, how to create your error signals, etc. You’re far more than just prepared to deal with errors! If not, let us know, and we’ll release more guides in the future. Good luck!
Tags: sql errors, sql server Last modified: October 10, 2023
