-- 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
--------------------------------------------------------------------
--------------------------------------------------------------------
Comments
0 B
|👍
/👎