Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- -- 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
- */
- --------------------------------------------------------------------------------------------
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement