UTF8_Bug_35606671_SQLCLR-Tests

-- 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-16
-- 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
-----------------------------------------------------------------------


--========================================================================
--
-- THE FOLLOWING TESTS USE A SQLCLR FUNCTION, DB_BulkCopy, WHICH IS A
-- WRAPPER FOR THE .NET SqlBulkCopy CLASS. IT IS AVAILALBLE IN THE
-- FREE VERSION OF SQL#: https://SQLsharp.com/
--
--========================================================================

-- THERE ARE THREE MAIN TESTS. FOR EACH TEST, HIGHLIGHT "USE" STATEMENT
-- THROUGH "SELECT * FROM ##BulkTest;". EACH TEST WILL FAIL. THEN CHANGE
-- THE "@SourceConnection" PARAMETER VALUE TO BE "NULL" AND EXECUTE
-- AGAIN. THEN IT WILL WORK!!

--
USE [UTF8];

--DROP TABLE ##BulkTest;
IF (OBJECT_ID(N'tempdb..##BulkTest') IS NULL)
BEGIN
  CREATE TABLE ##BulkTest (
    [1] VARCHAR(50) COLLATE Latin1_General_100_CI_AS,--DATABASE_DEFAULT,
    [2] BIT,
    [3] BIT,
    [4] BIT,
    [NULL] NCHAR(2000) COLLATE Latin1_General_100_CI_AS,
    [5] BIT,
    [6] NVARCHAR(4000) COLLATE Latin1_General_100_CI_AS,
    [7] VARCHAR(30) COLLATE Latin1_General_100_CI_AS,
    [8] BIGINT
  );
END;


DECLARE @RowsCopied BIGINT;

--EXEC SQL#.SQLsharp_SetSecurity 2, N'SQL#.DB';
EXEC SQL#.DB_BulkCopy
    @SourceConnection =  N'Server=(local)\SQL2019;Trusted_Connection=True;Database=UTF8;',
    @SourceQuery = N'
SELECT TOP (100)
    ''a'' AS [1],
    CONVERT(BIT, 1) AS [2],
    CONVERT(BIT, 1) AS [3],
    4 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',
    @DestinationConnection = N'Server=(local)\SQL2019;Trusted_Connection=True;Database=tempdb;',
    @DestinationTableName = N'##BulkTest',
    @BatchSize = 0,
    @NotifyAfterRows = 0,
    @TimeOut = 30,
    @ColumnMappings = N'',
    @BulkCopyOptionsList = N'',
    @SourceCommandTimeout = 0,
    @RowsCopied = @RowsCopied OUTPUT;

SELECT @RowsCopied AS [RowsCopied];

SELECT * FROM ##BulkTest;



/*
Results in ##BulkTest:
    (1 row of the following):
a   1   1   1   앉仪gf❵𐑪.abcdefghijklmnoptuvwxyzĈ  1       ??gf??? 7205868257088135424



Msg 6522, Level 16, State 1, Procedure SQL#.DB_BulkCopy, Line 0 [Batch Start Line 0]
A .NET Framework error occurred during execution of user-defined routine or aggregate "DB_BulkCopy":
System.InvalidOperationException: Internal connection fatal error. Error state: 15, Token : 0
System.InvalidOperationException:
   at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at System.Data.SqlClient.SqlDataReader.TryCloseInternal(Boolean closeReader)
   at System.Data.SqlClient.SqlDataReader.Close()
   at DB.BulkCopy(SqlString SourceType, SqlString SourceConnection, SqlString SourceQuery, SqlString DestinationConnection, SqlString DestinationTableName, SqlInt32 BatchSize, SqlInt32 NotifyAfterRows, SqlInt32 TimeOut, SqlString ColumnMappings, SqlString BulkCopyOptionsList, SqlInt32 SourceCommandTimeout, SqlInt64& RowsCopied)
.
*/

--------------------------------------------------------------------
Change:
    @SourceConnection =  NULL, -- USE "Context_Connection=True;"


Works perfectly. 100 rows of:
a   1   1   1   NULL    1   앉仪gf❵𐑪 abcdefghijklmnoptuvwxyz 1
--------------------------------------------------------------------

-- NEXT: Change column [NULL] in temp table to CHAR(1)  (was NCHAR(2000)),
--  AND: column [4] in SELECT to "CONVERT(BIT, 1)"      (was just "4").

USE [UTF8];

--DROP TABLE ##BulkTest;
IF (OBJECT_ID(N'tempdb..##BulkTest') IS NULL)
BEGIN
  CREATE TABLE ##BulkTest (
    [1] VARCHAR(50) COLLATE Latin1_General_100_CI_AS,--DATABASE_DEFAULT,
    [2] BIT,
    [3] BIT,
    [4] BIT,
    [NULL] CHAR(1) COLLATE Latin1_General_100_CI_AS,
    [5] BIT,
    [6] NVARCHAR(4000) COLLATE Latin1_General_100_CI_AS,
    [7] VARCHAR(30) COLLATE Latin1_General_100_CI_AS,
    [8] BIGINT
  );
END;


DECLARE @RowsCopied BIGINT;

--EXEC SQL#.SQLsharp_SetSecurity 2, N'SQL#.DB';
EXEC SQL#.DB_BulkCopy
    @SourceConnection =  N'Server=(local)\SQL2019;Trusted_Connection=True;Database=UTF8;',
    @SourceQuery = N'
SELECT TOP (100)
    ''a'' 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',
    @DestinationConnection = N'Server=(local)\SQL2019;Trusted_Connection=True;Database=tempdb;',
    @DestinationTableName = N'##BulkTest',
    @BatchSize = 0,
    @NotifyAfterRows = 0,
    @TimeOut = 30,
    @ColumnMappings = N'',
    @BulkCopyOptionsList = N'',
    @SourceCommandTimeout = 0,
    @RowsCopied = @RowsCopied OUTPUT;

SELECT @RowsCopied AS [RowsCopied];

SELECT * FROM ##BulkTest;

/*
Results in ##BulkTest:
    (no rows)


Msg 6522, Level 16, State 1, Procedure SQL#.DB_BulkCopy, Line 0 [Batch Start Line 0]
A .NET Framework error occurred during execution of user-defined routine or aggregate "DB_BulkCopy":
System.Data.SqlClient.SqlException: Received an invalid column length from the bcp client for colid 5.
System.Data.SqlClient.SqlException:
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
   at System.Data.SqlClient.SqlBulkCopy.RunParser(BulkCopySimpleResultSet bulkCopyHandler)
   at System.Data.SqlClient.SqlBulkCopy.CopyBatchesAsyncContinuedOnSuccess(BulkCopySimpleResultSet internalResults, String updateBulkCommandText, CancellationToken cts, TaskCompletionSource`1 source)
   at System.Data.SqlClient.SqlBulkCopy.CopyBatchesAsyncContinued(BulkCopySimpleResultSet internalResults, String updateBulkCommandText, CancellationToken cts, TaskCompletionSource`1 source)
   at System.Data.SqlClient.SqlBulkCopy.CopyBatchesAsync(BulkCopySimpleResultSet internalResults, String updateBulkCommandText, CancellationToken cts, TaskCompletionSource`1 source)
   at System.Data.SqlClient.SqlBulkCopy.WriteToServerInternalRestContinuedAsync(BulkCopySimpleResultSet internalResults, CancellationToken cts, TaskCompletionSource`1 source)
   at System.Data.SqlClient.SqlBulkCopy.WriteToServerInternalRestAsync(CancellationToken cts, TaskCompletionSource`1 source)
   at System.Data.SqlClient.SqlBulkCopy.WriteToServerInternalAsync(CancellationToken ctoken)
   at System.Data.SqlClient.SqlBulkCopy.WriteRowSourceToSe...
*/

--------------------------------------------------------------------
Change:
    @SourceConnection =  NULL, -- USE "Context_Connection=True;"

Works perfectly. 100 rows of:
a   1   1   1   NULL    1   앉仪gf❵𐑪 abcdefghijklmnoptuvwxyz 1
--------------------------------------------------------------------



-- NEXT: Change column [NULL] in temp table to NVARCHAR(1000)  (was CHAR(1)).

USE [UTF8];

--DROP TABLE ##BulkTest;
IF (OBJECT_ID(N'tempdb..##BulkTest') IS NULL)
BEGIN
  CREATE TABLE ##BulkTest (
    [1] VARCHAR(50) COLLATE Latin1_General_100_CI_AS,--DATABASE_DEFAULT,
    [2] BIT,
    [3] BIT,
    [4] BIT,
    [NULL] NVARCHAR(1000) COLLATE Latin1_General_100_CI_AS,
    [5] BIT,
    [6] NVARCHAR(4000) COLLATE Latin1_General_100_CI_AS,
    [7] VARCHAR(30) COLLATE Latin1_General_100_CI_AS,
    [8] BIGINT
  );
END;


DECLARE @RowsCopied BIGINT;

--EXEC SQL#.SQLsharp_SetSecurity 2, N'SQL#.DB';
EXEC SQL#.DB_BulkCopy
    @SourceConnection =  N'Server=(local)\SQL2019;Trusted_Connection=True;Database=UTF8;',
    @SourceQuery = N'
SELECT TOP (100)
    ''a'' 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',
    @DestinationConnection = N'Server=(local)\SQL2019;Trusted_Connection=True;Database=tempdb;',
    @DestinationTableName = N'##BulkTest',
    @BatchSize = 0,
    @NotifyAfterRows = 0,
    @TimeOut = 30,
    @ColumnMappings = N'',
    @BulkCopyOptionsList = N'',
    @SourceCommandTimeout = 0,
    @RowsCopied = @RowsCopied OUTPUT;

SELECT @RowsCopied AS [RowsCopied];

SELECT * FROM ##BulkTest;


/*
Results in ##BulkTest:
    (25 rows of the following):
a   1   1   1   앉仪gf❵𐑪.abcdefghijklmnoptuvwxyzĈ  1   앉仪gf❵𐑪 abcdefghijklmnoptuvwxyz 1

NO ERROR! SUCCESS (well, outside of it a) returning 25 out of 100 rows, and b) returning garbage in column #5
*/

--------------------------------------------------------------------
Change:
    @SourceConnection =  NULL, -- USE "Context_Connection=True;"

Works perfectly. 100 rows of:
a   1   1   1   NULL    1   앉仪gf❵𐑪 abcdefghijklmnoptuvwxyz 1
--------------------------------------------------------------------
--------------------------------------------------------------------