-- These examples support the following bug report for SQL Server 2019 CTP 2.0: -- https://feedback.azure.com/forums/908035-sql-server/suggestions/35606671-utf-8-in-sql-2019-an-error-occurred-while-executi -- By: Solomon Rutzky -- On: 2018-10-15 -- https://SqlQuantumLeap.com/ ----------------------------------------------------------------------- IF (DB_ID(N'UTF8') IS NULL) BEGIN CREATE DATABASE [UTF8] COLLATE Latin1_General_100_CI_AS_SC_UTF8; ALTER DATABASE [UTF8] SET RECOVERY SIMPLE; END; GO ----------------------------------------------------------------------- -- !! HIGHLIGHT EACH QUERY AND THE "USE" STATEMENT ABOVE IT TO EXECUTE INDIVIDUALLY !! USE [UTF8]; ;WITH nums AS ( SELECT TOP (1) ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS [num] FROM [master].[sys].[columns] col ) SELECT CONVERT(CHAR(1), NULL), CONVERT(BIT, 1), CONVERT(BIT, 1), 4, CONVERT(BIT, 1), --CONVERT(BIT, 1), --CONVERT(BIT, 1), CONVERT(BIT, 1), CONVERT(BIT, 1), CONVERT(BIT, 1) FROM nums chr USE [UTF8]; ;WITH nums AS ( SELECT TOP (100) ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS [num] FROM [master].[sys].[columns] col ) SELECT CONVERT(CHAR(1), NULL), CONVERT(BIT, 1),--1, CONVERT(BIT, 1),--2, 4, 5,--CONVERT(BIT, 1),--3, CONVERT(BIT, 1),--4, CONVERT(BIT, 1),--5, CONVERT(BIT, 1),-- CONVERT(BIT, 1),--6, CONVERT(BIT, 1) --7 FROM nums chr -- ā NULL NULL 1280 16843008 1 1 1 1 1 USE [UTF8]; ;WITH nums AS ( SELECT TOP (20) ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS [num] FROM [master].[sys].[columns] col ) SELECT CONVERT(CHAR(1), Null) AS [NULL UTF8], CONVERT(int, 1) AS [1], CONVERT(int, 0) AS [2], 0 AS [3], 0 AS [4], NEWID() AS [5], CONVERT(INT, 1) AS [6], CONVERT(BIGINT, 1) AS [7], CONVERT(BIGINT, 1) AS [8] FROM nums chr -- An error occurred while executing batch. Error message is: Offset and length were out of bounds for the array or count is greater than the number of elements from index to the end of the source collection. USE [UTF8]; ;WITH nums AS ( SELECT TOP (20) ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS [num] FROM [master].[sys].[columns] col ) SELECT CONVERT(CHAR(1), Null) AS [NULL UTF8], CONVERT(BIGINT, 1) AS [1], CONVERT(INT, 0) AS [2], 0 AS [3], 0 AS [4], NEWID() AS [5], CONVERT(INT, 1) AS [6], CONVERT(BIGINT, 1) AS [7], CONVERT(BIGINT, 1) AS [8] FROM nums chr -- An error occurred while executing batch. Error message is: Invalid array size. -------------------------- -- In each of the following tests (which moves the [NULL UTF8] column incrementally from first to last position), columns to the left of [NULL UTF8] display correctly, while [NULL UTF8] displays as an empty string, and columns to the right of [NULL UTF8] display incorrectly in varying ways depending on the position of the [NULL UTF8] column and sometimes even the particular execution. USE [UTF8]; SELECT TOP (20) CONVERT(CHAR(1), NULL) AS [NULL UTF8], CONVERT(INT, 1) AS [1], CONVERT(INT, 0) AS [2], 0 AS [3], 0 AS [4], NEWID() AS [5], CONVERT(INT, 1) AS [6], CONVERT(BIGINT, 1) AS [7], CONVERT(BIGINT, 1) AS [8] FROM [master].[sys].[columns] col -- An error occurred while executing batch. Error message is: Offset and length were out of bounds for the array or count is greater than the number of elements from index to the end of the source collection. --------------------------------------------- USE [UTF8]; SELECT TOP (20) CONVERT(INT, 1) AS [1], CONVERT(CHAR(1), NULL) AS [NULL UTF8], CONVERT(INT, 0) AS [2], 0 AS [3], 0 AS [4], NEWID() AS [5], CONVERT(INT, 1) AS [6], CONVERT(BIGINT, 1) AS [7], CONVERT(BIGINT, 1) AS [8] FROM [master].[sys].[columns] col -- An error occurred while executing batch. Error message is: Invalid array size. --------------------------------------------- USE [UTF8]; SELECT TOP (20) CONVERT(INT, 1) AS [1], CONVERT(INT, 0) AS [2], CONVERT(CHAR(1), NULL) AS [NULL UTF8], 0 AS [3], 0 AS [4], NEWID() AS [5], CONVERT(INT, 1) AS [6], CONVERT(BIGINT, 1) AS [7], CONVERT(BIGINT, 1) AS [8] FROM [master].[sys].[columns] col -- An error occurred while executing batch. Error message is: Offset and length were out of bounds for the array or count is greater than the number of elements from index to the end of the source collection. --------------------------------------------- USE [UTF8]; SELECT TOP (20) CONVERT(INT, 1) AS [1], CONVERT(INT, 0) AS [2], CONVERT(CHAR(1), NULL) AS [NULL UTF8], 0 AS [3], 0 AS [4], NEWID() AS [5], CONVERT(INT, 1) AS [6], CONVERT(BIGINT, 1) AS [7], CONVERT(BIGINT, 1) AS [8] FROM [master].[sys].[columns] col -- An error occurred while executing batch. Error message is: Invalid array size. --------------------------------------------- USE [UTF8]; SELECT TOP (20) CONVERT(INT, 1) AS [1], CONVERT(INT, 0) AS [2], 0 AS [3], CONVERT(CHAR(1), NULL) AS [NULL UTF8], 0 AS [4], NEWID() AS [5], CONVERT(INT, 1) AS [6], CONVERT(BIGINT, 1) AS [7], CONVERT(BIGINT, 1) AS [8] FROM [master].[sys].[columns] col -- An error occurred while executing batch. Error message is: Invalid array size. --------------------------------------------- USE [UTF8]; SELECT TOP (20) CONVERT(INT, 1) AS [1], CONVERT(INT, 0) AS [2], 0 AS [3], 0 AS [4], CONVERT(CHAR(1), NULL) AS [NULL UTF8], NEWID() AS [5], CONVERT(INT, 1) AS [6], CONVERT(BIGINT, 1) AS [7], CONVERT(BIGINT, 1) AS [8] FROM [master].[sys].[columns] col -- (query never returns; had to close query tab, answer "yes" to "cancel the query?") --------------------------------------------- USE [UTF8]; SELECT TOP (20) CONVERT(INT, 1) AS [1], CONVERT(INT, 0) AS [2], 0 AS [3], 0 AS [4], NEWID() AS [5], CONVERT(CHAR(1), NULL) AS [NULL UTF8], CONVERT(INT, 1) AS [6], CONVERT(BIGINT, 1) AS [7], CONVERT(BIGINT, 1) AS [8] FROM [master].[sys].[columns] col -- An error occurred while executing batch. Error message is: Internal connection fatal error. Error state: 15, Token : 4 --------------------------------------------- USE [UTF8]; SELECT TOP (20) CONVERT(INT, 1) AS [1], CONVERT(INT, 0) AS [2], 0 AS [3], 0 AS [4], NEWID() AS [5], CONVERT(INT, 1) AS [6], CONVERT(CHAR(1), NULL) AS [NULL UTF8], CONVERT(BIGINT, 1) AS [7], CONVERT(BIGINT, 1) AS [8] FROM [master].[sys].[columns] col -- An error occurred while executing batch. Error message is: Internal connection fatal error. Error state: 15, Token : 0 --------------------------------------------- USE [UTF8]; SELECT TOP (20) CONVERT(INT, 1) AS [1], CONVERT(INT, 0) AS [2], 0 AS [3], 0 AS [4], NEWID() AS [5], CONVERT(INT, 1) AS [6], CONVERT(BIGINT, 1) AS [7], CONVERT(CHAR(1), NULL) AS [NULL UTF8], CONVERT(BIGINT, 1) AS [8] FROM [master].[sys].[columns] col -- An error occurred while executing batch. Error message is: Internal connection fatal error. Error state: 15, Token : 122 --------------------------------------------- USE [UTF8]; SELECT TOP (20) CONVERT(INT, 1) AS [1], CONVERT(INT, 0) AS [2], 0 AS [3], 0 AS [4], NEWID() AS [5], CONVERT(INT, 1) AS [6], CONVERT(BIGINT, 1) AS [7], CONVERT(BIGINT, 1) AS [8], CONVERT(CHAR(1), NULL) AS [NULL UTF8] FROM [master].[sys].[columns] col -- (query never returns; had to close query tab, answer "yes" to "cancel the query?") --------------------------------------------- --------------------------------------------- USE [UTF8]; SELECT TOP (100) CONVERT(BIT, 1) AS [1], CONVERT(BIT, 1) AS [2], CONVERT(BIT, 1) AS [3], CONVERT(BIT, 1) AS [4], CONVERT(CHAR(1), NULL) AS [NULL], CONVERT(BIT, 1) AS [5], NCHAR(50505) + NCHAR(20202) + N'gf' + NCHAR(10101) + NCHAR(66666) AS [6], 'abcdefghijklmnoptuvwxyz' AS [7], CONVERT(BIGINT, 1) AS [8] FROM [master].[sys].[columns] col -- Returned 1 row of the following, and errored with the message shown below the result row: -- 1 1 1 1 앉仪gf❵𐑪.abcdefghijklmnoptuvwxyzĈ 1 f❵𐑪.abcdefghijklmnoptuvwxyzĈ fghijklmnoptuvwxyzĈ 8430866047665467136 -- An error occurred while executing batch. Error message is: Internal connection fatal error. Error state: 15, Token : 0 USE [UTF8]; SELECT TOP (100) CONVERT(BIT, 1) AS [1], CONVERT(int, 1) AS [2], -- changed to "int" CONVERT(BIT, 1) AS [3], CONVERT(BIT, 1) AS [4], CONVERT(CHAR(1), NULL) AS [NULL], CONVERT(BIT, 1) AS [5], NCHAR(50505) + NCHAR(20202) + N'gf' + NCHAR(10101) + NCHAR(66666) AS [6], 'abcdefghijklmnoptuvwxyz' AS [7], CONVERT(BIGINT, 1) AS [8] FROM [master].[sys].[columns] col; -- 1 1 1 1 앉仪gf❵𐑪.abcdefghijklmnoptuvwxyzĈ 1 ก䤀李昀甀ħ櫘⻜愀戀挀搀攀昀最栀椀樀欀氀洀渀漀瀀琀甀瘀眀砀礀稀ࠀ āā앉仪gf❵𐑪.abcdefghijklmnoptuvwxyzĈ NULL USE [UTF8]; SELECT TOP (100) CONVERT(BIT, 1) AS [1], CONVERT(BIT, 1) AS [2], -- changed back to "bit" CONVERT(BIT, 1) AS [3], CONVERT(int, 4) AS [4], -- changed to "int" / 4 CONVERT(CHAR(1), NULL) AS [NULL], CONVERT(BIT, 1) AS [5], NCHAR(50505) + NCHAR(20202) + N'gf' + NCHAR(10101) + NCHAR(66666) AS [6], 'abcdefghijklmnoptuvwxyz' AS [7], CONVERT(BIGINT, 1) AS [8] FROM [master].[sys].[columns] col -- 1 1 1 4 앉仪gf❵𐑪.abcdefghijklmnoptuvwxyzĈ NULL 앉仪gf❵𐑪.abcdefghijklmnoptuvwxyzĈ NULL USE [UTF8]; SELECT TOP (100) CONVERT(BIT, 1) AS [1], CONVERT(BIT, 1) AS [2], CONVERT(BIT, 1) AS [3], 4 AS [4], -- removed explicit CONVERT() CONVERT(CHAR(1), NULL) AS [NULL], CONVERT(BIT, 1) AS [5], NCHAR(50505) + NCHAR(20202) + N'gf' + NCHAR(10101) + NCHAR(66666) AS [6], 'abcdefghijklmnoptuvwxyz' AS [7], CONVERT(BIGINT, 1) AS [8] FROM [master].[sys].[columns] col -- Returned 25 rows (not 100) of the following, saying "query completed successfully" -- 1 1 1 4 앉仪gf❵𐑪.abcdefghijklmnoptuvwxyzĈ 1 앉仪gf❵𐑪 abcdefghijklmnoptuvwxyz 1 --------------------------------------------- --------------------------------------------- -- THE FOLLOWING TEST PROVES THAT THE ISSUE IS NOT TIED TO THE DEFAULT COLLATION OF THE CURRENT -- DATABASE. SINCE YOU AREN'T SUPPOSED TO BE ABLE TO USE A UTF8 COLLATION AT THE INSTANCE LEVEL, -- [master] SHOULD BE GUARANTEED TO NOT BE USING A "_UTF8" COLLATION. -- AND THE ISSUE IS NOT THE SIZE OF THE COLUMN SINCE "CHAR(10)" SHOULD BE PLENTY BIG ENOUGH. USE [master]; SELECT TOP (100) CONVERT(BIT, 1) AS [1], CONVERT(int, 1) AS [2], -- changed to "int" CONVERT(BIT, 1) AS [3], CONVERT(int, 1) AS [4], -- changed to "int" CONVERT(CHAR(10), NULL) COLLATE Latin1_General_100_CI_AS_SC_UTF8 AS [NULL], -- added COLLATE CONVERT(BIT, 1) AS [5], NCHAR(50505) + NCHAR(20202) + N'gf' + NCHAR(10101) + NCHAR(66666) AS [6], 'abcdefghijklmnoptuvwxyz' AS [7], CONVERT(BIGINT, 1) AS [8] FROM [master].[sys].[columns] col; -- Returned over 13 rows of the following (the 13th row was the only row that was different): /* 1 2 3 4 NULL 5 6 7 8 ------------------------------------------------------------------------------------- 1 1 1 1 앉仪gf❵𐑪扡摣晥桧橩汫湭灯畴睶祸ࡺ NULL  NULL .... 1 1 1 1 앉仪gf❵𐑪扡摣晥桧橩汫湭灯畴睶祸 0 NULL */ --------------------------------------------------------------------------------------------