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