Below are three ways we can use T-SQL to return a list of databases in SQL Server.
Continue readingCategory: SQL Server
Fix “Arithmetic overflow error converting int to data type numeric” in SQL Server
If you’re receiving error Msg 8115, Level 16, Arithmetic overflow error converting int to data type numeric in SQL Server, it’s probably because you’re performing an operation that results in a data conversion error due to an out of range value.
This will often happen when you try to convert a number to a different data type, but it’s out of the accepted range for the new data type.
Continue readingFix “Arithmetic overflow error converting expression to data type int” in SQL Server
If you’re receiving error Msg 8115, Level 16, Arithmetic overflow error converting expression to data type int in SQL Server, it could be that you’re performing a calculation that results in an out of range value.
This can happen when you use a function such as SUM() on a column, and the calculation results in a value that’s outside the range of the column’s type.
Fix “Arithmetic overflow error converting IDENTITY to data type…” in SQL Server
If you’re getting error “Msg 8115, Level 16 Arithmetic overflow error converting IDENTITY to data type…” error in SQL Server, it’s probably because you’re trying to insert data into a table when its IDENTITY column has reached its data type’s limit.
An IDENTITY column automatically increments the value that’s inserted with each new row. If the value being inserted is out of the range of the column’s data type, then the above error will occur.
3 Ways to Get a Column’s Data Type in SQL Server (T-SQL)
GUIs like SSMS or Azure Data Studio make it easy to see a column’s data type. Usually it’s a simple matter of navigating to the column in the object explorer and you can see the data type right next to the column.
But if you’re using T-SQL, you’ll need to run a query.
Continue readingFormat a Number as a Percentage in SQL
There are several ways to format a number as a percentage in SQL, depending on the DBMS being used.
Here are examples of adding a percent sign to a number in some of the most popular DBMSs.
Continue readingHAS_DBACCESS() – Discover if a User Can Access a Database in SQL Server
SQL Server has a HAS_DBACCESS() function that returns information about whether the user has access to a specified database.
SQL LPAD()
In SQL, LPAD()is a commonly used function that pads the left part of a string with a specified character. The function can be used on strings and numbers, although depending on the DBMS, numbers may have to be passed as a string before they can be padded.
DBMSs that have an LPAD() function include MySQL, MariaDB, PostgreSQL, and Oracle.
DBMSs that don’t have an LPAD() function include SQL Server and SQLite (although there are other ways to apply left padding in these DBMSs).
SQL RPAD()
In SQL, RPAD()is used to pad the right part of a string with a specified character. The function can be used on strings and numbers, although depending on the DBMS, numbers may have to be passed as a string before they can be padded.
DBMSs that have an RPAD() function include MySQL, MariaDB, PostgreSQL, and Oracle.
DBMSs that don’t have an RPAD() function include SQL Server and SQLite.
Fix “Conversion failed when converting the varchar value” When Trying to Concatenate in SQL Server
If you get error Msg 245 that reads “Conversion failed when converting the varchar value…” when trying to perform a concatenation operation in SQL Server, it’s likely that you’re attempting to concatenate a string and a number.
Doing this will result in an error, due to SQL Server trying to add the string and number instead of concatenate them.
To fix this, either convert the number to a string, or use a function like CONCAT() or CONCAT_WS() to perform the concatenation.