If you’re getting SQL Server error Msg 529 that reads something like Explicit conversion from data type int to xml is not allowed, it’s probably because you’re trying to perform a data type conversion that’s not permitted.
SQL Server doesn’t allow certain conversions. If you try to perform such a conversion, you’ll get this error.
If you’re getting SQL Server error Msg 8116 with the message Argument data type varchar is invalid for argument 1 of session_context function, it’s because you’re passing the wrong data type to a function – in this case the SESSION_CONTEXT() function.
In SQL, the columns information schema view, which returns information about columns, is an ISO standard view that is supported by most of the major RDBMSs. You can use this view to get information about a column’s data type.
Most of the major RDBMs also provide other methods for getting column information.
Here are examples of getting the data type of a column in various SQL databases.
If you’re getting SQL Server error Msg 8117 with the message Operand data type varchar is invalid for sum operator, it’s because you’re passing the wrong data type to an operator or function.
In this case, the error indicates that we’re passing a string to the SUM() function. The SUM() function does not operate on strings. It only works on numeric types.
The same error (Msg 8117) can also occur in other contexts – it’s not limited to the SUM() function.
Sometimes you might need to search a database table for only those rows that contain at least one number in a given column.
Strictly speaking, numbers can be represented by words and other symbols, but for the purpose of this article, “number” means “numerical digit”.
Below is an example of how to use T-SQL to find rows that contain at least one number in SQL Server.
There may be occasions where you need to check a column for non-numeric values. For example, you discover that a column is a varchar column when it really should be a numeric column.
This is easily done in SQL Server with the ISNUMERIC() function.
Here are some examples of formatting phone numbers in SQL Server.
This includes examples of formatting numbers in E.164 format (for international numbers), prepending the country code and area code, as well as omitting leading zero from the country code when required.
In SQL Server, the SUM() aggregate function returns the sum of a given expression.
It can also be used to return the sum of all distinct (unique) values in the expression.
SUM() only works on numeric columns. Null values are ignored.
If you’re getting SQL Server error Msg 8116 with text that reads Argument data type date is invalid for argument 1 of substring function, it’s because you’re passing the wrong data type to a function – in this case, the SUBSTRING() function.
You could also see the same error (Msg 8116) in many other contexts – it’s not limited to the SUBSTRING() function.
If you’re getting SQL Server error Msg 8114 that reads something like Error converting data type varchar to numeric, it’s probably because you’re trying to perform a data type conversion that fails due to the value not being able to be converted to the destination type.
It’s not because you can’t convert that type to the new type. It’s because of the value itself.