Symptoms:
Incorrect coding may cause SQL errors when executing.
Impact: Medium
SQL Exceptions will interrupt user flow, and potentially may cause a lack of functionality and influence application performance. This can have a strong negative impact on overall system value by degrading the performance and reliability of the data.
Expected behavior :
SQL exception is a problem (usually an error) that prevents the continuation of a program. With such a problem, you cannot continue processing because there is not enough information needed to handle the problem.
Proper application design and programming should trap any possible failure of the coding to execute properly. This is usually done by wrapping all SQL calls in a TRY… CATCH… THROW block which tests whether the SQL statement can execute correctly and if not will perform the required action.
Exceptions reported by AimBetter monitor
Permission Violation: A user is trying to execute some function for which he/she does not have sufficient permission – eg. reading data from a secure table
Syntax exception – may occur if SQL Server cannot execute a command (INSERT or UPDATE or DELETE, etc). For example, if a table name is incorrect, the server would report “SQLServerException: Incorrect syntax near —”.
Overflow exception – doing arithmetic conversions between data types can result in an overflow (for example, converting data type Float into Integer if the result exceeds the upper limit).
Foreign key exception – means that a new key is inserted/updated in the parent table does not also appear in another table that is designated as the child.
Convert exception – conversion between different data types (string to a number, string to date) can fail. (For example data form ‘DateTime’ is more restrictive and will throw an exception if the input does not exactly match the expected format.)
Duplicate key exception – SQL Server cannot insert a duplicate key in a table designated as having a unique index of the key field.
Permission violation – can occur when executing stored procedures if permission has not been granted for the user.
Table corrupted exception – SQL Server may report that a table in the database cannot be accessed because it has become corrupted. This may be due to various reasons, most typically hardware failure but may also be because of an abrupt system shutdown while the database is opened, changes in SQL account or virus infection.
Null exception – reported if an INSERT or UPDATE command is trying to add a NULL value into a table/column where the definition of the column does not allow NULL value.
Parameter exception – indicates that the parameter count in a command (INSERT/UPDATE) does not match the values supplied. (Our suggestion to avoid this possibility is to used Stored Procedures, which are designed to pass parameters and handle the differing requests that are made.)
Login exception – This problem occurs if the user tries to log in with credentials that cannot be validated. This problem can occur if the login may be a SQL Server login but the server only accepts Windows Authentication, or you are trying to connect by using SQL Server Authentication but the login used does not exist on SQL Server.
Background
SQL exceptions occur where a query has been executed but cannot complete. The proper action required will be to investigate the source code that was executing and to correct any errors. Best practice requires that all SQL functions and calls should be properly coded with provision for possible failure via the TRY…CATCH protocol.
