SqlQuantumLeap

UTF8_Bug_35606671_SQLCLR-Tests

Oct 16th, 2018
113
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. -- These examples support the following bug report for SQL Server 2019 CTP 2.0:
  2. -- https://feedback.azure.com/forums/908035-sql-server/suggestions/35606671-utf-8-in-sql-2019-an-error-occurred-while-executi
  3.  
  4. -- By: Solomon Rutzky
  5. -- On: 2018-10-16
  6. -- https://SqlQuantumLeap.com/
  7.  
  8. -----------------------------------------------------------------------
  9. IF (DB_ID(N'UTF8') IS NULL)
  10. BEGIN
  11.    CREATE DATABASE [UTF8] COLLATE Latin1_General_100_CI_AS_SC_UTF8;
  12.    ALTER DATABASE [UTF8] SET RECOVERY SIMPLE;
  13. END;
  14. GO
  15. -----------------------------------------------------------------------
  16.  
  17.  
  18. --========================================================================
  19. --
  20. -- THE FOLLOWING TESTS USE A SQLCLR FUNCTION, DB_BulkCopy, WHICH IS A
  21. -- WRAPPER FOR THE .NET SqlBulkCopy CLASS. IT IS AVAILALBLE IN THE
  22. -- FREE VERSION OF SQL#: https://SQLsharp.com/
  23. --
  24. --========================================================================
  25.  
  26. -- THERE ARE THREE MAIN TESTS. FOR EACH TEST, HIGHLIGHT "USE" STATEMENT
  27. -- THROUGH "SELECT * FROM ##BulkTest;". EACH TEST WILL FAIL. THEN CHANGE
  28. -- THE "@SourceConnection" PARAMETER VALUE TO BE "NULL" AND EXECUTE
  29. -- AGAIN. THEN IT WILL WORK!!
  30.  
  31. --
  32. USE [UTF8];
  33.  
  34. --DROP TABLE ##BulkTest;
  35. IF (OBJECT_ID(N'tempdb..##BulkTest') IS NULL)
  36. BEGIN
  37.   CREATE TABLE ##BulkTest (
  38.     [1] VARCHAR(50) COLLATE Latin1_General_100_CI_AS,--DATABASE_DEFAULT,
  39.     [2] BIT,
  40.     [3] BIT,
  41.     [4] BIT,
  42.     [NULL] NCHAR(2000) COLLATE Latin1_General_100_CI_AS,
  43.     [5] BIT,
  44.     [6] NVARCHAR(4000) COLLATE Latin1_General_100_CI_AS,
  45.     [7] VARCHAR(30) COLLATE Latin1_General_100_CI_AS,
  46.     [8] BIGINT
  47.   );
  48. END;
  49.  
  50.  
  51. DECLARE @RowsCopied BIGINT;
  52.  
  53. --EXEC SQL#.SQLsharp_SetSecurity 2, N'SQL#.DB';
  54. EXEC SQL#.DB_BulkCopy
  55.     @SourceConnection =  N'Server=(local)\SQL2019;Trusted_Connection=True;Database=UTF8;',
  56.     @SourceQuery = N'
  57. SELECT TOP (100)
  58.     ''a'' AS [1],
  59.     CONVERT(BIT, 1) AS [2],
  60.     CONVERT(BIT, 1) AS [3],
  61.     4 AS [4],
  62.     CONVERT(CHAR(1), NULL) AS [NULL],
  63.     CONVERT(BIT, 1) AS [5],
  64.     NCHAR(50505) + NCHAR(20202) + N''gf'' + NCHAR(10101) + NCHAR(66666) AS [6],
  65.     ''abcdefghijklmnoptuvwxyz'' AS [7],
  66.     CONVERT(BIGINT, 1) AS [8]
  67. FROM [master].[sys].[columns] col',
  68.     @DestinationConnection = N'Server=(local)\SQL2019;Trusted_Connection=True;Database=tempdb;',
  69.     @DestinationTableName = N'##BulkTest',
  70.     @BatchSize = 0,
  71.     @NotifyAfterRows = 0,
  72.     @TimeOut = 30,
  73.     @ColumnMappings = N'',
  74.     @BulkCopyOptionsList = N'',
  75.     @SourceCommandTimeout = 0,
  76.     @RowsCopied = @RowsCopied OUTPUT;
  77.  
  78. SELECT @RowsCopied AS [RowsCopied];
  79.  
  80. SELECT * FROM ##BulkTest;
  81.  
  82.  
  83.  
  84. /*
  85. Results in ##BulkTest:
  86.     (1 row of the following):
  87. a   1   1   1   앉仪gf❵𐑪.abcdefghijklmnoptuvwxyzĈ  1       ??gf??? 7205868257088135424
  88.  
  89.  
  90.  
  91. Msg 6522, Level 16, State 1, Procedure SQL#.DB_BulkCopy, Line 0 [Batch Start Line 0]
  92. A .NET Framework error occurred during execution of user-defined routine or aggregate "DB_BulkCopy":
  93. System.InvalidOperationException: Internal connection fatal error. Error state: 15, Token : 0
  94. System.InvalidOperationException:
  95.    at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
  96.    at System.Data.SqlClient.SqlDataReader.TryCloseInternal(Boolean closeReader)
  97.    at System.Data.SqlClient.SqlDataReader.Close()
  98.    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)
  99. .
  100. */
  101.  
  102. --------------------------------------------------------------------
  103. Change:
  104.     @SourceConnection =  NULL, -- USE "Context_Connection=True;"
  105.  
  106.  
  107. Works perfectly. 100 rows of:
  108. a   1   1   1   NULL    1   앉仪gf❵𐑪 abcdefghijklmnoptuvwxyz 1
  109. --------------------------------------------------------------------
  110.  
  111. -- NEXT: Change column [NULL] in temp table to CHAR(1)  (was NCHAR(2000)),
  112. --  AND: column [4] in SELECT to "CONVERT(BIT, 1)"      (was just "4").
  113.  
  114. USE [UTF8];
  115.  
  116. --DROP TABLE ##BulkTest;
  117. IF (OBJECT_ID(N'tempdb..##BulkTest') IS NULL)
  118. BEGIN
  119.   CREATE TABLE ##BulkTest (
  120.     [1] VARCHAR(50) COLLATE Latin1_General_100_CI_AS,--DATABASE_DEFAULT,
  121.     [2] BIT,
  122.     [3] BIT,
  123.     [4] BIT,
  124.     [NULL] CHAR(1) COLLATE Latin1_General_100_CI_AS,
  125.     [5] BIT,
  126.     [6] NVARCHAR(4000) COLLATE Latin1_General_100_CI_AS,
  127.     [7] VARCHAR(30) COLLATE Latin1_General_100_CI_AS,
  128.     [8] BIGINT
  129.   );
  130. END;
  131.  
  132.  
  133. DECLARE @RowsCopied BIGINT;
  134.  
  135. --EXEC SQL#.SQLsharp_SetSecurity 2, N'SQL#.DB';
  136. EXEC SQL#.DB_BulkCopy
  137.     @SourceConnection =  N'Server=(local)\SQL2019;Trusted_Connection=True;Database=UTF8;',
  138.     @SourceQuery = N'
  139. SELECT TOP (100)
  140.     ''a'' AS [1],
  141.     CONVERT(BIT, 1) AS [2],
  142.     CONVERT(BIT, 1) AS [3],
  143.     CONVERT(BIT, 1) AS [4],
  144.     CONVERT(CHAR(1), NULL) AS [NULL],
  145.     CONVERT(BIT, 1) AS [5],
  146.     NCHAR(50505) + NCHAR(20202) + N''gf'' + NCHAR(10101) + NCHAR(66666) AS [6],
  147.     ''abcdefghijklmnoptuvwxyz'' AS [7],
  148.     CONVERT(BIGINT, 1) AS [8]
  149. FROM [master].[sys].[columns] col',
  150.     @DestinationConnection = N'Server=(local)\SQL2019;Trusted_Connection=True;Database=tempdb;',
  151.     @DestinationTableName = N'##BulkTest',
  152.     @BatchSize = 0,
  153.     @NotifyAfterRows = 0,
  154.     @TimeOut = 30,
  155.     @ColumnMappings = N'',
  156.     @BulkCopyOptionsList = N'',
  157.     @SourceCommandTimeout = 0,
  158.     @RowsCopied = @RowsCopied OUTPUT;
  159.    
  160. SELECT @RowsCopied AS [RowsCopied];
  161.  
  162. SELECT * FROM ##BulkTest;
  163.  
  164. /*
  165. Results in ##BulkTest:
  166.     (no rows)
  167.  
  168.  
  169. Msg 6522, Level 16, State 1, Procedure SQL#.DB_BulkCopy, Line 0 [Batch Start Line 0]
  170. A .NET Framework error occurred during execution of user-defined routine or aggregate "DB_BulkCopy":
  171. System.Data.SqlClient.SqlException: Received an invalid column length from the bcp client for colid 5.
  172. System.Data.SqlClient.SqlException:
  173.    at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
  174.    at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
  175.    at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
  176.    at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
  177.    at System.Data.SqlClient.SqlBulkCopy.RunParser(BulkCopySimpleResultSet bulkCopyHandler)
  178.    at System.Data.SqlClient.SqlBulkCopy.CopyBatchesAsyncContinuedOnSuccess(BulkCopySimpleResultSet internalResults, String updateBulkCommandText, CancellationToken cts, TaskCompletionSource`1 source)
  179.    at System.Data.SqlClient.SqlBulkCopy.CopyBatchesAsyncContinued(BulkCopySimpleResultSet internalResults, String updateBulkCommandText, CancellationToken cts, TaskCompletionSource`1 source)
  180.    at System.Data.SqlClient.SqlBulkCopy.CopyBatchesAsync(BulkCopySimpleResultSet internalResults, String updateBulkCommandText, CancellationToken cts, TaskCompletionSource`1 source)
  181.    at System.Data.SqlClient.SqlBulkCopy.WriteToServerInternalRestContinuedAsync(BulkCopySimpleResultSet internalResults, CancellationToken cts, TaskCompletionSource`1 source)
  182.    at System.Data.SqlClient.SqlBulkCopy.WriteToServerInternalRestAsync(CancellationToken cts, TaskCompletionSource`1 source)
  183.    at System.Data.SqlClient.SqlBulkCopy.WriteToServerInternalAsync(CancellationToken ctoken)
  184.    at System.Data.SqlClient.SqlBulkCopy.WriteRowSourceToSe...
  185. */
  186.  
  187. --------------------------------------------------------------------
  188. Change:
  189.     @SourceConnection =  NULL, -- USE "Context_Connection=True;"
  190.  
  191. Works perfectly. 100 rows of:
  192. a   1   1   1   NULL    1   앉仪gf❵𐑪 abcdefghijklmnoptuvwxyz 1
  193. --------------------------------------------------------------------
  194.  
  195.  
  196.  
  197. -- NEXT: Change column [NULL] in temp table to NVARCHAR(1000)  (was CHAR(1)).
  198.  
  199. USE [UTF8];
  200.  
  201. --DROP TABLE ##BulkTest;
  202. IF (OBJECT_ID(N'tempdb..##BulkTest') IS NULL)
  203. BEGIN
  204.   CREATE TABLE ##BulkTest (
  205.     [1] VARCHAR(50) COLLATE Latin1_General_100_CI_AS,--DATABASE_DEFAULT,
  206.     [2] BIT,
  207.     [3] BIT,
  208.     [4] BIT,
  209.     [NULL] NVARCHAR(1000) COLLATE Latin1_General_100_CI_AS,
  210.     [5] BIT,
  211.     [6] NVARCHAR(4000) COLLATE Latin1_General_100_CI_AS,
  212.     [7] VARCHAR(30) COLLATE Latin1_General_100_CI_AS,
  213.     [8] BIGINT
  214.   );
  215. END;
  216.  
  217.  
  218. DECLARE @RowsCopied BIGINT;
  219.  
  220. --EXEC SQL#.SQLsharp_SetSecurity 2, N'SQL#.DB';
  221. EXEC SQL#.DB_BulkCopy
  222.     @SourceConnection =  N'Server=(local)\SQL2019;Trusted_Connection=True;Database=UTF8;',
  223.     @SourceQuery = N'
  224. SELECT TOP (100)
  225.     ''a'' AS [1],
  226.     CONVERT(BIT, 1) AS [2],
  227.     CONVERT(BIT, 1) AS [3],
  228.     CONVERT(BIT, 1) AS [4],
  229.     CONVERT(CHAR(1), NULL) AS [NULL],
  230.     CONVERT(BIT, 1) AS [5],
  231.     NCHAR(50505) + NCHAR(20202) + N''gf'' + NCHAR(10101) + NCHAR(66666) AS [6],
  232.     ''abcdefghijklmnoptuvwxyz'' AS [7],
  233.     CONVERT(BIGINT, 1) AS [8]
  234. FROM [master].[sys].[columns] col',
  235.     @DestinationConnection = N'Server=(local)\SQL2019;Trusted_Connection=True;Database=tempdb;',
  236.     @DestinationTableName = N'##BulkTest',
  237.     @BatchSize = 0,
  238.     @NotifyAfterRows = 0,
  239.     @TimeOut = 30,
  240.     @ColumnMappings = N'',
  241.     @BulkCopyOptionsList = N'',
  242.     @SourceCommandTimeout = 0,
  243.     @RowsCopied = @RowsCopied OUTPUT;
  244.  
  245. SELECT @RowsCopied AS [RowsCopied];
  246.  
  247. SELECT * FROM ##BulkTest;
  248.  
  249.  
  250. /*
  251. Results in ##BulkTest:
  252.     (25 rows of the following):
  253. a   1   1   1   앉仪gf❵𐑪.abcdefghijklmnoptuvwxyzĈ  1   앉仪gf❵𐑪 abcdefghijklmnoptuvwxyz 1
  254.  
  255. NO ERROR! SUCCESS (well, outside of it a) returning 25 out of 100 rows, and b) returning garbage in column #5
  256. */
  257.  
  258. --------------------------------------------------------------------
  259. Change:
  260.     @SourceConnection =  NULL, -- USE "Context_Connection=True;"
  261.  
  262. Works perfectly. 100 rows of:
  263. a   1   1   1   NULL    1   앉仪gf❵𐑪 abcdefghijklmnoptuvwxyz 1
  264. --------------------------------------------------------------------
  265. --------------------------------------------------------------------
RAW Paste Data