MySQL TinyInt treated as Boolean

When working with a MySQL database as a source you may experience some unexpected results in your data extracts. Most of the issues are related with handling of certain data types such as “TinyInt”.

A particular issue I recently came upon was the fact that TinyInt(1) is treated by default in SSIS as a “Boolean”. You can see this setting in the ADO.Net provider configuration under the All section as shown in the image below.

Columns with data type of Boolean are expected to have values of either 1 (True) or 0 (False) or Null. But, TinyInt(1) data type allows values other than 1 or 0. At this particular client, columns defined as TinyInt(1) had values ranging from 1 to 9. Because of the default setting in the ADO.Net driver, only values of 1 and 0 were coming through as all values that were not 1 were turned into 0.

As a recommendation when working with MySQL, check for data types like these that could be interpreted differently depending on your driver. A good practice is too refer to an existing data dictionary or profile the data source before you start coding away. Also, check the documentation online, in particular for MySQL you can find it at http://dev.mysql.com/doc

As a side note, also check the values allowed for each data type. TinyInt allows different range of values depending if it is signed and unsigned. For example, in MySQL signed TinyInt allows values ranging from -128 to 127 while unsigned allows values from 0 to 255. SQL Server only allows values of 0 to 255 for TinyInt. In other words, SQL Server only supports unsigned TinyInt. You will need to use SmallInt at a minimum in SQL Server to support signed Tinyint data types.

There is an additional type of integer in MySQL called “MediumInt” that allows values greater then “SmallInt” but smaller than “Int”. In this case you will need to use Int data type in SQL Server at a minimum.

MySQL Integer (Exact Value) and its variations data type documentation can be found at:
https://dev.mysql.com/doc/refman/5.0/en/integer-types.html

SQL Server Integer and its variations data type documentation can be found at:
https://msdn.microsoft.com/en-us/library/ms187745.aspx

Advertisement

Useful queries #3: Get SQL Server Edition, Version, Build and Last Update date and time.

Keeping track of all the versions and editions SQL Server instances can be a daunting task if you don’t have the right tools and documentation. A very easy way to obtain version, edition and build information from your SQL Servers is by querying the SERVERPROPERTY function as follows:

SELECT
SERVERPROPERTY(‘Edition’) AS ‘Edition’,
SERVERPROPERTY(‘ProductVersion’) AS ‘ProductVersion’,
SERVERPROPERTY(‘ProductLevel’) AS ‘ProductLevel’,
SERVERPROPERTY(‘ResourceLastUpdateDateTime’) AS ‘LastUpdate’, SERVERPROPERTY(‘ResourceVersion’) AS ‘Version’

Additional info: http://support.microsoft.com/kb/321185

No way.

No way.

T-SQL UPPER(): Change an all lowercase string to uppercase

The T-SQL UPPER() command allows you to change a lowercase string to an uppercase string.

For example, it will allow you to change the word hello to HELLO.

Example 1:

Declare @MyVar varchar(50);
Set @Myvar=’hello’;
Select UPPER(@MyVar)   => Output will be HELLO in uppercase.

You can use the T-SQL UPPER() command with other commands to Capitalize only the first letter of a word.

For example, change florida to Florida.

Example2:

DECLARE @MyVar varchar(50);
SET @Myvar=’florida’;
SELECT UPPER(LEFT(@MyVar,1)) +SUBSTRING(@MyVar,2,49)

Since my string is of length 50, notice that I am selecting the first letter and converting it to upper case and concatenating with the rest 49 letters that are already in lowercase starting with the second position in the string.